mysql用户管理与权限分配( Account Management Statements)学习笔记

mysql账号(用户)管理语句( Account Management Statements)学习笔记

mysql 官方文档

https://dev.mysql.com/doc/refman/5.7/en/account-management-statements.html

13.7.1.1 ALTER USER Statement
13.7.1.2 CREATE USER Statement
13.7.1.3 DROP USER Statement
13.7.1.4 GRANT Statement
13.7.1.5 RENAME USER Statement
13.7.1.6 REVOKE Statement
13.7.1.7 SET PASSWORD Statement

学习笔记

常用操作

-- 创建用户 => 操作影响到`mysql`.`user`表
CREATE USER 'test_user'@'%';
CREATE USER 'test_user'@'%' IDENTIFIED WITH mysql_native_password BY 'test_password';
CREATE USER IF NOT EXISTS 'test_user'@'%' IDENTIFIED WITH mysql_native_password BY 'test_password';
-- 修改用户密码 => 操作影响到`mysql`.`user`表
ALTER USER 'test_user' IDENTIFIED BY 'test_password';
ALTER USER IF EXISTS 'test_user' IDENTIFIED BY 'test_password';
ALTER USER 'test_user' IDENTIFIED WITH mysql_native_password BY 'test_password';
ALTER USER IF EXISTS 'test_user' IDENTIFIED WITH mysql_native_password BY 'test_password';
-- 删除用户 => 操作影响到`mysql`.`user`表
DROP USER 'test_user'@'%';
DROP USER IF EXISTS 'test_user'@'%';
-- 授予权限
GRANT ALL PRIVILEGES ON *.* TO 'test_user'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'test_user'@'%' IDENTIFIED WITH mysql_native_password BY 'test_password'; 
-- 取消授权
REVOKE ALL PRIVILEGES ON *.* FROM 'test_user'@'%' ;
-- 刷新权限
FLUSH PRIVILEGES;
-- 查询所有用户
SELECT DISTINCT CONCAT(user,'@',host) AS query FROM mysql.user;

授权

服务器权限

-- 添加所有权限(服务器权限)
GRANT ALL PRIVILEGES ON *.* TO 'test_user'@'%';
-- 删除所有权限(服务器权限)
REVOKE ALL PRIVILEGES ON *.* FROM 'test_user'@'%' ;
-- 添加指定权限(服务器权限)
GRANT INSERT,DELETE,SELECT,UPDATE ON *.* TO 'test_user'@'%';
-- 删除指定权限(服务器权限)
REVOKE INSERT,DELETE,SELECT,UPDATE ON *.* FROM 'test_user'@'%' ;
-- 查询权限(服务器权限)
SELECT * FROM `mysql`.`user` WHERE `User` = 'test_user' AND `Host` = '%';
-- 刷新权限
FLUSH PRIVILEGES;

数据库权限

-- 添加所有权限(数据库权限) => 操作影响到`mysql`.`db`表
GRANT ALL PRIVILEGES ON `test_database`.* TO 'test_user'@'%' ; 
-- 删除所有权限（数据库级别） => 操作结响到`mysql`.`db`表
REVOKE ALL PRIVILEGES ON `test_database`.* FROM 'test_user'@'%' ;
-- 添加指定权限(数据库权限) => 操作影响到`mysql`.`db`表
GRANT INSERT,DELETE,SELECT,UPDATE ON `test_database`.* TO 'test_user'@'%' ; 
-- 删除指定权限(数据库权限) => 操作结响到`mysql`.`db`表
REVOKE INSERT,DELETE,SELECT,UPDATE ON `test_database`.* FROM 'test_user'@'%' ;
-- 查询权限(数据库权限) 
SELECT * FROM `mysql`.`db` WHERE `User` = 'test_user' AND `Host` = '%';
-- 刷新权限
FLUSH PRIVILEGES;

数据表权限

-- 添加所有权限(数据库权限) => 操作影响到`mysql`.`db`表
GRANT ALL PRIVILEGES ON `test_database`.`test_table` TO 'test_user'@'%' ; 
-- 删除所有权限（数据库级别） => 操作结响到`mysql`.`db`表
REVOKE ALL PRIVILEGES ON `test_database`.`test_table` FROM 'test_user'@'%' ;
-- 添加指定权限(数据库权限) => 操作影响到`mysql`.`db`表
GRANT INSERT,DELETE,SELECT,UPDATE ON `test_database`.`test_table` TO 'test_user'@'%' ; 
-- 删除指定权限(数据库权限) => 操作结响到`mysql`.`db`表
REVOKE INSERT,DELETE,SELECT,UPDATE ON `test_database`.`test_table` FROM 'test_user'@'%' ;
-- 查询权限(数据库权限) 
SELECT * FROM `mysql`.`db` WHERE `User` = 'test_user' AND `Host` = '%' AND `Table_name` = 'test_table';
-- 刷新权限
FLUSH PRIVILEGES;

指定权限列表

Privilege	Meaning and Grantable Levels
ALL [PRIVILEGES]	Grant all privileges at specified access level except GRANT OPTION and PROXY.
USAGE	Synonym for “no privileges”
GRANT OPTION	Enable privileges to be granted to or removed from other accounts. Levels: Global, database, table, routine, proxy.
ALTER	Enable use of ALTER TABLE. Levels: Global, database, table.
ALTER ROUTINE	Enable stored routines to be altered or dropped. Levels: Global, database, routine.
CREATE	Enable database and table creation. Levels: Global, database, table.
CREATE ROUTINE	Enable stored routine creation. Levels: Global, database.
CREATE TABLESPACE	Enable tablespaces and log file groups to be created, altered, or dropped. Level: Global.
CREATE TEMPORARY TABLES	Enable use of CREATE TEMPORARY TABLE. Levels: Global, database.
CREATE USER	Enable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES. Level: Global.
CREATE VIEW	Enable views to be created or altered. Levels: Global, database, table.
DROP	Enable databases, tables, and views to be dropped. Levels: Global, database, table.
SELECT	Enable use of SELECT. Levels: Global, database, table, column.
INSERT	Enable use of INSERT. Levels: Global, database, table, column.
UPDATE	Enable use of UPDATE. Levels: Global, database, table, column.
DELETE	Enable use of DELETE. Level: Global, database, table.
EXECUTE	Enable the user to execute stored routines. Levels: Global, database, routine.
TRIGGER	Enable trigger operations. Levels: Global, database, table.
EVENT	Enable use of events for the Event Scheduler. Levels: Global, database.
FILE	Enable the user to cause the server to read or write files. Level: Global.
INDEX	Enable indexes to be created or dropped. Levels: Global, database, table.
LOCK TABLES	Enable use of LOCK TABLES on tables for which you have the SELECT privilege. Levels: Global, database.
PROCESS	Enable the user to see all processes with SHOW PROCESSLIST. Level: Global.
PROXY	Enable user proxying. Level: From user to user.
REFERENCES	Enable foreign key creation. Levels: Global, database, table, column.
RELOAD	Enable use of FLUSH operations. Level: Global.
REPLICATION CLIENT	Enable the user to ask where source or replica servers are. Level: Global.
REPLICATION SLAVE	Enable replicas to read binary log events from the source. Level: Global.
SHOW DATABASES	Enable SHOW DATABASES to show all databases. Level: Global.
SHOW VIEW	Enable use of SHOW CREATE VIEW. Levels: Global, database, table.
SHUTDOWN	Enable use of mysqladmin shutdown. Level: Global.
SUPER	Enable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command. Level: Global.