MySQL有哪些权限?
https://blog.csdn.net/zhouhao88410234/article/details/79245544
Privileges Supported by MySQL
https://dev.mysql.com/doc/refman/5.5/en/grant.html
MySQL权限管理
https://docs.lvrui.io/2017/03/21/MySQL%E6%9D%83%E9%99%90%E7%AE%A1%E7%90%86
注意:
1. 管理权限(如 super, process, file等)不能够指定某个数据库,on后面必须跟 *.*
2. truncate权限呢,其实truncate权限就是create+drop的权限组合
MySQL包含哪些权限,共29个
MySQL用户管理及SQL语句详解
https://cloud.tencent.com/developer/article/1007983
数据库中DQL、DML、DDL、DCL的概念与区别
https://blog.csdn.net/sinat_25059791/article/details/69666318
MySQL之权限管理
https://www.cnblogs.com/richardzhu/p/3318595.html
Mysql Grant 用户权限总结
https://blog.51cto.com/zlyang/1785513
MySQL权限图
#####################################
#数据类
INSERT
SELECT
UPDATE
DELETE
#结构类
CREATE
ALTER
INDEX
DROP
CREATE TEMPORARY TABLES
SHOW VIEW
CREATE ROUTINE
ALTER ROUTINE
EXECUTE
CREATE VIEW
EVENT
TRIGGER
# 管理类
GRANT
SUPER
PROCESS
FILE
RELOAD
SHUTDOWN
SHOW DATABASES
LOCK TABLES
REFERENCES
REPLICATION CLIENT
REPLICATION SLAVE
CREATE USER
########################################
# 项目业务用户需要用到的权限(一般地,业务账号除了管理类不要,其他类都要)
INSERT,SELECT,UPDATE,DELETE,CREATE,ALTER,INDEX,DROP,CREATE TEMPORARY TABLES,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,EXECUTE,CREATE VIEW,EVENT,TRIGGER
# 定义一个grant_opts变量
grant_opts="INSERT,SELECT,UPDATE,DELETE,CREATE,ALTER,INDEX,DROP,CREATE TEMPORARY TABLES,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,EXECUTE,CREATE VIEW,EVENT,TRIGGER"
mysql -u root -p"MySQL@123" -e "CREATE DATABASE IF NOT EXISTS zabbix DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;"
mysql -u root -p"MySQL@123" -e "GRANT ${grant_opts} ON zabbix.* TO zabbix@'192.168.1.%' IDENTIFIED BY 'Zabbix@123';"
# mysql -u root -p"MySQL@123" -e "flush privileges;"
mysql -u root -p"MySQL@123" -e "show grants for zabbix@'192.168.1.%';"
注意:
这里我们只是将zabbix看做一个普通业务账号,跟具体搭建zabbix服务没有关系
实际测试中发现,如果用zabbix用户往zabbix库中刷zabbix官方提供的初始化sql,在上面的基础还需要加一个管理类权限 REFERENCES
否则,无法成功执行初始化脚本,报错如下:
如果给 grant_opts 加一个管理类权限 REFERENCES
grant_opts="INSERT,SELECT,UPDATE,DELETE,CREATE,ALTER,INDEX,DROP,CREATE TEMPORARY TABLES,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,EXECUTE,CREATE VIEW,EVENT,TRIGGER,REFERENCES"
综上,根据业务sql,业务账号的权限中,可能也需要某些管理类权限,不能一概而论,认为普通业务账号就一定不需要任何管理类权限!
# docker exec -it mysql mysql -u root -p"MySQL@123" -e "show grants for root@'localhost';"
# docker exec -it mysql mysql -u root -p"MySQL@123" -e "show grants for root@'%';"
MySQL 数据库赋予用户权限操作表
https://www.cnblogs.com/goodhacker/p/3373213.html
MySQL ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
https://www.cnblogs.com/rangle/p/8715866.html
https://www.cnblogs.com/tianshupei88/p/5075367.html
注意:FILE是全局权限,针对的是*.*,而不能单独对某个库、表授权,否则会报错!