权限语句

SQL 权限管理：授权与收回语句详解

在数据库管理中，权限控制是保障数据安全的核心环节。SQL 提供了 GRANT（授权）和 REVOKE（收回权限）语句，用于精细化管理用户对数据库对象（表、视图、函数等）的操作权限。本文详细解析这两类语句的语法、参数及使用场景。

授权语句（GRANT）

GRANT 语句用于向用户或角色授予特定的数据库操作权限，语法结构如下：

GRANT <权限1>[, <权限2>, ...]
ON <对象类型> <对象名>
TO <用户1>[, <用户2>, ...]
[WITH GRANT OPTION];

核心参数说明

（1）<权限>：授予的具体操作权限

常见权限包括：

• 数据操作权限：SELECT（查询）、INSERT（插入）、UPDATE（更新）、DELETE（删除）。
• 结构操作权限：CREATE（创建对象）、ALTER（修改结构）、DROP（删除对象）、INDEX（创建索引）。
• 管理权限：ALL PRIVILEGES（所有权限）、GRANT OPTION（授权权限，见下文）。
• 特殊权限：REFERENCES（引用外键）、EXECUTE（执行存储过程 / 函数）等。

（2）<对象类型> <对象名>：权限作用的对象

• 对象类型：TABLE（表）、VIEW（视图）、DATABASE（数据库）、FUNCTION（函数）等。
• 对象名：具体对象的名称，可使用通配符 * 表示所有对象（如 TABLE * 表示所有表）。

示例：

-- 授予对表 `employees` 的查询权限
GRANT SELECT ON TABLE employees TO 'user1';

（3）<用户>：被授权的用户或角色

• 可以是具体用户名（如 'user1'@'localhost'），也可以是角色（如 'admin_role'）。
• 关键字 PUBLIC 表示所有用户（谨慎使用）。

（4）WITH GRANT OPTION：权限传递开关

• 若指定此选项，被授权用户可将获得的权限再授予其他用户。
• 若不指定，则被授权用户仅能自己使用权限，无法传递。

示例：

-- 允许 user1 将 employees 表的查询权限授予他人
GRANT SELECT ON TABLE employees TO 'user1' WITH GRANT OPTION;

常用授权示例

-- 1. 授予用户对表的所有数据操作权限
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE customers TO 'user2';

-- 2. 授予角色对数据库的所有权限
GRANT ALL PRIVILEGES ON DATABASE sales_db TO 'sales_role';

-- 3. 授予执行函数的权限
GRANT EXECUTE ON FUNCTION calculate_total TO 'user3';

-- 4. 授予所有用户查询表的权限（不推荐，安全性低）
GRANT SELECT ON TABLE public_data TO PUBLIC;

收回权限语句（REVOKE）

REVOKE 语句用于收回已授予的权限，语法结构如下：

REVOKE <权限1>[, <权限2>, ...]
ON <对象类型> <对象名>
FROM <用户1>[, <用户2>, ...]
[RESTRICT | CASCADE];

核心参数说明

（1）<权限>、<对象类型> <对象名>、<用户>：与 GRANT 对应

需与授权时的参数一致，确保精准收回指定权限。

（2）RESTRICT 与 CASCADE：级联收回选项

• RESTRICT：仅收回用户自身的权限，若该用户已将权限授予他人，则收回失败（默认行为）。
• CASCADE：级联收回所有相关权限 —— 不仅收回用户自身的权限，还会收回该用户通过 WITH GRANT OPTION 授予他人的权限。

示例：

-- 级联收回 user1 及其授予的 employees 表查询权限
REVOKE SELECT ON TABLE employees FROM 'user1' CASCADE;

常用收回权限示例

-- 1. 收回用户对表的插入和删除权限
REVOKE INSERT, DELETE ON TABLE customers FROM 'user2';

-- 2. 收回角色的所有数据库权限
REVOKE ALL PRIVILEGES ON DATABASE sales_db FROM 'sales_role';

-- 3. 级联收回用户及被其授权的权限
REVOKE EXECUTE ON FUNCTION calculate_total FROM 'user3' CASCADE;

-- 4. 收回所有用户对表的查询权限
REVOKE SELECT ON TABLE public_data FROM PUBLIC;

权限管理最佳实践

1. 最小权限原则：仅授予用户完成工作必需的权限（如只读用户只给 SELECT），避免过度授权。
2. 使用角色管理：将相同权限的用户归类为角色（如 admin、viewer），简化授权操作。
3. 谨慎使用 WITH GRANT OPTION：仅对信任的用户开放权限传递，避免权限扩散失控。
4. 定期审计权限：通过 SHOW GRANTS 语句检查用户权限（如 SHOW GRANTS FOR 'user1'），及时清理冗余权限。
5. 优先使用 CASCADE 收回：当需要彻底移除权限时，使用 CASCADE 确保所有相关权限被收回