转自: https://blog.csdn.net/yeahPeng11/article/details/121584343

一、MySQL用户权限

MySQL版本5.7

背景

在开发过程中数据库安装在云服务器，本地连接阿里云服务器中的MySQL就不能直接root用户连接，而每次数据库操作都要使用新建的用户与用户进行交互操作。

在使用非root用户的时，执行本地的sql文件，就需要一些权限，比如 SELECT,INSERT,UPDATE,DELETE,CREATE 等等权限

添加MySQL用户并设置权限的好处：新的SQL用户不允许访问访问属于其他SQL用户的库或表，甚至不能使用SELECT语句。新的SQL用户必须显式的被授予权限，才能执行对应的操作。

二、用户权限介绍

1.权限级别

全局：可以管理整个MySQL
数据库：可以管理指定的数据库
数据表：可以管理指定数据库的指定表
字段：可以管理指定数据库的指定表的指定字段

权限存储在mysql库的user,db,tables_priv,columns_priv,procs_priv这几个系统表中，待MySQL实例启动后就加载到内存中，实现用户的权限控制。

2.权限实现

MySQL权限实现分为两段验证：

第一阶段：服务器首先会检查此用户是否允许连接。先从user表中的Host,User,Password这3个字段中判断连接的ip、用户名、密码是否存在，存在则通过验证。

第二阶段：通过身份验证后，用户发起的每个请求都需要进行权限判断，按照 user，db，tables_priv，columns_priv，procs_priv 的顺序进行验证。即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db, tables_priv,columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表。以此类推。

3.权限分布

MYSQL的权限如何分布，就是针对表可以设置什么权限，针对列可以设置什么权限等等，这个可以从官方文档中的一个表来说明：

权限分布	可能的设置的权限
表权限	‘Select’, ‘Insert’, ‘Update’, ‘Delete’, ‘Create’, ‘Drop’, ‘Grant’, ‘References’, ‘Index’, ‘Alter’
列权限	‘Select’, ‘Insert’, ‘Update’, ‘References’
过程权限	‘Execute’, ‘Alter Routine’, ‘Grant’

MySQL 账号服务器权限查询表

权限	权限级别	说明
CREATE	数据库、表或索引	创建数据库、表或索引权限
DROP	数据库或表	删除数据库或表权限
GRANT OPTION	数据库、表或保存的程序	赋予权限选项
REFERENCES	数据库或表
ALTER	表	更改表，比如添加字段、索引等
DELETE	表	删除数据权限
INDEX	表	索引权限
INSERT	表	插入权限
SELECT	表	查询权限
UPDATE	表	更新权限
CREATE VIEW	视图	创建视图权限
SHOW VIEW	视图	查看视图权限
ALTER ROUTINE	存储过程	更改存储过程权限
CREATE ROUTINE	存储过程	创建存储过程权限
EXECUTE	存储过程	执行存储过程权限
FILE	访问服务器中的文件	文件访问权限
CREATE TEMPORARY TABLES	服务器管理	创建临时表权限
LOCK TABLES	服务器管理	锁表权限
CREATE USER	服务器管理	创建用户权限
PROCESS	服务器管理	查看进程权限
RELOAD	服务器管理	执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限
REPLICATION CLIENT	服务器管理	复制权限
REPLICATION SLAVE	服务器管理	复制权限
SHOW DATABASES	服务器管理	查看数据库权限
SHUTDOWN	服务器管理	关闭数据库权限
SUPER	服务器管理	执行kill线程权限

4.查询权限表

查看用户MySQL用户

1	select user,host from mysql.user;

查看root用户在权限表中的权限

前一个表为N，系统才会去检查下一个表。

# Y表示有权限 ，N表示无权限
# 1.mysql.user表 (all)
select * from mysql.user where user='root';

# 2.mysql.db表 (empty)
select * from mysql.db where user='root';

# 3.mysql.tables_priv (empty)
select * from mysql.tables_priv where user='root';

# 4.mysql.colums_priv表 (empty)
select * from mysql.columns_priv where user='root';

