印刻数据库

外观

OceanBase 用户创建与删除 ​

用户管理核心概念 ​

用户是 OceanBase 数据库中进行权限管理的基本单位，用于控制对数据库资源的访问。OceanBase 支持细粒度的用户权限管理，包括全局权限、数据库权限、表权限等。合理创建和管理用户对于保证数据库的安全性和可靠性至关重要。

用户类型 ​

  • 系统用户：用于管理 OceanBase 集群的用户，如 root 用户
  • 租户管理员：每个租户的管理员用户，如 sys 租户的 root 用户
  • 普通用户：由租户管理员创建的，用于访问和操作数据库对象的用户
  • 应用用户：用于应用程序连接数据库的用户

用户创建方法 ​

1. 使用 CREATE USER 语句创建用户 ​

基本语法 ​

sql
CREATE USER [IF NOT EXISTS] user_name [IDENTIFIED BY 'password'] [options];

示例 ​

sql
-- 创建普通用户
CREATE USER 'test_user'@'%' IDENTIFIED BY 'password123';

-- 创建带有效期的用户
CREATE USER 'temp_user'@'%' IDENTIFIED BY 'password123' WITH MAX_USER_CONNECTIONS 5 PASSWORD EXPIRE INTERVAL 30 DAY;

-- 创建限制连接数的用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'password123' WITH MAX_USER_CONNECTIONS 100;

-- 创建本地用户
CREATE USER 'local_user'@'localhost' IDENTIFIED BY 'password123';

2. 使用 ALTER USER 语句修改用户 ​

基本语法 ​

sql
ALTER USER user_name [IDENTIFIED BY 'password'] [options];

示例 ​

sql
-- 修改用户密码
ALTER USER 'test_user'@'%' IDENTIFIED BY 'new_password123';

-- 修改用户有效期
ALTER USER 'temp_user'@'%' PASSWORD EXPIRE INTERVAL 60 DAY;

-- 修改用户最大连接数
ALTER USER 'app_user'@'%' WITH MAX_USER_CONNECTIONS 200;

-- 锁定用户
ALTER USER 'test_user'@'%' ACCOUNT LOCK;

-- 解锁用户
ALTER USER 'test_user'@'%' ACCOUNT UNLOCK;

3. 使用 DROP USER 语句删除用户 ​

基本语法 ​

sql
DROP USER [IF EXISTS] user_name;

示例 ​

sql
-- 删除单个用户
DROP USER 'test_user'@'%';

-- 删除多个用户
DROP USER 'temp_user'@'%', 'local_user'@'localhost';

-- 删除不存在的用户（使用 IF EXISTS 避免错误）
DROP USER IF EXISTS 'non_existent_user'@'%';

用户创建与删除的最佳实践 ​

1. 用户命名规范 ​

  • 使用有意义的用户名，反映用户的用途或所属部门
  • 避免使用敏感词汇作为用户名
  • 统一用户名格式，便于管理和审计
  • 定期清理不再使用的用户

2. 密码策略 ​

  • 强制使用强密码，包含字母、数字和特殊字符
  • 设置合理的密码过期时间
  • 禁止使用默认密码
  • 定期强制用户修改密码
  • 记录密码修改历史
sql
-- 设置强密码策略
ALTER SYSTEM SET password_policy = 'STRONG';

-- 设置密码过期时间为90天
ALTER SYSTEM SET password_expire_time = 90;

3. 权限管理 ​

  • 遵循最小权限原则，只授予用户必要的权限
  • 使用角色管理权限，便于批量授权和回收
  • 定期审查用户权限，及时回收不再需要的权限
  • 记录权限变更历史
sql
-- 创建角色
CREATE ROLE 'read_only_role';

-- 授予角色权限
GRANT SELECT ON *.* TO 'read_only_role';

-- 将角色授予用户
GRANT 'read_only_role' TO 'test_user'@'%';

4. 连接管理 ​

  • 限制用户的最大连接数，避免资源耗尽
  • 限制用户的连接来源，提高安全性
  • 监控用户的连接状态，及时发现异常连接
sql
-- 创建限制连接数和来源的用户
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'password123' WITH MAX_USER_CONNECTIONS 100;

用户管理常用命令 ​

1. 查看用户信息 ​

查看所有用户 ​

sql
SELECT user, host FROM mysql.user;

查看用户权限 ​

sql
-- 方法1：使用 SHOW GRANTS 语句
SHOW GRANTS FOR 'test_user'@'%';

-- 方法2：使用 SHOW CREATE USER 语句
SHOW CREATE USER 'test_user'@'%';

查看用户连接状态 ​

sql
SHOW PROCESSLIST;

-- 查看特定用户的连接
SHOW PROCESSLIST WHERE USER = 'test_user';

2. 管理用户密码 ​

修改用户密码 ​

sql
-- 方法1：使用 ALTER USER 语句
ALTER USER 'test_user'@'%' IDENTIFIED BY 'new_password123';

-- 方法2：使用 SET PASSWORD 语句
SET PASSWORD FOR 'test_user'@'%' = PASSWORD('new_password123');

重置用户密码 ​

sql
-- 重置 root 用户密码
ALTER USER 'root'@'%' IDENTIFIED BY 'new_root_password';

