本文共 23020 字，大约阅读时间需要 76 分钟。
mysql> use mysql;mysql> show tables;+---------------------------+| Tables_in_mysql |+---------------------------+| columns_priv || db || procs_priv || proxies_priv || tables_priv || user |+---------------------------+31 rows in set (0.00 sec)
根据账户所具有的权限的不同，MySQL的账户可以分为以下几种：
如果某用户如root,拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库、连同这些库中的表。
该账号可以在具有权限的数据库中执行增删改查的所有操作（如果分配了这些权限）。
db表列出数据库，而用户有权限访问它们。在这里指定的权限适用于一个数据库中的所有表。
mysql> desc db;+-----------------------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------------------+---------------+------+-----+---------+-------+| Host | char(60) | NO | PRI | | || Db | char(64) | NO | PRI | | || User | char(32) | NO | PRI | | || Select_priv | enum('N','Y') | NO | | N | || Insert_priv | enum('N','Y') | NO | | N | || Update_priv | enum('N','Y') | NO | | N | || Delete_priv | enum('N','Y') | NO | | N | || Create_priv | enum('N','Y') | NO | | N | || Drop_priv | enum('N','Y') | NO | | N | || Grant_priv | enum('N','Y') | NO | | N | || References_priv | enum('N','Y') | NO | | N | || Index_priv | enum('N','Y') | NO | | N | || Alter_priv | enum('N','Y') | NO | | N | || Create_tmp_table_priv | enum('N','Y') | NO | | N | || Lock_tables_priv | enum('N','Y') | NO | | N | || Create_view_priv | enum('N','Y') | NO | | N | || Show_view_priv | enum('N','Y') | NO | | N | || Create_routine_priv | enum('N','Y') | NO | | N | || Alter_routine_priv | enum('N','Y') | NO | | N | || Execute_priv | enum('N','Y') | NO | | N | || Event_priv | enum('N','Y') | NO | | N | || Trigger_priv | enum('N','Y') | NO | | N | |+-----------------------+---------------+------+-----+---------+-------+22 rows in set (0.00 sec)*************************** 7. row *************************** Host: % Db: sunld_db User: account_sunld_db Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: YCreate_tmp_table_priv: Y Lock_tables_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Execute_priv: Y Event_priv: Y Trigger_priv: Y7 rows in set (0.00 sec)
该账号可以在具有权限的表上执行增删改查等所有操作（如果分配了这些权限）。
tables_priv表指定表级权限，在这里指定的一个权限适用于一个表的所有列。
mysql> desc tables_priv;+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+| Field | Type | Null | Key | Default | Extra |+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+| Host | char(60) | NO | PRI | | || Db | char(64) | NO | PRI | | || User | char(32) | NO | PRI | | || Table_name | char(64) | NO | PRI | | || Grantor | char(93) | NO | MUL | | || Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP || Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | | | || Column_priv | set('Select','Insert','Update','References') | NO | | | |+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+8 rows in set (0.00 sec)*************************** 4. row *************************** Host: % Db: sunld_db User: account_sunld_table Table_name: tb_a Grantor: root@localhost Timestamp: 0000-00-00 00:00:00 Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,TriggerColumn_priv: 该账号可以对某些表中具有权限的字段进行操作（取决于所分配的权限）。
columns_priv表指定列级权限。这里指定的权限适用于一个表的特定列。
mysql> desc columns_priv;+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+| Field | Type | Null | Key | Default | Extra |+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+| Host | char(60) | NO | PRI | | || Db | char(64) | NO | PRI | | || User | char(32) | NO | PRI | | || Table_name | char(64) | NO | PRI | | || Column_name | char(64) | NO | PRI | | || Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP || Column_priv | set('Select','Insert','Update','References') | NO | | | |+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+7 rows in set (0.00 sec) 该账号可以对存储程序进行增删改查的操作（取决于所分配的权限）
user表列出可以连接服务器的用户及其口令，并且它指定他们有哪种全局（超级用户）权限。在user表启用的任何权限均是全局权限，并适用于所有数据库。例如，如果你启用了DELETE权限，在这里列出的用户可以从任何表中删除记录，所以在你这样做之前要认真考虑。
mysql> desc user;+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Field | Type | Null | Key | Default | Extra |+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Host | char(60) | NO | PRI | | || User | char(32) | NO | PRI | | || Select_priv | enum('N','Y') | NO | | N | || Insert_priv | enum('N','Y') | NO | | N | || Update_priv | enum('N','Y') | NO | | N | || Delete_priv | enum('N','Y') | NO | | N | || Create_priv | enum('N','Y') | NO | | N | || Drop_priv | enum('N','Y') | NO | | N | || Reload_priv | enum('N','Y') | NO | | N | || Shutdown_priv | enum('N','Y') | NO | | N | || Process_priv | enum('N','Y') | NO | | N | || File_priv | enum('N','Y') | NO | | N | || Grant_priv | enum('N','Y') | NO | | N | || References_priv | enum('N','Y') | NO | | N | || Index_priv | enum('N','Y') | NO | | N | || Alter_priv | enum('N','Y') | NO | | N | || Show_db_priv | enum('N','Y') | NO | | N | || Super_priv | enum('N','Y') | NO | | N | || Create_tmp_table_priv | enum('N','Y') | NO | | N | || Lock_tables_priv | enum('N','Y') | NO | | N | || Execute_priv | enum('N','Y') | NO | | N | || Repl_slave_priv | enum('N','Y') | NO | | N | || Repl_client_priv | enum('N','Y') | NO | | N | || Create_view_priv | enum('N','Y') | NO | | N | || Show_view_priv | enum('N','Y') | NO | | N | || Create_routine_priv | enum('N','Y') | NO | | N | || Alter_routine_priv | enum('N','Y') | NO | | N | || Create_user_priv | enum('N','Y') | NO | | N | || Event_priv | enum('N','Y') | NO | | N | || Trigger_priv | enum('N','Y') | NO | | N | || Create_tablespace_priv | enum('N','Y') | NO | | N | || ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | || ssl_cipher | blob | NO | | NULL | || x509_issuer | blob | NO | | NULL | || x509_subject | blob | NO | | NULL | || max_questions | int(11) unsigned | NO | | 0 | || max_updates | int(11) unsigned | NO | | 0 | || max_connections | int(11) unsigned | NO | | 0 | || max_user_connections | int(11) unsigned | NO | | 0 | || plugin | char(64) | NO | | mysql_native_password | || authentication_string | text | YES | | NULL | || password_expired | enum('N','Y') | NO | | N | || password_last_changed | timestamp | YES | | NULL | || password_lifetime | smallint(5) unsigned | YES | | NULL | || account_locked | enum('N','Y') | NO | | N | |+------------------------+-----------------------------------+------+-----+-----------------------+-------+45 rows in set (0.00 sec)*************************** 12. row *************************** Host: % User: account_sunld_db_2 Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: NCreate_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 password_expired: N password_last_changed: 2017-12-30 12:33:32 password_lifetime: NULL account_locked: N grant 权限名称[字段列表] on [数据库资源类型]数据库资源 to MySQL账户1,[MySQL账户2] [with grant option]#增删改数据库数据权限grant select, insert, update, delete on ****#创建、修改、删除 MySQL 数据表结构权限grant create ,alter ,drop on ****#操作 MySQL 外键权限。grant references on ****#操作 MySQL 临时表权限grant create temporary tables on ****#操作 MySQL 索引权限grant index on ****#操作 MySQL 视图、查看视图源代码 权限grant create view on ****grant show view on ****#操作 MySQL 存储过程、函数 权限grant create routine on ****; -- now, can show procedure statusgrant alter routine on ****; -- now, you can drop a proceduregrant execute on ****;#普通 DBA 管理某个 MySQL 数据库的权限grant all privileges on db*** to其中，关键字 “privileges” 可以省略。#高级 DBA 管理 MySQL 中所有数据库的权限grant all on *.* to
#创建账号sunld_all，拥有所有数据库权限，并且具有grant权限，可以创建其他拥有服务实例权限的其他用户。#查询mysql> show grants for account_sunld_all;ERROR 1141 (42000): There is no such grant defined for user 'account_sunld_all' on host '%'#创建grant all privileges on *.* to 'account_sunld_all'@'%' identified by '123456' with grant option;flush privileges;#查询mysql> show grants for account_sunld_all;+--------------------------------------------------------------------------+| Grants for account_sunld_all@% |+--------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'account_sunld_all'@'%' WITH GRANT OPTION |+--------------------------------------------------------------------------+1 row in set (0.00 sec)
#创建账号sunld_db,拥有sunlddb数据库的所有权限，可以对该库中的表进行所有操作。#查询mysql> show grants for account_sunld_db;ERROR 1141 (42000): There is no such grant defined for user 'account_sunld_db' on host '%'#创建grant all privileges on sunld_db.* to 'account_sunld_db'@'%' identified by '123456' with grant option;#查询mysql> show grants for account_sunld_db;+----------------------------------------------------------------------------------+| Grants for account_sunld_db@% |+----------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'account_sunld_db'@'%' || GRANT ALL PRIVILEGES ON `sunld_db`.* TO 'account_sunld_db'@'%' WITH GRANT OPTION |+----------------------------------------------------------------------------------+2 rows in set (0.00 sec)
#创建账号sunld_table，对sunlddb数据中的tb_a表拥有所有权限#查询mysql> show grants for account_sunld_table;ERROR 1141 (42000): There is no such grant defined for user 'account_sunld_table' on host '%'#创建grant all privileges on table sunld_db.tb_a to 'account_sunld_table'@'%' identified by '123456';#查询mysql> show grants for account_sunld_table;+------------------------------------------------------------------------+| Grants for account_sunld_table@% |+------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'account_sunld_table'@'%' || GRANT ALL PRIVILEGES ON `sunld_db`.`tb_a` TO 'account_sunld_table'@'%' |+------------------------------------------------------------------------+2 rows in set (0.00 sec)#这里在给一个用户授权多张表时，可以多次执行以上语句。例如：grant select(user_id,username) on smp.users to mo_user@'%' identified by '123345';grant select on smp.mo_sms to mo_user@'%' identified by '123345';
grant select(id, se, rank) on testdb.apache_log to dba@localhost;
grant execute on procedure testdb.pr_add to 'dba'@'localhost'grant execute on function testdb.fn_add to 'dba'@'localhost'
字段设置冗余复杂，则每次MySQL在进行SQL执行的时候回进行同样复杂的权限判断，造成效率降低性能下降，同时也会存在安全隐患。因此简单、易管理的、高可用的权限才是可取的。
#查询mysql> show grants for account_sunld_db_1;ERROR 1141 (42000): There is no such grant defined for user 'account_sunld_db_1' on host '%'#比如原本的权限为：grant select,insert on sunld_db.* to 'account_sunld_db_1'@'%' identified by '123456' with grant option;#查询mysql> show grants for account_sunld_db_1;+------------------------------------------------------------------------------------+| Grants for account_sunld_db_1@% |+------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'account_sunld_db_1'@'%' || GRANT SELECT, INSERT ON `sunld_db`.* TO 'account_sunld_db_1'@'%' WITH GRANT OPTION |+------------------------------------------------------------------------------------+2 rows in set (0.00 sec)#想要增加update, delete,alter 权限可以如下操作：grant update,delete,alter on sunld_db.* to 'account_sunld_db_1'@'%' identified by '123456' with grant option;#然后使用show grants for account_sunld_db_1;mysql> show grants for account_sunld_db_1;+-----------------------------------------------------------------------------------------------------------+| Grants for account_sunld_db_1@% |+-----------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'account_sunld_db_1'@'%' || GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON `sunld_db`.* TO 'account_sunld_db_1'@'%' WITH GRANT OPTION |+-----------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)#删除权限insertrevoke insert on sunld_db.* from 'account_sunld_db_1'@'%';查看mysql> show grants for account_sunld_db_1;+---------------------------------------------------------------------------------------------------+| Grants for account_sunld_db_1@% |+---------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'account_sunld_db_1'@'%' || GRANT SELECT, UPDATE, DELETE, ALTER ON `sunld_db`.* TO 'account_sunld_db_1'@'%' WITH GRANT OPTION |+---------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)
用户管理也无非是增加用户、删除用户、密码修改和授权之类的。
查询数据库mysql下的表user;
mysql> select user,host from user;+----------------------------+-----------+| user | host |+----------------------------+-----------+| account_sunld_all | % || account_sunld_db | % || account_sunld_db_1 | % || account_sunld_table | % || inspurCloudDB | % || inspurCloudCheckDBDiskSize | 127.0.0.1 || inspurCloudDB | 127.0.0.1 || root | 127.0.0.1 || inspurCloudDB | localhost || mysql.sys | localhost || root | localhost |+----------------------------+-----------+11 rows in set (0.00 sec)
create user 'USER_NAME'@'HOST' identified by 'PASSOWRD';#例子create user 'account_sunld_db_2'@'%' identified by '123456';
set password=password('123456');其实这个命令不仅能够设置当前用户的密码，也可以设置其他用户的密码，这个命令的完整格式是：set password [for USER] = passowrd('新密码');#例子set password for account_sunld_db_2=password('123456'); ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> SHOW VARIABLES LIKE 'validate_password%';+--------------------------------------+--------+| Variable_name | Value |+--------------------------------------+--------+| validate_password_check_user_name | OFF || validate_password_dictionary_file | || validate_password_length | 8 || validate_password_mixed_case_count | 1 || validate_password_number_count | 1 || validate_password_policy | MEDIUM || validate_password_special_char_count | 1 |+--------------------------------------+--------+7 rows in set (0.00 sec)
validate_password_policy有以下取值：
| Policy | Tests Performed |
| 0 or LOW | Length |
| 1 or MEDIUM | Length; numeric, lowercase/uppercase, and special characters |
| 2 or STRONG | Length; numeric, lowercase/uppercase, and special characters; dictionary file |
mysql> set global validate_password_policy=0;mysql> set global validate_password_length=1;mysql> set password=password('123456a?'); drop user USER_NAME；#例子drop user account_sunld_db_2;
1. grant, revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。
2. 如果想让授权的用户，也可以将这些权限 grant 给其他用户，需要选项 “grant option“
grant select on testdb.* to 'dba'@'localhost' with grant option;
这个特性一般用不到。实际中，数据库权限最好由 DBA 来统一管理。
当不加@选项时，效果与加@'%'是一样的，'%'从名义上包括任何主机，（%必须加上引号，不然与@放在一起可能不会被辨认出。）不过有些时候（有些版本）'%'不包括localhost，要单独对@'localhost'进行赋值。
转载地址：http://ofasi.baihongyu.com/