【MySQL】MySQL之权限管理
MySQL有哪些权限?
https://blog.****.net/zhouhao88410234/article/details/79245544
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.****.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
########################################
# 项目业务用户需要用到的权限(管理类不要,结构类的CREATE、ALTER和DROP不需要,其他都要)
INSERT,SELECT,UPDATE,DELETE,INDEX,CREATE TEMPORARY TABLES,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,EXECUTE,CREATE VIEW,EVENT,TRIGGER
# 定义一个grant_opts
grant_opts="INSERT,SELECT,UPDATE,DELETE,INDEX,CREATE TEMPORARY TABLES,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,EXECUTE,CREATE VIEW,EVENT,TRIGGER"
mysql -u root -p"[email protected]" -e "CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;"
mysql -u root -p"[email protected]" -e "GRANT ${grant_opts} ON test_db.* TO [email protected]'192.168.1.%' IDENTIFIED BY 'test_password';"
mysql -u root -p"[email protected]" -e "show grants for [email protected]'192.168.1.%';"
# docker exec -it mysql mysql -u root -p"[email protected]" -e "show grants for [email protected]'localhost';"
# docker exec -it mysql mysql -u root -p"[email protected]" -e "show grants for [email protected]'%';"
docker exec -it mysql mysql -u root -p"[email protected]" -e "show grants for [email protected]'192.168.%.%';"
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是全局权限,针对的是*.*,而不能单独对某个库、表授权,否则会报错!