MySQL学习笔记（三）：用户权限

1.用户权限

1.1 用户权限级别

MySQL中的用户权限级别分为：

（1）全局权限：拥有对所有数据库和用户进行操作的权限。用户的全局（所有）权限信息存储在“mysql.user”数据表中。

（2）数据库权限：可以操作数据库。用户的数据库权限信息存储在“mysql.db”数据表中。

（3）数据表权限：可以操作数据表。用户的数据表权限信息存储在“mysql.table_priv”数据表中。

（4）字段权限：可以操作字段。用户的字段权限信息存储在“mysql.columns_priv”数据表中。

（5）存储过程权限：可以操作存储过程。用户的存储过程权限信息存储在“mysql.proc_priv”数据表中。

1.2 各级别权限的详细信息

2.授予用户权限

2.1 授予全局权限

GRANT [ALL|ALL PRIVILEGES] ON *.* TO '用户名'@{'主机名'|'IP地址'};

（1）“[...|...]”：可选其一，也可都不选。

（2）“{...|...}”：必选其一。

（3）“*.*”：表示所有数据库和所有数据表。

2.2 授予数据库、表权限

GRANT 权限名 ON 数据库名[.数据表名|.视图] TO '用户名'@{'主机名'|'IP地址'}
    [WITH GRANT OPTION];  # 使用效果：被授权的用户有权限把自身所有权限授权给别的用户

2.3 授予字段权限

GRANT 权限名(字段名1, 字段名2, ...) ON 数据库名.数据表名 TO '用户名'@{'主机名'|'IP地址'}
    [WITH GRANT OPTION];

2.4 授予存储过程权限

GRANT 权限名 ON PROCEDURE 数据库名.存储过程名 TO '用户名'@{'主机名'|'IP地址'}
    [WITH GRANT OPTION];

3.查询用户权限

3.1 查询当前用户权限

SHOW GRANTS;

3.2 查询其他用户权限

SHOW GRANT FOR '用户名'@{'主机名'|'IP地址'};

3.3 查询用户权限列表

DESC 权限列表;           # 查询权限列表的结构, 包括对应级别的所有权限
SELECT * FROM 权限列表;  # 查询所有用户对应级别的权限信息
# 权限列表：mysql.user、mysql.db、mysql.table_priv、mysql.columns_priv、mysql.proc_priv

4.撤销用户权限

REVOKE 权限名[(字段名1, 字段名2, ...)] ON 数据库名.数据表名
    FROM '用户名'@{'主机名'|'IP地址'};

5 例子

5.1 创建MySQL对象

# 创建MySQL中的对象(用户, 数据库, 数据表和存储过程)
CREATE USER 'test_user'@'localhost' IDENTIFIED BY '123456';  # 创建一个用户

CREATE DATABASE test_database;  # 创建一个数据库

USE test_database;              # 连接创建的数据库

CREATE TABLE test_table         # 在test_database数据库中创建一个数据表
(
    id VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255),
    age VARCHAR(255)
);

DELIMITER \\
CREATE PROCEDURE test_proc()    # 在test_database数据库中创建一个存储过程
BEGIN
    DECLARE count INT;
    SELECT COUNT(*) INTO count FROM test_table;
END \\
DELIMITER ;

5.2 查询用户权限

5.2.1 查询当前用户权限

SHOW GRANTS;

上图是VS code中MySQL插件（VS code模组仓库中搜索MySQL shell for VS code）返回的结果，如果用MySQL自带的编辑器可以使用如下命令，可以让返回的结果更可读，下述命令也都是如此。

SHOW GRANTS\G

5.2.2 查询新创建的用户权限

SHOW GRANTS FOR 'test_user'@'localhost';

5.2.3 查询权限列表

SELECT * FROM mysql.user;

从上图可以看出，新创建的用户“test_user”的权限基本都为“N”。

5.3 授予用户权限

5.3.1 授予存储过程权限

（1）查看存储过程权限列表

SELECT * FROM mysql.procs_priv;  # 查询存储过程权限

（2）授予用户存储过程权限

# 授予用户"test_user"执行存储过程"test_database.test_proc"的权限
GRANT EXECUTE ON PROCEDURE test_database.test_proc TO 'test_user'@'localhost';

 （3）再次查看存储过程权限列表

SELECT * FROM mysql.procs_priv;  # 再次查询存储过程权限

5.3.2 授予字段权限

（1）查看字段权限列表

SELECT * FROM mysql.columns_priv;  # 查询字段权限

（2）授予用户字段权限

# 字段权限：授予用户"test_user"修改字段"age"的权限
GRANT UPDATE(age) ON test_database.test_table TO 'test_user'@'localhost';

 （3）再次查看字段权限列表

SELECT * FROM mysql.columns_priv;  # 再次查询字段权限

5.3.3 授予数据库、表权限

（1）查看数据库、表权限列表

SELECT * FROM mysql.tables_priv;  # 查询数据表权限
SELECT * FROM mysql.db;           # 查询数据库权限

（2）授予用户数据库、表权限

# 授予用户"test_user"查询"test_database.test_table"数据表的权限
GRANT SELECT ON test_database.test_table TO 'test_user'@'localhost';

# 授予用户"test_user"执行所有数据库程序的权限
GRANT EXECUTE ON *.* TO 'test_user'@'localhost';

（3）再次查看数据库、表权限列表

SELECT * FROM mysql.tables_priv;  # 再次查询数据表权限
SELECT * FROM mysql.db;           # 再次查询数据库权限

5.3.4 授予用户全局权限

（1）查看全局权限列表

SELECT * FROM mysql.user;  # 查询全局权限

（2）授予用户全局权限

# 授予用户"test_user"所有全局权限
GRANT ALL ON *.* TO 'test_user'@'localhost';

（3）再次查看全局权限列表

SELECT * FROM mysql.user;  # 再次查询全局权限