MySQL DCL 数据控制

1.新建用户

连接到 MySQL 服务器后，管理员或特权用户可以使用 CREATE USER 语句创建新用户。

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 示例
CREATE USER lvlv@"%";
CREATE USER lvlv@"%" IDENTIFIED BY 'lvlv';
CREATE USER lvlv@localhost IDENTIFIED BY 'lvlv';
CREATE USER lvlv@192.168.1.1 IDENTIFIED BY 'lvlv';

username 是将要创建的用户名。

host 指定该用户在哪个主机上可以登录，如果是本地用户可用 localhost，如果想让该用户可以从任意远程主机登录，可以使用通配符 %。

password 是用户的登录密码，密码可以为空，如果为空则该用户可以不需要密码登录 MySQL 服务器。

创建的用户信息存放于 mysql.user 数据表中。

2.删除用户

连接到 MySQL 服务器后，管理员或特权用户可以使用 DROP USER 语句删除指定的用户。

DROP USER 'username'@'host';

-- 示例
DROP USER lvlv@localhost;

删除用户时，主机名要与创建用户时使用的主机名一致。

3.用户授权

在 MySQL 中，要为用户授予数据库操作权限，可以使用 GRANT 语句进行用户授权。GRANT 语句允许管理员或特权用户授予其他用户对数据库、表、列等对象的特定权限。授权后，被授权用户可以执行授予的权限操作。

GRANT privilege_type ON dbname.tablename TO 'username'@'host';

-- 示例
GRANT SELECT, INSERT ON *.* TO lvlv@'%';
GRANT ALL ON *.* TO lvlv@'%';

在授权用户权限之后，要使新权限生效，需要使用刷新权限。

FLUSH PRIVILEGES;

privilege_type 是一个用逗号分隔的赋予用户的权限列表，如 SELECT , INSERT , UPDATE 等（详细列表见该文末）。如果要授予所有权限则使用 ALL。

dbname 为数据库名，tablename 为表名，如果要授予用户对所有数据库和表的权限可用*.*表示所有库表。

使用 GRANT 为用户授权时，如果指定的用户不存在，则会新建该用户并授权。设置允许用户远程访问 MySQL 服务器时，一般使用该命令，并指定密码。

GRANT SELECT, INSERT ON *.* TO lvlv@'%' IDENTIFIED BY '123456';

4.撤销用户权限

在 MySQL 中，要撤销用户的权限，可以使用 REVOKE 语句进行权限撤销。REVOKE 语句允许管理员或特权用户从其他用户中撤销已经授予的权限。

REVOKE privilege_type ON dbname.tablename FROM 'username'@'host';

-- 示例
REVOKE SELECT ON *.* FROM lvlv@'%';
REVOKE ALL ON *.* FROM 'lvlv'@'%';

假如给用户'pig'@'%'授权时使用下面（或类似）这种方式。

GRANT SELECT ON test.user TO 'pig'@'%’

则在使用下面的命令撤销用户对所有数据库表权限时，并不能撤销该用户对 test 数据库中 user 表的 SELECT 权限。

REVOKE SELECT ON *.* FROM  'pig'@'%';

相反，如果授权时指定的是所有数据库表。

GRANT SELECT ON *.* TO 'pig'@'%’;

撤销用户某个库表的权限时，使用下面命令也不能撤销该用户对 test 数据库中 user 表的 SELECT 权限。

REVOKE SELECT ON test.user FROM 'pig'@'%';

具体信息可以用命令SHOW GRANTS FOR 'pig'@'%'; 查看。

5.查看用户权限

在 MySQL 中，有多种方法可以查看用户的权限。

• 使用 SHOW GRANTS 语句。

SHOW GRANTS FOR 'username'@'host';

-- 示例
SHOW GRANTS FOR lvlv@localhost;
SHOW GRANTS FOR lvlv;

不指定主机名称，默认为任意主机"%"。

• 查询 INFORMATION_SCHEMA.SCHEMA_PRIVILEGES 表。

SELECT * FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES;

• 查询 mysql.user 表。