强制用户修改密码 ​

sql
ALTER USER 'test_user'@'%' PASSWORD EXPIRE;

3. 管理用户锁定状态 ​

锁定用户 ​

sql
ALTER USER 'test_user'@'%' ACCOUNT LOCK;

解锁用户 ​

sql
ALTER USER 'test_user'@'%' ACCOUNT UNLOCK;

查看用户锁定状态 ​

sql
SELECT user, host, account_locked FROM mysql.user WHERE user = 'test_user';

用户删除的注意事项 ​

1. 确认用户不再使用 ​

  • 检查用户是否还有活跃连接
  • 确认用户没有关联的作业或定时任务
  • 确认用户没有拥有任何数据库对象

2. 备份用户权限 ​

  • 在删除用户之前，备份用户的权限信息
  • 以便在需要时恢复用户权限
sql
-- 备份用户权限
SHOW GRANTS FOR 'test_user'@'%' INTO OUTFILE '/tmp/test_user_grants.sql';

3. 回收用户权限 ​

  • 在删除用户之前，先回收用户的所有权限
  • 避免权限残留导致安全问题
sql
-- 回收用户的所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'test_user'@'%';

4. 清理用户相关资源 ​

  • 删除用户创建的数据库对象（如果需要）
  • 清理用户相关的日志和审计记录
  • 清理用户相关的连接和会话

批量用户管理 ​

1. 使用脚本批量创建用户 ​

示例脚本 ​

sql
-- 创建批量用户的存储过程
DELIMITER //
CREATE PROCEDURE create_batch_users()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE user_name VARCHAR(50);
    DECLARE password VARCHAR(50);
    
    WHILE i <= 10 DO
        SET user_name = CONCAT('user_', i);
        SET password = CONCAT('password_', i);
        
        SET @sql = CONCAT('CREATE USER ''', user_name, '''@''%'' IDENTIFIED BY ''', password, '''');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

-- 执行存储过程
CALL create_batch_users();

2. 使用脚本批量删除用户 ​

示例脚本 ​

sql
-- 创建批量删除用户的存储过程
DELIMITER //
CREATE PROCEDURE drop_batch_users()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE user_name VARCHAR(50);
    
    WHILE i <= 10 DO
        SET user_name = CONCAT('user_', i);
        
        SET @sql = CONCAT('DROP USER IF EXISTS ''', user_name, '''@''%''');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

-- 执行存储过程
CALL drop_batch_users();

用户管理的安全最佳实践 ​

1. 定期审查用户 ​

  • 定期审查用户列表，清理不再使用的用户
  • 定期审查用户权限，回收不必要的权限
  • 定期更换用户密码，提高安全性

2. 使用最小权限原则 ​

  • 只授予用户必要的权限
  • 避免使用 ALL PRIVILEGES 权限
  • 使用角色管理权限，便于统一管理

3. 限制用户连接 ​

  • 限制用户的连接来源，只允许可信 IP 访问
  • 限制用户的最大连接数，避免资源耗尽
  • 监控用户的连接状态，及时发现异常连接

4. 启用审计日志 ​

  • 启用用户操作审计，记录用户的所有操作
  • 定期分析审计日志，发现异常行为
  • 保存审计日志，便于追溯和合规检查
sql
-- 启用审计日志
ALTER SYSTEM SET enable_audit_log = True;

5. 使用加密连接 ​

  • 启用 SSL/TLS 加密连接，保护数据传输安全
  • 配置用户必须使用加密连接
sql
-- 创建要求加密连接的用户
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'password123' REQUIRE SSL;

常见问题（FAQ） ​

Q1: 如何创建带有特殊字符的用户密码？ ​

A1: 创建带有特殊字符的用户密码时，需要使用单引号包裹密码：

sql
CREATE USER 'test_user'@'%' IDENTIFIED BY 'P@ssw0rd!23';

Q2: 如何修改当前用户的密码？ ​

A2: 修改当前用户密码的方法：

sql
-- 方法1：使用 ALTER USER 语句
ALTER USER CURRENT_USER() IDENTIFIED BY 'new_password123';

-- 方法2：使用 SET PASSWORD 语句
SET PASSWORD = PASSWORD('new_password123');

Q3: 如何删除用户及其所有对象？ ​

A3: 删除用户及其所有对象的方法：

sql
-- 先删除用户的所有对象
DROP DATABASE IF EXISTS test_db;

-- 然后删除用户
DROP USER 'test_user'@'%';

注意：OceanBase 不支持 CASCADE 选项，需要手动删除用户的所有对象。

Q4: 如何查看用户的有效期？ ​

A4: 查看用户有效期的方法：

sql
SELECT user, host, password_expired, password_lifetime FROM mysql.user WHERE user = 'test_user';

Q5: 如何创建只读用户？ ​

A5: 创建只读用户的方法：

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

-- 授予只读权限
GRANT SELECT ON *.* TO 'read_only_user'@'%';

Q6: 如何限制用户只能访问特定数据库？ ​

A6: 限制用户只能访问特定数据库的方法：

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

-- 授予用户对特定数据库的权限
GRANT ALL PRIVILEGES ON test_db.* TO 'db_user'@'%';

-- 刷新权限
FLUSH PRIVILEGES;