Oracle 用户生命周期管理

用户创建

预创建准备

• 用户需求分析：确定用户类型、权限需求、表空间分配
• 命名规范：制定统一的用户命名规范
• 权限模板：根据用户角色创建权限模板
• 表空间规划：为用户分配适当的表空间和配额

用户创建步骤

创建用户

-- 创建基本用户
CREATE USER app_user IDENTIFIED BY "ComplexPassword123!"
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA 100M ON users
  PROFILE default;

-- 创建具有特定权限的用户
CREATE USER readonly_user IDENTIFIED BY "ReadonlyPass456!"
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA 0 ON users
  PROFILE readonly_profile;

分配初始权限

-- 授予基本连接权限
GRANT CREATE SESSION TO app_user;

-- 授予对象权限
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO app_user;

-- 授予系统权限
GRANT CREATE TABLE, CREATE VIEW TO app_user;

-- 授予角色
GRANT resource, connect TO app_user;

用户创建验证

-- 验证用户创建
SELECT username, account_status, default_tablespace, temporary_tablespace
FROM dba_users
WHERE username = 'APP_USER';

-- 验证权限
SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee = 'APP_USER';

SELECT grantee, owner, table_name, privilege
FROM dba_tab_privs
WHERE grantee = 'APP_USER';

SELECT grantee, granted_role
FROM dba_role_privs
WHERE grantee = 'APP_USER';

用户权限管理

权限设计

基于角色的权限管理

-- 创建角色
CREATE ROLE app_developer;
CREATE ROLE app_readonly;
CREATE ROLE app_admin;

-- 为角色分配权限
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO app_developer;
GRANT CREATE SESSION, SELECT ANY TABLE TO app_readonly;
GRANT CREATE SESSION, ALTER USER, DROP USER TO app_admin;

-- 为用户分配角色
GRANT app_developer TO dev_user;
GRANT app_readonly TO report_user;
GRANT app_admin TO admin_user;

最小权限原则

• 仅授予必要权限：根据用户职责授予最小必要权限
• 定期权限审查：定期检查和调整用户权限
• 权限分离：关键操作需要多个用户配合完成

权限变更

权限授予

-- 授予系统权限
GRANT CREATE PROCEDURE TO app_user;

-- 授予对象权限
GRANT EXECUTE ON hr.payroll_package TO app_user;

-- 授予角色
GRANT app_developer TO app_user;

权限回收

-- 回收系统权限
REVOKE CREATE TABLE FROM app_user;

-- 回收对象权限
REVOKE DELETE ON hr.employees FROM app_user;

-- 回收角色
REVOKE app_developer FROM app_user;

权限审计

-- 审计权限变更
AUDIT GRANT, REVOKE ON DEFAULT;

-- 查看权限变更审计记录
SELECT timestamp, action_name, object_name, privilege, grantee
FROM dba_audit_trail
WHERE action_name IN ('GRANT', 'REVOKE')
ORDER BY timestamp DESC;

用户状态管理

用户状态类型

状态	描述	操作权限
OPEN	正常打开状态	可以正常登录和操作
LOCKED	账户被锁定	无法登录，需要解锁
EXPIRED	密码过期	需要修改密码后登录
EXPIRED(GRACE)	密码过期但在宽限期内	可以登录但需要修改密码
LOCKED(TIMED)	密码尝试失败次数过多被锁定	需要管理员解锁

用户状态变更

锁定用户

-- 手动锁定用户
ALTER USER app_user ACCOUNT LOCK;

-- 锁定并过期用户
ALTER USER app_user ACCOUNT LOCK PASSWORD EXPIRE;

解锁用户

-- 解锁用户
ALTER USER app_user ACCOUNT UNLOCK;

-- 解锁并重置密码
ALTER USER app_user IDENTIFIED BY "NewPassword123!" ACCOUNT UNLOCK;

密码过期处理

-- 设置用户密码过期
ALTER USER app_user PASSWORD EXPIRE;

