用户权限管理 #

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

-- 指定主机
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'password123';
CREATE USER 'user3'@'192.168.1.%' IDENTIFIED BY 'password123';

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

-- 查看用户
SELECT User, Host FROM mysql.user;

-- 修改密码
ALTER USER 'user1'@'%' IDENTIFIED BY 'newpassword';

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

-- 重命名用户
RENAME USER 'user1'@'%' TO 'user1_new'@'%';

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

-- 删除多个用户
DROP USER 'user2'@'localhost', 'user3'@'192.168.1.%';

TiDB 权限类型
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   全局权限 (ON *.*)                                         │
│   ├── ALL PRIVILEGES: 所有权限                             │
│   ├── CREATE: 创建数据库/表                                │
│   ├── DROP: 删除数据库/表                                  │
│   ├── GRANT OPTION: 授权权限                               │
│   ├── PROCESS: 查看进程                                    │
│   ├── RELOAD: 执行 FLUSH                                   │
│   ├── REPLICATION CLIENT: 复制客户端                       │
│   ├── REPLICATION SLAVE: 复制从库                          │
│   ├── SHOW DATABASES: 查看数据库                           │
│   ├── SHUTDOWN: 关闭服务器                                 │
│   └── SUPER: 超级权限                                      │
│                                                             │
│   数据库权限 (ON db.*)                                      │
│   ├── ALL PRIVILEGES                                       │
│   ├── CREATE, DROP                                         │
│   ├── SELECT, INSERT, UPDATE, DELETE                       │
│   └── INDEX, ALTER, CREATE TEMPORARY TABLES                │
│                                                             │
│   表权限 (ON db.table)                                      │
│   ├── SELECT, INSERT, UPDATE, DELETE                       │
│   ├── CREATE, DROP, ALTER, INDEX                           │
│   └── REFERENCES, TRIGGER                                  │
│                                                             │
└─────────────────────────────────────────────────────────────┘

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

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

-- 授予特定权限
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'user2'@'%';

-- 授予表权限
GRANT SELECT, INSERT ON mydb.users TO 'user3'@'%';

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

-- 授予并允许传递授权
GRANT SELECT ON mydb.* TO 'user5'@'%' WITH GRANT OPTION;

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

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

-- 撤销特定权限
REVOKE SELECT, INSERT ON mydb.* FROM 'user2'@'%';

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

-- 撤销授权权限
REVOKE GRANT OPTION ON mydb.* FROM 'user5'@'%';

-- 查看用户权限
SHOW GRANTS FOR 'user1'@'%';

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

-- 查看所有用户权限
SELECT User, Host, Select_priv, Insert_priv, Update_priv 
FROM mysql.user;

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

-- 查看角色
SELECT User, Host FROM mysql.user WHERE Account_locked = 'Y';

-- 给角色授权
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 'reader'@'%';
GRANT 'app_read', 'app_write' TO 'writer'@'%';
GRANT 'app_admin' TO 'admin'@'%';

-- 设置默认角色
SET DEFAULT ROLE 'app_read' TO 'reader'@'%';
SET DEFAULT ROLE ALL TO 'admin'@'%';

-- 启用角色
SET ROLE 'app_read';
SET ROLE ALL;
SET ROLE NONE;

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

-- 撤销用户的角色
REVOKE 'app_read' FROM 'reader'@'%';

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

-- 使用强密码
CREATE USER 'secure_user'@'%' 
IDENTIFIED BY 'Str0ngP@ssw0rd!2024';

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

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

-- 永不过期
ALTER USER 'user1'@'%' PASSWORD EXPIRE NEVER;

-- 只授予必要的权限
-- 只读用户
GRANT SELECT ON mydb.* TO 'readonly'@'%';

-- 只写用户
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'writeonly'@'%';

-- 避免使用 ALL PRIVILEGES
-- ✗ 不推荐
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%';
-- ✓ 推荐
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'%';

-- 限制连接主机
CREATE USER 'internal'@'10.0.%' IDENTIFIED BY 'password';
CREATE USER 'local'@'localhost' IDENTIFIED BY 'password';

-- 要求 SSL 连接
CREATE USER 'ssl_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;

-- 要求 X509 证书
CREATE USER 'cert_user'@'%' IDENTIFIED BY 'password' REQUIRE X509;

-- 查看当前连接
SHOW PROCESSLIST;

-- 查看连接统计
SELECT 
    USER,
    HOST,
    DB,
    COUNT(*) AS connection_count
FROM INFORMATION_SCHEMA.PROCESSLIST
GROUP BY USER, HOST, DB;

-- 启用审计日志 (需要配置)
-- 在配置文件中设置:
-- [log]
-- enable-audit = true

-- 查看审计日志
-- 日志位置: {deploy_dir}/log/tidb_audit.log