之前遇到过这么一个需求:由于某个系统是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> 

参考:

林晓斌 2019-02-1842 | grant之后要跟着flush privileges吗?

Logo

开放原子开发者工作坊旨在鼓励更多人参与开源活动,与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动,如meetup、训练营等,主打技术交流,干货满满,真诚地邀请各位开发者共同参与!

更多推荐