可以从 mysql.user 表中查看所有用户信息，包括用户的权限。

SELECT * FROM mysql.user WHERE user='username';

6.修改用户密码

• SET PASSWORD

使用 SET PASSWORD 语句可以修改指定用户的密码。

SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');

# 示例
SET PASSWORD FOR lvlv@localhost=PASSWORD('123456');

如果是当前登录用户则不需要指定用户名。

SET PASSWORD = PASSWORD("newpassword");

• ALTER USER

MySQL 5.7 版本及以上支持 ALTER USER 语句修改用户密码。

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

• 更新 mysql.user 表中的密码字段。

注意，使用这种方法需要谨慎操作，确保您具有足够的特权，并且了解 MySQL 用户表的结构。

UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE user = 'username' AND host = 'host';

修改密码后，一定要执行 FLUSH PRIVILEGES 命令刷新权限，以使新密码生效。

• 使用服务端工具 mysqladmin。

另外，使用服务端工具 mysqladmin 也可以修改用户密码。

mysqladmin -u 'username' -p 'oldpassword' password 'newpassword'

-- 示例
mysqladmin -u 'lvlv' -p '123456' password "123321"

7.权限类型

MySQL 权限可以分为三种类型：数据库、数据表和数据列的权限。从 mysql.user 表中可查看用户权限信息。

SELECT * from mysql.user WHERE user='username' \G

权限有：

Select_priv: 查看数据表；
Insert_priv: 插入数据表；
Update_priv: 更新数据表；
Delete_priv: 删除数据表记录；
Create_priv: 创建数据库和数据表；
Drop_priv: 删除数据库和数据表；
Reload_priv: 允许使用FLUSH； 
Shutdown_priv: 允许使用mysqladmin shutdown；
Process_priv: 允许使用SHOW FULL PROCESSLIST查看其他用户的进程；
File_priv: 允许使用SELECT… INTO OUTFILE and LOAD DATA INFILE；
Grant_priv: 允许使用grant为用户授权；
References_priv: 未来功能的占位符；现在没有作用；
Index_priv: 确定用户是否可以创建和删除表索引；
Alter_priv: 确定用户是否可以重命名和修改表结构；
Show_db_priv: 确定用户是否可以查看服务器上所有数据库的名字，包括用户拥有足够访问权限的数据库。可以考虑对所有用户禁用这个权限，除非有特别不可抗拒的原因；
Super_priv: 确定用户是否可以执行某些强大的管理功能，例如通过KILL命令删除用户进程，Allows use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL SQL statements. Allows mysqladmin debug command. Allows one extra connection to be made if maximum connections are reached；
Create_tmp_table_priv: 创建临时表；
Lock_tables_priv: 可以使用LOCK TABLES命令阻止对表的访问修改；
Execute_priv: 执行存储过程。此权限只在MySQL5.0及更高版本中有意义。
Repl_slave_priv: 读取用于维护复制数据库环境的二进制日志文件。此用户位于主系统中，有利于主机和客户机之间的通信；
Repl_client_priv: 确定用户是否可以确定复制从服务器和主服务器的位置；
Create_view_priv: 创建视图。此权限只在MySQL5.0及更高版本中有意义；
Show_view_priv: 查看视图或了解视图如何执行。此权限只在MySQL5.0及更高版本中有意义。关于视图的更多信息；
Create_routine_priv: 更改或放弃存储过程和函数。此权限是在MySQL5.0中引入；
Alter_routine_priv: 修改或删除存储函数及函数。此权限是在MySQL5.0中引入的；
Create_user_priv: 执行CREATE USER命令，这个命令用于创建新的MySQL账户；
Event_priv: 确定用户能否创建、修改和删除事件。这个权限是MySQL 5.1.6新增；
Trigger_priv: 创建和删除触发器，这个权限是MySQL 5.1.6新增的；

MySQL 特别权限： 
ALL: 允许做任何事(和root一样)； 
USAGE: 只允许登录，其它什么也不允许做。

---

参考文献

Account Management Statements - MySQL