下面从三个维度（数据库类型、操作类型、权限层级）分类介绍SQL用户权限设置及权限管理的基础语法，覆盖SQL权限管理核心知识点，仅供参考。

一、按数据库类型分类

1. MySQL权限体系

/* 用户创建 */
CREATE USER 'inventory_mgr'@'192.168.1.%'
IDENTIFIED BY 'P@ssw0rd!'
WITH MAX_QUERIES_PER_HOUR 100;  -- 限制每小时查询次数

/* 权限授予 */
GRANT SELECT, INSERT, UPDATE 
ON warehouse.* 
TO 'inventory_mgr'@'192.168.1.%'
WITH GRANT OPTION;  -- 允许转授权

/* 权限回收 */
REVOKE INSERT ON warehouse.sensitive_table 
FROM 'inventory_mgr'@'192.168.1.%';

/* 权限查看 */
SHOW GRANTS FOR 'inventory_mgr'@'192.168.1.%';

2. SQL Server权限体系

/* 登录名创建 */
CREATE LOGIN sales_admin 
WITH PASSWORD = 'S@les2023', 
CHECK_POLICY = ON;  -- 启用密码策略

/* 数据库用户映射 */
USE SalesDB;
CREATE USER sales_admin FOR LOGIN sales_admin;

/* 架构级授权 */
GRANT SELECT, INSERT 
ON SCHEMA::dbo TO sales_admin;

/* 存储过程授权 */
GRANT EXECUTE ON OBJECT::usp_GetSalesReport 
TO sales_admin;

/* 权限查询 */
SELECT permission_name, state_desc 
FROM sys.database_permissions 
WHERE grantee_principal_id = USER_ID('sales_admin');

3. PostgreSQL权限体系

/* 角色创建 */
CREATE ROLE analytics_team 
WITH LOGIN PASSWORD 'Data123'
CONNECTION LIMIT 20;  -- 限制并发连接数

/* 数据库所有权 */
CREATE DATABASE sales_data OWNER analytics_team;

/* 表空间权限 */
GRANT ALL ON TABLESPACE fast_ssd TO analytics_team;

/* 行级安全策略 */
CREATE POLICY user_data_policy ON customer_data
FOR SELECT USING (tenant_id = current_setting('app.tenant'));
ALTER TABLE customer_data ENABLE ROW LEVEL SECURITY;

/* 权限查询 */
du+ analytics_team  -- 命令行查看
SELECT * FROM pg_roles WHERE rolname = 'analytics_team';

二、按操作类型分类

1. 权限授予（GRANT）

/* 全局权限 */
GRANT SUPER, REPLICATION CLIENT ON *.* 
TO 'dba_admin'@'localhost';  -- MySQL

/* 数据库级 */
GRANT CREATE, TEMPORARY ON DATABASE ecom_db 
TO analytics_team;  -- PostgreSQL

/* 表级 */
GRANT SELECT, REFERENCES ON Products 
TO sales_role;  -- SQL Server

/* 列级 */
GRANT UPDATE (price, discount) ON Products 
TO 'pricing_mgr'@'%';  -- MySQL

/* 过程级 */
GRANT EXECUTE ON PROCEDURE CalculateTax 
TO finance_role;

2. 权限回收（REVOKE）

/* 级联回收 */
REVOKE ALL PRIVILEGES, GRANT OPTION 
FROM 'deprecated_user'@'%';  -- MySQL

/* 特定权限回收 */
REVOKE DELETE ON Orders FROM sales_staff;

/* 架构级回收 */
REVOKE INSERT ON SCHEMA::dbo FROM junior_dev;  -- SQL Server

/* 角色权限回收 */
REVOKE finance_role FROM 'temp_user';  -- PostgreSQL

3. 权限验证

/* MySQL权限检查 */
SELECT * FROM information_schema.table_privileges 
WHERE grantee = "'user'@'host'";

/* SQL Server权限测试 */
EXECUTE AS USER = 'test_user';
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');
REVERT;

/* PostgreSQL权限模拟 */
SET ROLE analytics_team;
SELECT has_table_privilege('sales_data', 'SELECT');
RESET ROLE;

三、按权限层级分类

1. 全局层级

/* MySQL服务器级 */
GRANT PROCESS, FILE ON *.* TO 'sysadmin'@'localhost';

/* SQL Server实例级 */
GRANT ALTER ANY LOGIN TO security_admin;

/* PostgreSQL集群级 */
ALTER ROLE admin CREATEROLE CREATEDB;

2. 数据库层级

/* MySQL */
GRANT ALL ON ecom_db.* TO 'db_admin'@'%';

/* SQL Server */
USE ecom_db;
GRANT CONTROL DATABASE TO db_owner;

/* PostgreSQL */
GRANT CONNECT, TEMPORARY ON DATABASE ecom_db TO app_user;

3. 表/对象层级

/* 基础表权限 */
GRANT SELECT, INSERT ON Orders TO order_clerk;

/* 视图权限控制 */
CREATE VIEW customer_limited AS 
SELECT id, name FROM customers WHERE region='EU';
GRANT SELECT ON customer_limited TO eu_sales;

/* 序列权限 */
GRANT USAGE, SELECT ON SEQUENCE order_id_seq TO order_processor;