-- 检查用户状态
SELECT username, account_status, lock_date, expiry_date
FROM dba_users
WHERE username = 'APP_USER';

用户状态监控

-- 查看所有锁定的用户
SELECT username, account_status, lock_date
FROM dba_users
WHERE account_status LIKE '%LOCKED%';

-- 查看密码即将过期的用户
SELECT username, account_status, expiry_date,
       TRUNC(expiry_date - SYSDATE) AS days_until_expiry
FROM dba_users
WHERE expiry_date IS NOT NULL
AND TRUNC(expiry_date - SYSDATE) BETWEEN 0 AND 7
ORDER BY days_until_expiry;

-- 查看无效用户
SELECT username, account_status
FROM dba_users
WHERE account_status != 'OPEN';

用户密码管理

密码策略配置

密码配置文件

-- 创建密码配置文件
CREATE PROFILE secure_profile LIMIT
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LIFE_TIME 90
  PASSWORD_REUSE_TIME 365
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_LOCK_TIME 1
  PASSWORD_GRACE_TIME 7
  PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;

-- 修改现有配置文件
ALTER PROFILE default LIMIT
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LIFE_TIME 60
  PASSWORD_GRACE_TIME 5;

-- 为用户分配配置文件
ALTER USER app_user PROFILE secure_profile;

密码复杂度验证

-- 启用密码复杂度验证
ALTER PROFILE secure_profile LIMIT
  PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;

-- 查看密码验证函数
SELECT object_name, status
FROM dba_objects
WHERE object_name LIKE '%VERIFY_FUNCTION%';

密码重置

管理员重置密码

-- 重置用户密码
ALTER USER app_user IDENTIFIED BY "NewPassword123!";

-- 重置密码并解锁
ALTER USER app_user IDENTIFIED BY "NewPassword123!" ACCOUNT UNLOCK;

-- 强制用户下次登录修改密码
ALTER USER app_user IDENTIFIED BY "TempPassword123!" PASSWORD EXPIRE;

用户自助修改密码

-- 用户登录后修改密码
ALTER USER app_user IDENTIFIED BY "NewPassword123!" REPLACE "OldPassword123!";

-- 使用SQL*Plus修改密码
-- 在SQL*Plus中执行：
-- PASSWORD
-- 然后按照提示输入旧密码和新密码

密码安全措施

• 定期密码更换：设置合理的密码生命周期
• 密码复杂度：要求包含大小写字母、数字和特殊字符
• 密码历史：防止重用近期使用过的密码
• 登录尝试限制：限制失败登录尝试次数
• 密码锁定：失败尝试过多时自动锁定账户
• 密码加密：使用Oracle的密码加密功能

用户审计

审计配置

启用审计

-- 启用审计
ALTER SYSTEM SET audit_trail = 'DB,EXTENDED' SCOPE=SPFILE;

-- 重启数据库使审计生效
SHUTDOWN IMMEDIATE;
STARTUP;

审计策略

-- 审计用户登录/登出
AUDIT SESSION;

-- 审计用户管理操作
AUDIT CREATE USER, ALTER USER, DROP USER;

-- 审计权限变更
AUDIT GRANT, REVOKE;

-- 审计对象访问
AUDIT SELECT, INSERT, UPDATE, DELETE ON hr.employees;

审计数据分析

登录审计

-- 查看登录尝试
SELECT timestamp, username, os_username, terminal, action_name, returncode
FROM dba_audit_session
ORDER BY timestamp DESC;

-- 查看失败的登录尝试
SELECT timestamp, username, os_username, terminal, returncode
FROM dba_audit_session
WHERE returncode != 0
ORDER BY timestamp DESC;

操作审计

-- 查看用户管理操作
SELECT timestamp, username, action_name, object_name, new_name
FROM dba_audit_trail
WHERE action_name IN ('CREATE USER', 'ALTER USER', 'DROP USER')
ORDER BY timestamp DESC;

