MySQL如何进行权限管理,如何让不同的用户具有操作不同的数据库或表的权限?
本文介绍了如何在MySQL中进行权限控制,所谓的权限控制,是指控制连接数据库的某个用户所具有的的某些权限。包括全局权限控制,数据库权限控制,表和列的权限控制。通过精细化的权限控制,能实现相应的隔离,也避免了某个用户权限过大造成删库跑路的情况。
之前遇到过这么一个需求:由于某个系统是A、B两个公司一起负责开发的,当要在生产服务器上进行部署测试时,由于数据库用的是同一个,A公司要求B公司不能看到A公司所负责的那一块业务所对应的数据库或者是表。当时对MySQL使用得不熟,现在想来这就可以通过权限控制来进行管理。
如下将分别介绍如何进行全局的权限控制、数据库权限控制、表以及列权限控制。本文演示中使用的 MySQL 的版本为5.7。MySQL 运行在 docker 中,服务器的系统版本为 Centos 8。
全局权限
全局权限,作用于整个 MySQL 实例,这些权限信息保存在 mysql 库的 user 表里。进入MySQL,在 mysq l.user
表中记录了MySQL的用户所对应的全局权限。
使用 select * from mysql.user \G
命令可以查看到如下结果。如下是 root 用户所对应的权限,以 Select_priv 这个权限为例,这里写上了 Y,就表示有这个权限,如果是 N 则表示没有这个权限
*************************** 4. row ***************************
Host: %
User: root
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: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
password_expired: N
password_last_changed: 2021-05-01 03:53:13
password_lifetime: NULL
account_locked: N
4 rows in set (0.00 sec)
mysql>
如下,我新创建一个用户 user1,设置用户密码为 user1。需要注意的是在MySQL 里面,用户名 (user)+ 地址 (host) 才表示一个用户,因此 user1@ip1 和 user1@ip2 代表的是两个不同的用户。
mysql> create user 'user1'@'%' identified by 'user1';
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from mysql.user \G
*************************** 1. row ***************************
count(*): 5
1 row in set (0.00 sec)
可以看到由于没有给 user1 这个用户赋予任何权限,所以该用户对应的全局权限都是N。
mysql> select * from mysql.user where User='user1' \G
*************************** 1. row ***************************
Host: %
User: user1
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: *34D3B87A652E7F0D1D371C3DBF28E291705468C4
password_expired: N
password_last_changed: 2021-05-17 07:13:55
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
mysql>
如果我用一些数据库管理工具,使用该user1这个用户连接上这个数据库,会发现查看任何一张表的权限都没有。
接着,我以 root 用户的身份,使用 grant 命令将所有权限都赋予给了 user1 这个用户,然后 Navicat 这里断开再重新连接(以user1的身份进行登录),会发现就能打开相应的表,查看相应的数据了。当然,在生产环境下,通常都不允许将所有的权限给某个数据库用户,除非它是管理员。
mysql> grant all privileges on *.* to 'user1'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
需要注意的是:在这个 grant 命令执行完成后,如果有新的客户端通过 user1 这个用户登录成功,MySQL 会为新连接维护一个线程对象,然后从 acl_users 数组里查到这个用户的权限,并将权限值拷贝到这个线程对象中。之后在这个连接中执行的语句,所有关于全局权限的判断,都直接使用线程对象内部保存的权限位。这就类似与MySQL在可重复读(RR)隔离级别下的事务,开启了一个事务之后,不管其它事务咋个修改数据,只要这个事务没提交,看到的是数据都是一致的。因此,对于已存在的连接,修改了全局权限,需要在该连接中执行 flush privileges 语句来刷新权限,该连接中才能获取最新的权限状态。
如果要回收user1的所有权限,就可以使用revoke这个命令
revoke all privileges on *.* from 'ua'@'%';
数据库权限
数据库权限,对应着某个用户操作某个数据库,或者其中表的权限。
如下命令使得user1具有了操作db1下所有的表的权限。
grant all privileges on db1.* to 'user1'@'%' with grant option;
客户端断开之前的连接再重新连接,会发现此时 user1 只拥有db1下的所有权限,能查看 db1 下 t 表的所有数据,但是看不了 db2 下 t 表的数据。
而用户所具有的数据库的权限需要在 mysql.db 这张表中查看,如下可以看出,user1具有操作 db1 的所有权限。
mysql> select * from mysql.db where user = 'user1' \G
*************************** 1. row ***************************
Host: %
Db: db1
User: user1
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
1 row in set (0.01 sec)
mysql>
如果我们只想授予用户部分权限,比如仅仅只允许用户读(SELECT)某一张表呢?
数据库所对应的权限如下图所示。
如下,仅仅授予 use1 对db1下所有表进行 SELECT 的权限。
mysql> grant SELECT on db1.* to 'user1'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql>
可以通过查看 mysql.db 这个表查看到 user1 具有了对 db1 Select 的权限。
同样,断开连接以后再重连,发现可以查看 t 这个表的数据,但是不能做任何修改。
如果想回收 user1 所具有的 db1 的 SELECT 权限,可以这么写
mysql> revoke SELECT on db1.* from 'user1'@'%';
Query OK, 0 rows affected (0.00 sec)
注意:db1.* 表示 db1 下所有的表,如果只授予用户操作数据库 db1 下某一张表的权限,可以这么写:比如 db1.t1
如果想回收user1 操作 db1 的所有权限,可以这么写
mysql> revoke all privileges on db1.* from 'user1'@'%';
Query OK, 0 rows affected (0.00 sec)
表权限
表权限记录在 mysql.tables_priv 这个表中。
表对应的权限有如下内容
在 db1 这个数据库下,除了t这张表以外,又重新新建了几张表,以 create table t2 like t 的形式创建,如下:
mysql> show tables from db1;
+---------------+
| Tables_in_db1 |
+---------------+
| t |
| t2 |
| t3 |
| t4 |
+---------------+
4 rows in set (0.00 sec)
mysql>
先回收之前授予 user1 操作 db1下所有表的权限。
mysql> revoke all privileges on db1.* from 'user1'@'%';
Query OK, 0 rows affected (0.00 sec)
现在授予 user1 一些操作 db1.t2 的权限,如下仅仅授予了该用户SELECT(id) 以及I NSERT(id,a) 的权限。
mysql> show columns from t2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| a | int(11) | YES | MUL | NULL | |
| b | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> GRANT SELECT(id), INSERT (id,a) ON db1.t2 TO 'user1'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)
客户端断开又重连,db1下所有的表都不能在可视化界面显示,因为都没有SELECT * 的权限。仅仅是db1下的 t2具有一定的操作权限。
可以通过查看 mysql.tables_priv 这张表查看user1所具有的table的权限
需要注意的是,修改表的权限与修改数据库的权限类似。每次 grant 的时候都会修改数据表,也会同步修改内存中的 hash 结构。因此,对这两类权限的操作,也会马上影响到已经存在的连接,因此修改表与数据库的权限不需要执行 flush privileges 语句来刷新权限。
列权限
在查看 mysql.tables_priv 表权限的时候,可以发现已经可以查看到列权限。修改表权限时,实际上已经是在操作列权限了。
详细的,也可以通过查看 mysql.columns_priv 这个表,查看某个用户所具有的列权限。
mysql> select * from mysql.columns_priv where user = 'user1' \G
*************************** 1. row ***************************
Host: %
Db: db1
User: user1
Table_name: t2
Column_name: id
Timestamp: 0000-00-00 00:00:00
Column_priv: Select,Insert
*************************** 2. row ***************************
Host: %
Db: db1
User: user1
Table_name: t2
Column_name: a
Timestamp: 0000-00-00 00:00:00
Column_priv: Insert
2 rows in set (0.00 sec)
mysql>
参考:
开放原子开发者工作坊旨在鼓励更多人参与开源活动,与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动,如meetup、训练营等,主打技术交流,干货满满,真诚地邀请各位开发者共同参与!
更多推荐
所有评论(0)