Write by lyc at 2018-04-09
Modify by lyc at 2018-11-05

一、mysql用户权限

1.常用用户权限说明

1
2
# 所有权限表字段展示
mysql > show privileges;
  • 全局-只读权限：SELECT
  • 局部业务库，一般开发者账号权限：INSERT,UPDATE,DELETE,SELECT,Execute
  • 全局，管理用户权限：INSERT,UPDATE,DELETE,SELECT,CREATE,INDEX,ALTER,DROP,FILE,PROCESS,SHUTDOWN,SUPER
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Alter 　　          修改表和索引
Create 　           创建数据库和表
Delete 　           删除表中已有的记录
Drop 　　           删除数据库和表
INDEX 　            创建或删除索引
Insert 　           向表中插入新行
Select　　　        查看表
Update 　　         修改现存表记录
FILE 　　　         允许用户在MySQL服务器上的文件系统中读取和写入文件
PROCESS 　　        show processlist;没给这个权限看不到别的用户的连接
RELOAD 　　         重载授权表或清空日志、主机缓存或表缓存。
SHUTDOWN　　        关闭服务器
ALL 　　　　        所有；ALL PRIVILEGES同义词
USAGE 　　　        特殊的“无权限”权限
SUPER
alter routine       修改与删除存储过程/函数
create routine      创建存储过程/函数
execute             调用存储过程/函数
Replication client  只能执行show slave status\G
Replication slave   可以读取远程binlog到本地
Lock tables         flsuh table with read lock;全局锁表
Grant option        给别的用户授权的权限

2.MySQL权限控制表

表 说明
mysql.user 每个创建的用户都会有一条信息
mysql.db 限制用户作用域特定的DB
mysql.tables_priv 用于表级别的权限限制
mysql.procs_priv 用于存储过程和函数权限限制

3.为什么要flush privileges;

  • MySQL启动时从mysql库中把权限读取加载到内存中
  • 创建用户的本质是修改mysql.user表的数据
  • flush privileges;动作就是把数据刷新到内存中

二、mysql查看用户权限

1.查看用户列表与密码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 查看用户权限（常用）
mysql > SELECT user,host FROM mysql.user;

# MySQL5.7查看用户的密文密码串
mysql > select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
# mysql.session  内置账号，保留
# mysql.sls      mysql sys库的内置账号，保留

# MySQL5.6查看密码
mysql > select user,host,password from mysql.user;

2.查看用户的权限

1
2
3
4
5
6
7
8
9
# 查看指定用户的权限
mysql > show grants for 'root'@'localhost';     

# 查看当前登录用户拥有的权限
mysql > show grants;                            
mysql > show grants for current_user();

# 查看连接源过来的账号，查看创建的账号
mysql > select user(),current_user();

三、mysql删除用户

1
2
3
4
5
6
7
8
9
10
# drop删除用户
mysql > DROP USER 'user_name'@'host';
mysql > flush privileges;

# REVOKE回收blog用户的INSERT权限
mysql > REVOKE INSERT ON *.* FROM 'blog'@'192.168.%.%';
mysql > FLUSH PRIVILEGES;

# 用户更名：更改用户名和主机名部分，保留权限
mysql > rename user 'opslook'@'%' to 'opslook'@'192.168.%.%';

四、迁移mysql.user用户权限表

方式一：物理迁移

mysql.user的引擎是MySIAM表，在同版本下可以直接cp到其他实例来完成用户权限迁移。

方式二：逻辑导出sql

MySQL5.6 用户权限导出到sql

1
2
3
4
5
6
7
8
9
10
$ cd /tmp/
$ mysql -B -N -uroot -p -S /data/mysql_data_3308/mysql_3308.sock -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user NOT IN('root','','mysql.session','mysql.sys','checksum','mysqlbackup','mysqlcheck','opslook','zabbix')" mysql  > mysql_all_users.txt

while read line
do
    mysql -B -N -uroot -pmypw.td...^@014 -S /data/mysql_data_3308/mysql_3308.sock -e "SHOW GRANTS FOR $line";
done < /tmp/mysql_all_users.txt > /tmp/mysql_all_users.sql

sed -i 's/$/;/' mysql_all_users.sql
echo "flush privileges;" >> mysql_all_users_sql.sql

五、常用业务权限开通

1.*.* 权限：项目独享单实例

Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

某个项目独占一个MySQL实例，权限级别可以给 *.*，因为实例下的所有database都是这个项目的。

1
2
3
mysql > create user 'username'@'10.19.%.%' identified by '123456';
mysql > GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON *.* TO 'username'@'10.19.%.%';
mysql > flush privileges;

2.db.*：多个项目共享单实例

多个项目共享单实例：

  • 各个项目创建独立的 user，切权限级别限制到 db.*，防止权限乱窜
  • 另外建个admin用户用于部署 view, event, route, index 等。
1
2
3
4
5
6
7
8
9
# 创建app用的用户：
mysql > create user 'username'@'10.19.%.%' identified by '123456';
mysql > GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON dbname.* TO 'username'@'10.19.%.%';
mysql > flush privileges;

# 创建管理用户
mysql > create user 'admin'@'10.19.%.%' identified by '123456';
mysql > GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON *.* TO 'admin'@'10.19.%.%';
mysql > flush privileges;