0MySQL 权限控制的解决办法

目录

• mysql权限控制
• 权限系统表
• MySQL修改权限之后的生效方法
• 赋予权限与回收权限
• 一个特殊例子

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)

MySQL 权限控制分析

可以看到，第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 权限控制分析

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

代码如下：

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)

MySQL 权限控制分析

以上就是MySQL 权限控制详解的详细内容，更多关于MySQL 权限控制的资料请关注512笔记其它相关文章！