MariaDB用户权限管理 #

MariaDB权限系统
├── 用户账户
│   ├── 用户名
│   └── 主机名
├── 权限类型
│   ├── 全局权限
│   ├── 数据库权限
│   ├── 表权限
│   └── 列权限
└── 角色系统
    └── 权限集合

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

-- 创建用户并设置密码
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'password123';

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

-- 创建特定IP访问的用户
CREATE USER 'user4'@'192.168.1.%' IDENTIFIED BY 'password123';

-- 使用密码插件
CREATE USER 'user5'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('password123');

-- 主机名模式说明
'user'@'localhost'      -- 只能本地连接
'user'@'127.0.0.1'      -- 只能通过127.0.0.1连接
'user'@'%'              -- 任何主机
'user'@'192.168.1.%'    -- 192.168.1网段
'user'@'%.example.com'  -- example.com域名

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

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

-- 修改自己的密码
SET PASSWORD = PASSWORD('new_password');

-- 修改其他用户密码
SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('new_password');

-- MariaDB 10.4+ 语法
ALTER USER 'user1'@'localhost' IDENTIFIED BY 'new_password';

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

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

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

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

-- 查看用户状态
SELECT user, host, account_locked FROM mysql.user;

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

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

-- 如果存在则删除
DROP USER IF EXISTS 'user1'@'localhost';

GRANT privilege_type ON object TO user;

-- 授予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- 授予特定全局权限
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'user'@'localhost';

-- 常用全局权限
-- ALL PRIVILEGES: 所有权限
-- CREATE USER: 创建用户
-- RELOAD: 重新加载权限
-- SHUTDOWN: 关闭服务器
-- SUPER: 超级权限
-- PROCESS: 查看进程
-- REPLICATION CLIENT: 复制客户端
-- REPLICATION SLAVE: 复制从库

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

-- 授予数据库特定权限
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'user'@'localhost';

-- 授予多个数据库权限
GRANT ALL PRIVILEGES ON mydb1.* TO 'user'@'localhost';
GRANT ALL PRIVILEGES ON mydb2.* TO 'user'@'localhost';

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

-- 授予所有表操作权限
GRANT ALL PRIVILEGES ON mydb.users TO 'user'@'localhost';

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

-- 授予执行权限
GRANT EXECUTE ON PROCEDURE mydb.my_procedure TO 'user'@'localhost';

-- 授予函数执行权限
GRANT EXECUTE ON FUNCTION mydb.my_function TO 'user'@'localhost';

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

REVOKE privilege_type ON object FROM user;

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

-- 撤销数据库权限
REVOKE INSERT, UPDATE ON mydb.* FROM 'user'@'localhost';

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

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

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

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

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

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

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

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

-- MariaDB 10.0+ 支持角色

-- 创建角色
CREATE ROLE 'read_only';
CREATE ROLE 'read_write';
CREATE ROLE 'admin_role';

-- 创建角色时指定权限
CREATE ROLE 'app_role';
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'app_role';

-- 给角色授权
GRANT SELECT ON mydb.* TO 'read_only';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'read_write';
GRANT ALL PRIVILEGES ON mydb.* TO 'admin_role';

-- 给用户分配角色
GRANT 'read_only' TO 'user1'@'localhost';
GRANT 'read_write' TO 'user2'@'localhost';
GRANT 'admin_role' TO 'admin'@'localhost';

-- 分配多个角色
GRANT 'read_only', 'read_write' TO 'user3'@'localhost';

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

-- 激活角色
SET ROLE 'read_only';
SET ROLE 'read_write';

-- 激活所有角色
SET ROLE ALL;

-- 取消所有角色
SET ROLE NONE;

-- 设置默认角色
SET DEFAULT ROLE ALL TO 'user1'@'localhost';

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

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

-- 删除角色
DROP ROLE 'read_only';
DROP ROLE IF EXISTS 'read_only';

-- 刷新权限（重新加载权限表）
FLUSH PRIVILEGES;

-- 注意：直接修改权限表后需要刷新
-- 使用GRANT/REVOKE会自动刷新

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

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

-- 密码过期
ALTER USER 'user'@'localhost' PASSWORD EXPIRE;

-- 设置密码过期时间
ALTER USER 'user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 只授予必要的权限
-- 应用用户：SELECT, INSERT, UPDATE, DELETE
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'localhost';

-- 只读用户：SELECT
GRANT SELECT ON mydb.* TO 'readonly'@'localhost';

-- 管理用户：特定数据库的所有权限
GRANT ALL PRIVILEGES ON mydb.* TO 'db_admin'@'localhost';

-- 限制主机访问
CREATE USER 'user'@'192.168.1.100' IDENTIFIED BY 'password';

-- 限制连接数
CREATE USER 'user'@'localhost' 
IDENTIFIED BY 'password' 
WITH MAX_QUERIES_PER_HOUR 100 
MAX_UPDATES_PER_HOUR 50 
MAX_CONNECTIONS_PER_HOUR 10 
MAX_USER_CONNECTIONS 5;

-- 启用审计插件
INSTALL SONAME 'server_audit';

-- 配置审计
SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE';
SET GLOBAL server_audit_logging = 'ON';

-- 查看审计配置
SHOW VARIABLES LIKE 'server_audit%';

-- 创建应用专用用户
CREATE USER 'myapp'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'myapp'@'%';
FLUSH PRIVILEGES;

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

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

-- 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;