MySQL用户权限管理 #

┌─────────────────────────────────────┐
│         MySQL权限系统               │
├─────────────────────────────────────┤
│  用户账户                           │
│    ├── 用户名                       │
│    └── 主机名                       │
│  权限级别                           │
│    ├── 全局级别                     │
│    ├── 数据库级别                   │
│    ├── 表级别                       │
│    └── 列级别                       │
└─────────────────────────────────────┘

-- 创建用户
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123';

-- 创建用户并指定认证插件
CREATE USER 'user2'@'localhost' 
IDENTIFIED WITH mysql_native_password BY 'password123';

-- 创建允许远程访问的用户
CREATE USER 'user3'@'%' IDENTIFIED BY 'password123';

-- 创建用户并授予权限
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin123';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- 查看所有用户
SELECT user, host FROM mysql.user;

-- 查看当前用户
SELECT USER(), CURRENT_USER();

-- 查看用户详细信息
SELECT 
    user,
    host,
    authentication_string,
    password_expired,
    account_locked
FROM mysql.user
WHERE user = 'user1';

-- 修改当前用户密码
ALTER USER USER() IDENTIFIED BY 'new_password';

-- 修改指定用户密码
ALTER USER 'user1'@'localhost' IDENTIFIED BY 'new_password';

-- 使用SET PASSWORD
SET PASSWORD FOR 'user1'@'localhost' = 'new_password';

-- MySQL 5.7
SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('new_password');

-- 重命名用户
RENAME USER 'user1'@'localhost' TO 'newuser'@'localhost';

-- 同时修改主机
RENAME USER 'user1'@'localhost' TO 'user1'@'%';

-- 删除用户
DROP USER 'user1'@'localhost';

-- 删除多个用户
DROP USER 'user1'@'localhost', 'user2'@'localhost';

-- 锁定用户
ALTER USER 'user1'@'localhost' ACCOUNT LOCK;

-- 解锁用户
ALTER USER 'user1'@'localhost' ACCOUNT UNLOCK;

-- 查看锁定状态
SELECT user, host, account_locked FROM mysql.user;

-- 授予全局权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';

-- 授予数据库权限
GRANT ALL PRIVILEGES ON mydb.* TO 'user1'@'localhost';

-- 授予表权限
GRANT SELECT, INSERT, UPDATE ON mydb.users TO 'user1'@'localhost';

-- 授予列权限
GRANT SELECT(id, name), UPDATE(name) ON mydb.users TO 'user1'@'localhost';

-- 授予存储过程权限
GRANT EXECUTE ON PROCEDURE mydb.my_procedure TO 'user1'@'localhost';

-- 授予代理权限
GRANT PROXY ON 'admin'@'localhost' TO 'user1'@'localhost';

-- 授予GRANT OPTION（允许用户授权给其他用户）
GRANT SELECT ON mydb.* TO 'user1'@'localhost' WITH GRANT OPTION;

-- 查看当前用户权限
SHOW GRANTS;

-- 查看指定用户权限
SHOW GRANTS FOR 'user1'@'localhost';

-- 查看用户全局权限
SELECT * FROM mysql.user WHERE user = 'user1';

-- 查看用户数据库权限
SELECT * FROM mysql.db WHERE user = 'user1';

-- 查看用户表权限
SELECT * FROM mysql.tables_priv WHERE user = 'user1';

-- 撤销全局权限
REVOKE ALL PRIVILEGES ON *.* FROM 'admin'@'localhost';

-- 撤销数据库权限
REVOKE ALL PRIVILEGES ON mydb.* FROM 'user1'@'localhost';

-- 撤销表权限
REVOKE SELECT, INSERT ON mydb.users FROM 'user1'@'localhost';

-- 撤销GRANT OPTION
REVOKE GRANT OPTION ON mydb.* FROM 'user1'@'localhost';

-- 撤销所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user1'@'localhost';

-- 刷新权限（使权限立即生效）
FLUSH PRIVILEGES;

-- 全局权限适用于所有数据库
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';

-- 常用全局权限
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'user'@'localhost';
GRANT CREATE, DROP, ALTER ON *.* TO 'user'@'localhost';
GRANT RELOAD, SHUTDOWN ON *.* TO 'admin'@'localhost';

-- 数据库权限适用于指定数据库的所有对象
GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'localhost';

-- 常用数据库权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'user'@'localhost';
GRANT CREATE, DROP, ALTER ON mydb.* TO 'user'@'localhost';

-- 表权限适用于指定表
GRANT SELECT, INSERT, UPDATE ON mydb.users TO 'user'@'localhost';

-- 常用表权限
GRANT SELECT ON mydb.users TO 'readonly'@'localhost';
GRANT SELECT, INSERT ON mydb.logs TO 'app'@'localhost';

-- 列权限适用于指定列
GRANT SELECT(id, name), UPDATE(name) ON mydb.users TO 'user'@'localhost';

-- 创建角色
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- 查看角色
SELECT user, host FROM mysql.user WHERE user LIKE 'app_%';

-- 授予角色权限
GRANT SELECT ON mydb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';
GRANT ALL PRIVILEGES ON mydb.* TO 'app_admin';

-- 将角色授予用户
GRANT 'app_read' TO 'user1'@'localhost';
GRANT 'app_read', 'app_write' TO 'user2'@'localhost';
GRANT 'app_admin' TO 'admin'@'localhost';

-- 激活角色
SET ROLE 'app_read';

-- 激活所有授予的角色
SET ROLE ALL;

-- 查看当前激活的角色
SELECT CURRENT_ROLE();

-- 设置默认角色
ALTER USER 'user1'@'localhost' DEFAULT ROLE 'app_read';

-- 撤销用户的角色
REVOKE 'app_read' FROM 'user1'@'localhost';

-- 撤销角色的权限
REVOKE SELECT ON mydb.* FROM 'app_read';

-- 删除角色
DROP ROLE 'app_read';

-- 创建只读用户
CREATE USER 'readonly'@'%' IDENTIFIED BY 'password123';
GRANT SELECT ON mydb.* TO 'readonly'@'%';
FLUSH PRIVILEGES;

-- 创建应用用户（增删改查权限）
CREATE USER 'app'@'%' IDENTIFIED BY 'password123';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app'@'%';
FLUSH PRIVILEGES;

-- 创建管理员用户
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin123';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- 创建备份用户
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'backup123';
GRANT SELECT, LOCK TABLES, SHOW VIEW, RELOAD ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

-- 查看密码策略
SHOW VARIABLES LIKE 'validate_password%';

-- 设置密码策略
SET GLOBAL validate_password_length = 8;
SET GLOBAL validate_password_policy = MEDIUM;

-- 密码过期
ALTER USER 'user1'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 立即过期
ALTER USER 'user1'@'localhost' PASSWORD EXPIRE;

-- 限制用户只能从特定IP访问
CREATE USER 'user'@'192.168.1.100' IDENTIFIED BY 'password';

-- 限制用户只能从特定网段访问
CREATE USER 'user'@'192.168.1.%' IDENTIFIED BY 'password';

-- 限制最大连接数
ALTER USER 'user'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 100;

-- 开启审计日志
SET GLOBAL audit_log = ON;

-- 查看审计日志
SHOW VARIABLES LIKE 'audit_log%';