mysql权限控制

AsiaYe

发布于 2019-11-06 08:50:25

3K00

代码可运行

文章被收录于专栏：DBA随笔DBA随笔

运行总次数：0

代码可运行

mysql权限控制作为一名DBA，想必大家对MySQL中的权限都不陌生，MySQL中对于权限的控制分为三个层面：

全局性的管理权限，作用于整个MySQL实例级别
数据库级别的权限，作用于某个指定的数据库上或者所有的数据库上
数据库对象级别的权限，作用于指定的数据库对象上(表、视图等)或者所有的数据库对象上

这里，我们将mysql中的所有权限列出来，最后给出一个特殊的案例来反应mysql权限控制中的一个小bug。首先来看权限列表，权限的顺序按照首字母的顺序进行排列：

•All/All Privileges
该权限代表全局或者全数据库对象级别的所有权限
•Alter
该权限代表允许修改表结构的权限，但必须要求有create和insert权限配合。
如果是rename表名，则要求有alter和drop原表，create和insert新表的权限
•Alter routine
该权限代表允许修改或者删除存储过程、函数的权限
•Create
该权限代表允许创建新的数据库和表的权限
•Create routine
该权限代表允许创建存储过程、函数的权限
•Create tablespace
该权限代表允许创建、修改、删除表空间和日志组的权限
•Create temporary tables
该权限代表允许创建临时表的权限
•Create user
该权限代表允许创建、修改、删除、重命名user的权限
•Create view
该权限代表允许创建视图的权限
•Delete
该权限代表允许删除行数据的权限
•Drop
该权限代表允许删除数据库、表、视图的权限，包括truncate table命令
•Event
该权限代表允许查询，创建，修改，删除MySQL事件
•Execute
该权限代表允许执行存储过程和函数的权限
•File
该权限代表允许在MySQL可以访问的目录进行读写磁盘文件操作，
可使用的命令包括load data infile,select ... into outfile,load file()函数
•Grant option
该权限代表是否允许此用户授权或者收回给其他用户你给予的权 限
•Index
该权限代表是否允许创建和删除索引
•Insert
该权限代表是否允许在表里插入数据，同时在执行analyze table,optimize table,repair table语句的时候也需要insert权限
•Lock
该权限代表允许对拥有select权限的表进行锁定，以防止其他链接对此表的读或写
•Process
该权限代表允许查看MySQL中的进程信息，比如执行showprocesslist,
•Reference
该权限是在5.7.6版本之后引入，代表是否允许创建外键
•Reload
该权限代表允许执行flush命令，指明重新加载权限表到系统内存中，refresh命令代表关闭和重新开启日志文件并刷新所有的表
•Replication client
该权限代表允许执行show master status,show slave status,show binary logs命令
•Replication slave
该权限代表允许slave主机通过此用户连接master以便建立主从复制关系
•Select
该权限代表允许从表中查看数据，而且select权限在执行update/delete 语句中含有where条件的情况下也是需要的
•Show databases
该权限代表通过执行show databases命令查看所有的数据库名
•Show view
该权限代表通过执行show create view命令查看视图创建的语句mysqladmin processlist, show engine等命令
•Shutdown
该权限代表允许关闭数据库实例，执行语句包括mysqladmin shutdown
•Super
该权限代表允许执行一系列数据库管理命令，包括kill强制关闭某个连接 命令，change master to创建复制关系命令，以及create/alter/drop server等命 令
•Trigger
该权限代表允许创建，删除，执行，显示触发器的权限
•Update
该权限代表允许修改表中的数据的权限
•Usage
该权限是创建一个用户之后的默认权限，其本身代表连接登录权限

权限系统表

权限存储在mysql库的user,db, tables_priv, columns_priv, and procs_priv这几个系统表中，待MySQL实例启动后就加载到内存中 • User表:

存放用户账户信息以及全局级别(所有数据库)权限，决定了来自哪些主机的哪些用户可以访问数据库实例，如果有全局权限则意味着对所有数据库都有此权限 • Db表:

存放数据库级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库 • Tables_priv表:

存放表级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的这个表 • Columns_priv表:

存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的这个字段 • Procs_priv表:

存放存储过程和函数级别的权限

MySQL修改权限之后的生效方法

• 执行Grant,revoke,setpassword,renameuser命令修改权限之后，MySQL会自动将修改后的权限信息同步加载到系统内存中 • 如果执行insert/update/delete操作上述的系统权限表之后，则必须再执行刷新权限命令才能同步到系统内存中，刷新权限命令包括:flush privileges/mysqladmin flush-privileges/mysqladmin reload • 如果是修改tables和columns级别的权限，则客户端的下次操作新权限就会生效 • 如果是修改database级别的权限，则新权限在客户端执行use database命令后生效 • 如果是修改global级别的权限，则需要重新创建连接新权限才能生效 • --skip-grant-tables可以跳过所有系统权限表而允许所有用户登录，只在特殊情况下暂时使用

赋予权限与回收权限

赋予权限的时候通常使用grant语句，以查询为例，具体如下:

grant select on db_name.table_name to user@host;

其中，如果对一个账号授予数据库中的所有表的权限，需要将上述语句中的table_name改为*，如果对数据库中所有数据库中的所有表授予权限，则需要将db_name和table_name都改为*

回收权限通常使用revoke语法，它的使用方法如下，以查询为例：

revoke select on db_name.table_name from user@host;

需要注意的是，它与grant不同之处在于语句最后面由to改为了from。

一个特殊例子

今天在处理线上业务的时候，有这样一个开权限的需求，想从workbench客户端查看存储过程，我当时的第一反应就是直接给execute权限，结果给了execute权限之后，业务方反应还是无法查看存储过程，我过去看了下，连接上数据库之后，显示如下：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| AAA                |
+--------------------+
2 rows in set (0.00 sec)
mysql> use AAA
Database changed
mysql> show procedure status\G
*************************** 1. row ***************************
                  Db: CVS
                Name: ba_get_grow
                Type: PROCEDURE
             Definer: dba_admin@127.0.0.1
            Modified: 2019-02-26 ::
             Created: 2019-02-26 ::
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci

mysql> show create procedure ba_get_grow\G
*************************** 1. row ***************************
           Procedure: ba_get_grow
            sql_mode:
    Create Procedure: NULL
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
 row in set (0.00 sec)

可以看到，第29行的create procedure的值是NULL，而没有显示出来procedure的内容，当然也就无法查看procedure了，这种情况怎么办呢？

想到了mysql.proc表里面包含存储过程的信息，于是通过下面的方法给了mysql.proc表一个只读的权限：

mysql@127.0.0.1:(none) 22:35:07>>grant select on `mysql`.proc to 'user'@'host';
Query OK, 0 rows affected (0.00 sec)

再次登录客户端，可以发现存储过程的内容已经可以看到了，如下：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| AAA                |
+--------------------+
2 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| AAA              |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

mysql> show create procedure ba_get_grow\G
*************************** 1. row ***************************
           Procedure: ba_get_grow
            sql_mode:
    Create Procedure: CREATE DEFINER=`dba_admin`@`127.0.0.1` PROCEDURE `ba_get_grow`(
    in p_table_info varchar(),
    out out_retmsg varchar()
)
BEGIN
    XXXXXXXXX
    XXXXXXXXX
    XXXXXXXXX
    XXXXXXXXX
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
 row in set (0.00 sec)