-- 查看权限变更
SELECT timestamp, username, action_name, privilege, grantee
FROM dba_audit_trail
WHERE action_name IN ('GRANT', 'REVOKE')
ORDER BY timestamp DESC;

审计报告

-- 生成用户活动报告
CREATE OR REPLACE PROCEDURE generate_user_audit_report (p_username VARCHAR2) AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('User Audit Report for: ' || p_username);
  DBMS_OUTPUT.PUT_LINE('=====================================');
  
  -- 登录活动
  DBMS_OUTPUT.PUT_LINE('Login Activity:');
  FOR rec IN (
    SELECT timestamp, action_name, returncode, terminal
    FROM dba_audit_session
    WHERE username = p_username
    ORDER BY timestamp DESC
    FETCH FIRST 10 ROWS ONLY
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(rec.timestamp, 'YYYY-MM-DD HH24:MI:SS') || ' - ' || 
                        rec.action_name || ' - Status: ' || rec.returncode || 
                        ' - Terminal: ' || rec.terminal);
  END LOOP;
  
  -- 操作活动
  DBMS_OUTPUT.PUT_LINE('\nUser Operations:');
  FOR rec IN (
    SELECT timestamp, action_name, object_name, privilege
    FROM dba_audit_trail
    WHERE username = p_username
    AND action_name NOT IN ('LOGON', 'LOGOFF')
    ORDER BY timestamp DESC
    FETCH FIRST 10 ROWS ONLY
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(rec.timestamp, 'YYYY-MM-DD HH24:MI:SS') || ' - ' || 
                        rec.action_name || ' - Object: ' || rec.object_name || 
                        ' - Privilege: ' || rec.privilege);
  END LOOP;
END;
/

用户归档和清理

用户归档

识别非活跃用户

-- 查找30天未登录的用户
SELECT u.username, u.account_status, MAX(a.timestamp) AS last_login
FROM dba_users u
LEFT JOIN dba_audit_session a ON u.username = a.username
WHERE u.username NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN')
GROUP BY u.username, u.account_status
HAVING MAX(a.timestamp) < SYSDATE - 30
   OR MAX(a.timestamp) IS NULL
ORDER BY last_login NULLS FIRST;

-- 查找180天未登录的用户
SELECT u.username, u.account_status, MAX(a.timestamp) AS last_login
FROM dba_users u
LEFT JOIN dba_audit_session a ON u.username = a.username
WHERE u.username NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN')
GROUP BY u.username, u.account_status
HAVING MAX(a.timestamp) < SYSDATE - 180
   OR MAX(a.timestamp) IS NULL
ORDER BY last_login NULLS FIRST;

归档流程

1. 通知用户：提前通知用户账户将被归档
2. 备份用户数据：备份用户拥有的对象和数据
3. 锁定账户：先锁定账户观察一段时间
4. 标记归档：在用户备注中标记归档信息
5. 清理权限：回收用户权限，仅保留最低权限

-- 锁定并标记用户
ALTER USER app_user ACCOUNT LOCK;

-- 记录归档信息（使用备注或自定义表）
UPDATE user_archive_info
SET archive_date = SYSDATE,
    archive_reason = 'Inactive for 180 days',
    archived_by = 'SYSTEM'
WHERE username = 'APP_USER';

用户删除

删除前准备

• 备份用户数据：导出用户 schema
• 撤销权限：回收用户所有权限
• 删除依赖对象：删除用户拥有的对象
• 审计记录：记录删除操作的原因和执行人

删除用户

-- 删除用户但保留对象
DROP USER app_user;

-- 删除用户及其所有对象
DROP USER app_user CASCADE;

-- 删除用户并清理相关审计记录
DROP USER app_user CASCADE;
DELETE FROM user_archive_info WHERE username = 'APP_USER';

清理验证

-- 验证用户已删除
SELECT username FROM dba_users WHERE username = 'APP_USER';

