MySQL用户权限管理总结
可以使用grant命令为用户授权,也可以使用revoke命令收回用户的某些权限。在MySQL中,和权限管理有关的表主要有四个:
(1)user表:查看某个用户整体的权限;
(2)db表:查看某个用户针对某个数据库的权限;
(3)tables_priv表:查看某个用户针对某个表的权限;
(3)columns_priv表:查看某个用户针对某个列的权限;
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| .......... |
| tables_priv |
|........... |
| user |
+---------------------------+
31 rows in set (0.00 sec)
一、授予用户权限
使用grant命令给用户授权时,如果用户已存在,则给该用户授权,如果用户不存在,则创建用户同时授权,格式如下:
grant 权限 on 数据库.数据表 to '用户名'@'访问主机' identified by '密码' with grant option;
说明:
(1)常用的用户权限有CREATE、ALTER、DROP、INSERT、UPDATE、DELETE、SELECT等,也可以使用ALL PRIVILEGES表示所有权限。
(2)数据库.数据表:指定对哪个数据库的哪个表授权,可以使用【*.*】表示所有数据库中的所有表。
(3)如果需要创建新用户并授权,可以通过【 ‘用户名’@‘访问主机’】来表示用户可以从哪些主机登录,并使用【 identified by ‘密码’】创建登录密码。
(4)with grant option:表示该用户可以再给其他用户授权。
1、创建用户admin
用户admin具有操作所有数据库的所有权限,命令如下:
mysql> grant all on *.* to 'admin'@'localhost' identified by 'Admin123456.' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> select * from mysql.user where user='admin' and host='localhost'\G
*************************** 1. row ***************************
Host: localhost
User: admin
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
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: *7673FF697A1C23B2FAF5ACE9D881BB808F4B8B61
password_expired: N
password_last_changed: 2019-12-26 00:21:19
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
2、创建用户wang
用户wang具有操作my_db和test数据库的所有权限,命令如下:
mysql> grant all on my_db.* to 'wang'@'localhost' identified by 'Wang123456.' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on test.* to 'wang'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.user where user='wang' and host='localhost'\G
*************************** 1. row ***************************
Host: localhost
User: wang
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: N
Create_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: *1AB6ADD094F64ABA9635172A28662E13AE2F5389
password_expired: N
password_last_changed: 2019-12-26 00:26:08
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
可以发现在user表中看不到用户wang的权限,此时可以查看mysql.db表。
mysql> select * from mysql.db where user='wang' and host='localhost'\G
*************************** 1. row ***************************
Host: localhost
Db: my_db
User: wang
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: Y
Create_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: Y
*************************** 2. row ***************************
Host: localhost
Db: test
User: wang
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N --------注意该权限。
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_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: Y
2 rows in set (0.00 sec)
此时可以看到,和用户wang有关的数据库有两个,my_db和test,并且拥有操作这两个数据库的所有权限。
3、创建用户zhang
用户zhang具备操作test数据库中的emp和account表的所有权限,命令如下:
mysql> grant all on test.emp to 'zhang'@'localhost' identified by 'Zhang123456.' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on test.account to 'zhang'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.user where user='zhang' and host='localhost'\G
*************************** 1. row ***************************
Host: localhost
User: zhang
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: N
Create_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: *B4324C7FFBF055E9EF21C9E3F98713809068B3EF
password_expired: N
password_last_changed: 2019-12-26 00:33:00
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
mysql> select * from mysql.db where user='zhang' and host='localhost'\G
Empty set (0.00 sec)
查看mysql.user表和mysql.db表,看不到该用户的任何权限,此时可以查看tables_priv表:
mysql> select * from mysql.tables_priv where user='zhang' and host='localhost'\G
*************************** 1. row ***************************
Host: localhost
Db: test
User: zhang
Table_name: account
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,Trigger
Column_priv:
*************************** 2. row ***************************
Host: localhost
Db: test
User: zhang
Table_name: emp
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Select,Insert,Update,Delete,Create,Drop,Grant,References,Index,Alter,Create View,Show view,Trigger
Column_priv:
2 rows in set (0.00 sec)
此时可以看到,和用户zhang有关的数据库为test,同时拥有对表account与表emp的所有权限。
4、创建用户zhao
用户zhao可以操作test数据库中employee表,但只拥有查询权限,而且只能查询该表中的emp_id,emp_name,salary,dept_name字段;用户zhao同时可以修改dept表,但只能修改dept表的phone字段,命令如下:
mysql> grant select(emp_id,emp_name,salary,dept_name) on test.employee
-> to 'zhao'@'localhost' identified by 'Zhao123456.' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant update(phone) on test.dept to 'zhao'@'localhost';
Query OK, 0 rows affected (0.01 sec)
查看mysql.db表,tables_priv表和columns_priv表,结果如下:
----在mysql.db中看不到任何权限
mysql> select * from mysql.db where user='zhao' and host='localhost'\G
Empty set (0.00 sec)
----在dept表中可以看到用户zhao有更新权限,在employee表中可以看到用户zhao有查询权限
mysql> select * from mysql.tables_priv where user='zhao' and host='localhost'\G
*************************** 1. row ***************************
Host: localhost
Db: test
User: zhao
Table_name: dept
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv:
Column_priv: Update
*************************** 2. row ***************************
Host: localhost
Db: test
User: zhao
Table_name: employee
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Grant
Column_priv: Select
2 rows in set (0.00 sec)
--可以看到针对用户zhao在表中的每一列的权限
mysql> select * from mysql.columns_priv where user='zhao' and host='localhost'\G
*************************** 1. row ***************************
Host: localhost
Db: test
User: zhao
Table_name: dept
Column_name: phone
Timestamp: 0000-00-00 00:00:00
Column_priv: Update
*************************** 2. row ***************************
Host: localhost
Db: test
User: zhao
Table_name: employee
Column_name: dept_name
Timestamp: 0000-00-00 00:00:00
Column_priv: Select
*************************** 3. row ***************************
Host: localhost
Db: test
User: zhao
Table_name: employee
Column_name: emp_id
Timestamp: 0000-00-00 00:00:00
Column_priv: Select
*************************** 4. row ***************************
Host: localhost
Db: test
User: zhao
Table_name: employee
Column_name: emp_name
Timestamp: 0000-00-00 00:00:00
Column_priv: Select
*************************** 5. row ***************************
Host: localhost
Db: test
User: zhao
Table_name: employee
Column_name: salary
Timestamp: 0000-00-00 00:00:00
Column_priv: Select
5 rows in set (0.00 sec)
二、收回用户权限
收回用户权限使用revoke命令,该命令的语法和grant类似,格式如下:
revoke 权限 on 数据库名.表名 from '用户名'@'主机名';
1、收回用户wang对my_db数据库的全部访问权限
mysql> revoke all on my_db.* from 'wang'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.db where user='wang' and host='localhost'\G
*************************** 1. row ***************************
Host: localhost
Db: my_db
User: wang
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: Y
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
*************************** 2. row ***************************
Host: localhost
Db: test
User: wang
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_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: Y
2 rows in set (0.00 sec)
2、收回用户zhang针对account表的所有权限
mysql> revoke all on test.account from 'zhang'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> select * from mysql.tables_priv where user='zhang' and host='localhost'\G
*************************** 1. row ***************************
Host: localhost
Db: test
User: zhang
Table_name: emp
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Select,Insert,Update,Delete,Create,Drop,Grant,References,Index,Alter,Create View,Show view,Trigger
Column_priv:
1 row in set (0.00 sec)
3、收回用户zhang针对emp表的drop,alter权限
mysql> revoke drop,alter on test.emp from 'zhang'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> select * from mysql.tables_priv where user='zhang' and host='localhost'\G
*************************** 1. row ***************************
Host: localhost
Db: test
User: zhang
Table_name: emp
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Select,Insert,Update,Delete,Create,Grant,References,Index,Create View,Show view,Trigger
Column_priv:
1 row in set (0.00 sec)