4. 列层级

/* MySQL列级控制 */
GRANT SELECT (id, name), UPDATE (email) 
ON users TO support_team;

/* SQL Server列权限 */
GRANT SELECT (product_id, product_name) 
ON Products TO reporting_user;

/* PostgreSQL列级 */
GRANT UPDATE (price) ON products TO pricing_mgr;

5. 行层级

/* MySQL（通过视图） */
CREATE VIEW my_orders AS 
SELECT * FROM orders WHERE user_id = CURRENT_USER();
GRANT SELECT ON my_orders TO customer_user;

/* PostgreSQL行安全策略 */
CREATE POLICY user_orders_policy ON orders
FOR ALL TO PUBLIC
USING (user_id = current_user);

/* SQL Server（通过函数） */
CREATE FUNCTION fn_securitypredicate(@user_id int)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @user_id = USER_ID();

四、电商场景应用示例

1. 用户角色分类

角色	权限对象	权限类型	限制条件
商品管理员	products	SELECT, UPDATE(stock)	IP段限制
订单处理员	orders	SELECT, INSERT	列级权限
客户服务	customers	SELECT(name,email)	视图过滤
财务审计	.payment	SELECT	时间限制

2. 权限实施代码

/* MySQL实现 */
CREATE ROLE product_admin;
GRANT SELECT, UPDATE(stock) ON ecom.products TO product_admin;
GRANT product_admin TO 'prod_mgr'@'10.0.%.%';

/* SQL Server实现 */
CREATE ROLE order_processor;
GRANT SELECT, INSERT ON dbo.Orders TO order_processor;
GRANT SELECT ON dbo.Products TO order_processor;
ALTER ROLE order_processor ADD MEMBER 'order_user';

/* PostgreSQL实现 */
CREATE ROLE cs_agent;
GRANT SELECT ON customer_info_view TO cs_agent;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO cs_agent;

3. 权限验证测试

-- 商品管理员测试
UPDATE products SET stock = stock - 1 WHERE id=100; -- 成功
DELETE FROM products WHERE id=100; -- 失败(1142)

-- 订单处理员测试
INSERT INTO orders(user_id, product_id) VALUES (501, 200); -- 成功
UPDATE orders SET status='shipped' WHERE id=1001; -- 失败(1142)

-- 权限视图输出
/* MySQL */
SHOW GRANTS FOR 'prod_mgr'@'10.0.5.25';
/*
GRANT USAGE ON *.* TO `prod_mgr`@`10.0.5.25`
GRANT SELECT, UPDATE (`stock`) ON `ecom`.`products` TO `prod_mgr`@`10.0.5.25`
*/

五、性能优化与避坑指南

1. 权限缓存优化

# MySQL配置
[mysqld]
privilege_cache_size=256M
table_open_cache=4096

# PostgreSQL配置
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

2. 权限设计避坑

/* 错误示例：过度授权 */
GRANT ALL ON *.* TO 'webapp'@'%'; -- 高危！

/* 正确方案：最小权限 */
CREATE ROLE webapp_rw;
GRANT SELECT, INSERT, UPDATE ON app_db.* TO webapp_rw;
GRANT EXECUTE ON PROCEDURE api_* TO webapp_rw;

/* 权限泄漏检查 */
-- MySQL
SELECT * FROM mysql.user WHERE Grant_priv='Y';

-- SQL Server
SELECT name FROM sys.server_principals 
WHERE is_disabled=0 AND HAS_PERMS_BY_NAME(NULL, NULL, 'CONTROL SERVER')=1;

3. 审计与监控

/* MySQL审计 */
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_policy = ALL;

/* PostgreSQL审计 */
CREATE EXTENSION pgaudit;
SET pgaudit.log = 'write, ddl';

/* SQL Server审计 */
CREATE SERVER AUDIT Ecom_Audit
TO FILE (FILEPATH = 'D:Audits');
ALTER SERVER AUDIT Ecom_Audit WITH (STATE = ON);

六、典型面试题精解

Q1：GRANT和REVOKE的原子性如何保证？

答：

MySQL：权限操作自动提交，系统表更新+缓存刷新

SQL Server：在显式事务中执行权限操作

BEGIN TRANSACTION;
GRANT SELECT ON Orders TO userA;
REVOKE DELETE FROM userB;
COMMIT; -- 原子提交

PostgreSQL：每个GRANT/REVOKE是独立事务

Q2：如何实现跨数据库权限同步？

方案：

/* MySQL主从架构 */
CREATE USER 'sync_user'@'replica_host' IDENTIFIED BY 'sync_pass';
GRANT REPLICATION CLIENT, RELOAD ON *.* TO 'sync_user';

/* SQL Server Always On */
ALTER AVAILABILITY GROUP [AG1]
ADD LISTENER 'ag_listener' (PORT=1433);
GRANT CONNECT ON ENDPOINT::hadr_endpoint TO sync_user;

Q3：权限过多导致连接缓慢如何优化？

调优策略：

角色权限合并：减少单个用户权限条目

权限缓存扩容：SET GLOBAL privilege_cache_size=1G

DNS反向解析禁用：skip_name_resolve=ON

定期清理无效权限：

DELETE FROM mysql.db
WHERE User NOT IN (SELECT User FROM mysql.user);