# 5.mysql.procs_priv (empty)
select * from mysql.procs_priv where user='root';

三、用户权限实战

以下所有操作都是以为root用户，在mysql库中进行。

1.查看用户权限信息

查看当前用户

1	select user();

查看MYSQL有哪些用户

1	select user,host from mysql.user;

查看已经授权给用户的权限信息

1	show grants for 'pdh'@'%';

2.用户创建和授权

简单说一下MySQL的授权用户组成： 'user_name'@'host_name'（中间使用@符号连接）。其中user_name表示用户名，host_name表示主机，可以是ipv4和ipv6格式的，%表示所有主机均可访问。下面列举一下不同的格式表示不同的主机：

user_name	host_name	说明
‘pdh’	‘198.51.100.177’	pdh，只能从此ip连接
‘pdh’	‘198.51.100.%’	pdh，从`198.51.100` 子网中的任何主机
‘pdh’	‘%’	pdh，任何主机可连

创建MySQL用户和权限*

# 1.使用CREATE创建用户，后再授权
# 1.1 创建 pdh 用户,设置密码为123456，并没有权限
CREATE USER 'pdh'@'%' IDENTIFIED BY '123456';
# 1.2 授予pdh查询和添加test库的权限
grant select,insert,update,delete,create,alter on test.* to 'pdh';

# 2.使用GRANT创建用户并授权test库的所有操作
grant all privileges on test.* to 'pdh'@'%' identified by "123456" with grant option;

以上指令说明

1. ALL PRIVILEGES 是表示所有权限，你也可以使用select、update等权限。
2. ON 用来指定权限针对哪些库和表
3. test.*  表示test库的所有表
4. TO 表示将权限赋予某个用户。
5. 'pdh'@'%' 表示pdh用户，主机为%。主机可以是IP、IP段、域名以及%
6. IDENTIFIED BY 指定用户的登录密码
7. WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人

刷新权限

使用这个命令使权限生效，对权限表user、db、host等做了update或者delete更新的时候务必执行权限刷新。

1	flush privileges;

查看和修改权限

查看当前用户权限

1	show grants;

查看某个用户权限

1	show grants for 'pdh'@'%';

回收权限

# 回收alter权限
revoke alter on test.* from 'pdh'@'%';
# 回收所有权限
revoke all privilegeson test.* from 'pdh'@'%';

3. mysql数据库将某一个表的查询权限授予给所有用户

-- 要把一个表(如表table1)的select权限授予所有用户，需要自己遍历所有用户。表Table1的权限，可以通过:
select * from mysql.tables_priv where Table_name = 'table1';
-- 可以把所有用户读至游标，遍历所有用户，并用grant语句分别授权
delimiter $$
create procedure grant_select_table1_to_pubic()
begin
    -- 定义变量，用于储存用户名：
    declare v_user char(32);
    -- 定义结束标志
    DECLARE done INT DEFAULT FALSE;
    -- 定义游标，这里只演示普通用户：
    declare cur_user cursor for
        select user
        from mysql.user
        where User <> 'root'
          and user not like '%.%'
          and user <> '';
    -- 定义 NOT FOUND类异常的HANDLER：
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    -- 打开游标
    open cur_user;
    -- 取游标的一行数据到变量(取一个用户名)，并推进游标
    fetch cur_user into v_user;
    -- 每取一个user，构造一条授权语句，然后执行这条语句
    -- (因为grant语句不接受变量，它会把变量名当成一个用户名，所以只能构造整条语句)
    while not done
        do
            set @query = CONCAT('GRANT SELECT ON table1 to ', v_user);
            prepare statement from @query;
            execute statement;
            deallocate prepare statement;
            -- 取下一个用户
            fetch cur_user into v_user;
        end while;
    close cur_user;
end$$;
-- 调用过程，授权给所用用户:
call grant_select_table1_to_pubic();
-- 验证表“table1”的SELECT权限
select * from mysql.tables_priv where Table_name = 'table1';