-- 验证用户对象已删除
SELECT owner, object_name, object_type
FROM dba_objects
WHERE owner = 'APP_USER';

-- 验证权限已清理
SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee = 'APP_USER';

用户管理自动化

自动化脚本

用户创建脚本

-- 创建用户的存储过程
CREATE OR REPLACE PROCEDURE create_user (
  p_username IN VARCHAR2,
  p_password IN VARCHAR2,
  p_tablespace IN VARCHAR2 DEFAULT 'USERS',
  p_quota IN NUMBER DEFAULT 100,
  p_role IN VARCHAR2 DEFAULT 'APP_USER'
) AS
BEGIN
  -- 创建用户
  EXECUTE IMMEDIATE 'CREATE USER ' || p_username || 
    ' IDENTIFIED BY "' || p_password || '"'
    || ' DEFAULT TABLESPACE ' || p_tablespace
    || ' TEMPORARY TABLESPACE TEMP'
    || ' QUOTA ' || p_quota || 'M ON ' || p_tablespace
    || ' PROFILE secure_profile';
  
  -- 授予基本权限
  EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO ' || p_username;
  
  -- 授予角色
  IF p_role IS NOT NULL THEN
    EXECUTE IMMEDIATE 'GRANT ' || p_role || ' TO ' || p_username;
  END IF;
  
  -- 记录创建信息
  INSERT INTO user_creation_log (username, created_date, created_by, role_assigned)
  VALUES (p_username, SYSDATE, USER, p_role);
  
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

定期检查脚本

-- 检查非活跃用户的存储过程
CREATE OR REPLACE PROCEDURE check_inactive_users (
  p_days IN NUMBER DEFAULT 90
) AS
  CURSOR c_inactive_users IS
    SELECT u.username, MAX(a.timestamp) AS last_login
    FROM dba_users u
    LEFT JOIN dba_audit_session a ON u.username = a.username
    WHERE u.username NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN')
    GROUP BY u.username
    HAVING MAX(a.timestamp) < SYSDATE - p_days
       OR MAX(a.timestamp) IS NULL;
BEGIN
  FOR rec IN c_inactive_users LOOP
    -- 锁定非活跃用户
    EXECUTE IMMEDIATE 'ALTER USER ' || rec.username || ' ACCOUNT LOCK';
    
    -- 记录到归档表
    INSERT INTO inactive_users_log (username, last_login_date, lock_date, locked_by)
    VALUES (rec.username, rec.last_login, SYSDATE, USER);
  END LOOP;
  
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

调度作业

定期执行的作业

-- 调度用户创建审计作业
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'AUDIT_USER_CREATION',
    job_type => 'STORED_PROCEDURE',
    job_action => 'audit_user_creation',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2',
    enabled => TRUE
  );
END;
/

-- 调度非活跃用户检查作业
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'CHECK_INACTIVE_USERS',
    job_type => 'STORED_PROCEDURE',
    job_action => 'check_inactive_users',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=WEEKLY; BYDAY=MON; BYHOUR=1',
    enabled => TRUE
  );
END;
/

-- 调度密码过期检查作业
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'CHECK_PASSWORD_EXPIRY',
    job_type => 'STORED_PROCEDURE',
    job_action => 'check_password_expiry',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=8',
    enabled => TRUE
  );
END;
/

常见问题（FAQ）

Q1: 如何批量创建用户？

A1: 可以使用以下方法批量创建用户：

• 使用脚本：创建存储过程批量处理
• 使用SQL*Loader：从CSV文件加载用户信息
• 使用数据泵：从现有用户模板导入
• 使用Enterprise Manager：通过图形界面批量创建

示例脚本：

-- 从表中批量创建用户
DECLARE
  CURSOR c_users IS
    SELECT username, password, tablespace_name, quota
    FROM user_import_table;
BEGIN
  FOR rec IN c_users LOOP
    create_user(rec.username, rec.password, rec.tablespace_name, rec.quota);
  END LOOP;
