专栏首页DBA随笔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)

可以看到,第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)

本文分享自微信公众号 - DBA随笔(gh_acc2bbc0d447),作者:AsiaYe

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-03-11

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL Select语句是怎么执行的?

    最近在极客时间看丁奇大佬的《MySQL45讲》,真心觉得讲的不错,把其中获得的一些MySQL方向的经验整理整理分享给大家,有兴趣同学可以购买相关课程进行学习。

    AsiaYe
  • shell脚本中$符号的意义

    今天是远程办公的第4天,渐渐的找到了一点在家办公的节奏了,改了几个shell脚本,在改动过程中,发现还有一些知识点需要巩固,这里写出来,加深下印象。

    AsiaYe
  • MySQL全文索引的概念

    今天下午下班去吃饭,吃完饭坐电梯上三楼,竟然被困在电梯里面了,当时的感觉还是很刺激的,电梯上升着,突然就掉下来了,像跳楼机一样,突出一个刺激,索性只掉了...

    AsiaYe
  • Android权限机制,你真的了解吗?

    一、Android的权限机制 Android是目前最流行的智能手机软件平台之一,在智能移动终端如火如荼发展的同时,其安全态势也日益严峻。有调查表明,恶意软件的...

    腾讯移动品质中心TMQ
  • 【Unity游戏开发】Android6.0以上的动态权限申请问题

      最近公司的游戏在做安全性测试,期间也暴露出了不少安全上的问题。虽然我们今天要说的权限申请和安全性相关不大,但是也会影响到游戏的使用体验等,所以本篇博客中马三...

    马三小伙儿
  • Android动态权限申请(Kotlin版)

    Android6.0之后,APP有些用到的权限需要动态申请,虽然这个功能已经出来好久了,但一直没有研究过,只知道用法简单,到时候copy一下就好了,github...

    haifeiWu
  • 聊一聊Android 6.0的运行时权限

    Android 6.0,代号棉花糖,自发布伊始,其主要的特征运行时权限就很受关注。因为这一特征不仅改善了用户对于应用的使用体验,还使得应用开发者在实践开发中需要...

    技术小黑屋
  • Android 6.0动态获取权限

    Android系统的市场占有率不断攀升,智能手机的全面普及。这也给恶意软件、木马程序滋生提供了丰富营养。这些恶意软件、木马程序会对用户的敏感数据进行读写操作,用...

    猴哥yuri
  • 提权(2) windows权限分析

    lonelyvaf
  • 位运算的运用

    1.linux当中的位 在linux当中我们经常说777、775、755这个代表什么呢?其实很简单! 我们用1表示拥有执行权限,2表示拥有写入权限,4表...

    苦咖啡

扫码关注云+社区

领取腾讯云代金券