-- 基础授权

GRANT privilege_type ON database.table TO 'username'@'host';

-- 全局权限（所有数据库）

GRANT ALL PRIVILEGES ON . TO 'admin_user'@'localhost' WITH GRANT OPTION;

-- 数据库级别权限

GRANT ALL PRIVILEGES ON my_database.* TO 'app_user'@'localhost';

-- 表级别权限

GRANT SELECT, INSERT, UPDATE ON my_database.users TO 'web_user'@'%';

-- 列级别权限

GRANT SELECT (id, name, email), UPDATE (name, email) ON my_database.users TO 'api_user'@'localhost';

-- 存储过程和函数权限

GRANT EXECUTE ON PROCEDURE my_database.calculate_bonus TO 'hr_user'@'localhost';

-- 数据操作权限

GRANT SELECT, INSERT, UPDATE, DELETE ON database.* TO 'username'@'host';

-- 结构操作权限

GRANT CREATE, ALTER, DROP, INDEX ON database.* TO 'username'@'host';

-- 管理权限

GRANT RELOAD, PROCESS, SUPER, REPLICATION SLAVE ON . TO 'user'@'host';

-- 文件操作权限

GRANT FILE ON . TO 'user'@'host';

-- 撤销所有权限

REVOKE ALL PRIVILEGES ON . FROM 'username'@'host';

-- 撤销特定权限

REVOKE INSERT, UPDATE ON database.table FROM 'username'@'host';

-- 撤销GRANT OPTION权限

REVOKE GRANT OPTION ON . FROM 'username'@'host';

-- 查看用户权限

SHOW GRANTS FOR 'username'@'%';

-- 查看当前用户权限

SHOW GRANTS;

-- 通过系统表查看权限

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

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

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

-- 用户角色管理（MySQL 8.0+）

-- 创建角色

CREATE ROLE 'read_only', 'read_write', 'admin';

-- 删除角色

drop ROLE 'read_only', 'read_write', 'admin';

-- 为角色授予权限

GRANT SELECT ON . TO 'read_only';

GRANT SELECT, INSERT, UPDATE, DELETE ON my_app.* TO 'read_write';

GRANT ALL PRIVILEGES ON my_app.* TO 'app_admin';

-- 将角色授予用户

GRANT 'read_only' TO 'report_user'@'localhost';

GRANT 'read_write' TO 'app_user'@'localhost';

-- 激活角色

SET DEFAULT ROLE ALL TO 'report_user'@'localhost';

-- 查看角色

SELECT * FROM mysql.roles_mapping;

-- 查看当前连接的用户

SHOW PROCESSLIST;

-- 查看用户连接统计

SELECT user, host, count(*) as connection_count

FROM information_schema.processlist

WHERE user IS NOT NULL

GROUP BY user, host

ORDER BY connection_count DESC;

-- 启用连接审计

SET GLOBAL log_raw = 1;

SET GLOBAL general_log = 1;