END;
/

Q2: 如何处理大量用户的权限管理？

A2: 处理大量用户权限的方法：

• 使用角色：创建角色并为用户分配角色
• 权限继承：利用角色继承简化权限管理
• 权限模板：为不同类型的用户创建权限模板
• 定期审查：使用自动化工具定期审查权限
• 集中管理：使用Enterprise Manager集中管理用户权限

Q3: 如何防止用户密码被破解？

A3: 防止密码被破解的措施：

• 强密码策略：设置复杂的密码验证函数
• 登录尝试限制：限制失败登录尝试次数
• 账户锁定：失败尝试过多自动锁定
• 密码过期：定期强制更换密码
• 密码历史：防止重用旧密码
• 审计登录：记录所有登录尝试
• 网络安全：使用SSL加密数据库连接

Q4: 如何恢复已删除的用户？

A4: 恢复已删除用户的方法：

• 从备份恢复：使用RMAN或数据泵备份恢复
• 使用闪回：如果启用了闪回功能，可以使用闪回数据库
• 重建用户：重新创建用户并导入备份的数据
• 使用回收站：对于10g+版本，可以从回收站恢复

注意：删除用户前一定要做好备份，恢复删除的用户可能会丢失数据。

Q5: 如何监控用户活动？

A5: 监控用户活动的方法：

• 启用审计：配置适当的审计策略
• 使用触发器：创建登录和操作触发器
• 查询动态视图：监控当前用户会话
• 使用Enterprise Manager：通过图形界面监控
• 第三方工具：使用专业的数据库监控工具

示例监控SQL：

-- 查看当前活动用户
SELECT username, sid, serial#, status, machine, program
FROM v$session
WHERE username IS NOT NULL;

-- 查看用户执行的SQL
SELECT s.username, s.sid, sql.sql_text
FROM v$session s, v$sql sql
WHERE s.sql_id = sql.sql_id
AND s.username IS NOT NULL;

Q6: 如何处理用户权限冲突？

A6: 处理用户权限冲突的方法：

• 权限审查：定期审查用户权限，识别冲突
• 权限分离：明确不同角色的权限边界
• 权限继承：合理使用角色继承，避免直接授予权限
• 文档化：记录权限分配的原因和审批流程
• 变更管理：建立权限变更的审批流程

Q7: 如何实现用户自助服务？

A7: 实现用户自助服务的方法：

• Web界面：开发简单的Web界面供用户操作
• 存储过程：创建安全的存储过程处理用户请求
• 密码重置：实现通过邮件或短信的密码重置
• 权限申请：提供权限申请和审批流程
• 账户管理：允许用户查看和更新个人信息

Q8: 如何确保用户管理符合合规要求？

A8: 确保用户管理符合合规要求的方法：

• 审计记录：详细记录所有用户管理操作
• 权限审查：定期进行权限审查并记录结果
• 访问控制：实施严格的访问控制策略
• 数据保护：保护敏感数据的访问
• 文档化：记录所有用户管理流程和决策
• 定期评估：定期评估合规性并进行调整

Q9: 如何处理用户表空间不足的问题？

A9: 处理用户表空间不足的方法：

• 监控表空间：定期监控用户表空间使用情况
• 自动扩展：启用数据文件自动扩展
• 增加配额：为用户增加表空间配额
• 表空间清理：清理用户表空间中的垃圾数据
• 分区表：对大型表使用分区减少空间使用
• 压缩：使用Oracle的表压缩功能

Q10: 如何优化用户管理的性能？

A10: 优化用户管理性能的方法：

• 批量操作：使用批量语句减少网络往返
• 索引优化：为用户管理相关表创建适当索引
• 缓存策略：合理使用Oracle的缓存机制
• 并行处理：对于大量用户操作使用并行处理
• 定期维护：定期收集用户管理相关表的统计信息
• 避免锁冲突：合理安排用户管理操作的时间