DB / Oracle / 底层与基础

Oracle 安全深度解析

2026.09.06

Oracle 安全深度解析

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

Oracle 安全体系[1]：

层次：

  • 认证
  • 授权
  • 加密
  • 审计
  • 数据保护

详细见：Oracle 数据库安全机制。

2. 用户管理

2.1 创建

CREATE USER alice 
  IDENTIFIED BY ****** 
  DEFAULT TABLESPACE users 
  TEMPORARY TABLESPACE temp 
  QUOTA 100M ON users 
  PROFILE default 
  PASSWORD EXPIRE 
  ACCOUNT UNLOCK;

2.2 修改

ALTER USER alice IDENTIFIED BY ******
ALTER USER alice DEFAULT ROLE ALL EXCEPT dba;
ALTER USER alice ACCOUNT LOCK;
ALTER USER alice QUOTA UNLIMITED ON users;

2.3 删除

DROP USER alice CASCADE;

3. SYS / SYSTEM

3.1 SYS

- 数据字典所有者
- SYSDBA
- 不可删除

3.2 SYSTEM

- 管理工具
- DBA
- 可锁定

3.3 SYSDBA / SYSOPER

-- SYSDBA
CONNECT sys/Oracle123 AS SYSDBA

-- SYSOPER
CONNECT sys/Oracle123 AS SYSOPER

-- 授权
GRANT SYSDBA TO alice;

4. 权限

4.1 系统

GRANT CREATE SESSION TO alice;
GRANT CREATE TABLE TO alice;
GRANT CREATE PROCEDURE TO alice;
GRANT SELECT ANY TABLE TO alice;

4.2 对象

GRANT SELECT, INSERT ON employees TO alice;
GRANT EXECUTE ON my_pkg TO alice;
GRANT REFERENCES ON employees TO alice;

4.3 WITH GRANT OPTION

GRANT SELECT ON employees TO alice WITH GRANT OPTION;
-- alice 可再授权

5. 角色

5.1 创建

CREATE ROLE app_role;
GRANT SELECT ON employees TO app_role;
GRANT app_role TO alice;

5.2 预定义

  • CONNECT
  • RESOURCE
  • DBA
  • SYSDBA
  • SYSOPER

5.3 启用/禁用

SET ROLE app_role;
SET ROLE ALL EXCEPT dba;
SET ROLE NONE;

6. Profile

6.1 创建

CREATE PROFILE app_profile LIMIT
  SESSIONS_PER_USER 5
  CPU_PER_SESSION UNLIMITED
  CPU_PER_CALL 30000
  CONNECT_TIME 60
  IDLE_TIME 30
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LIFE_TIME 90
  PASSWORD_REUSE_TIME 30
  PASSWORD_REUSE_MAX 5
  PASSWORD_LOCK_TIME 1
  PASSWORD_GRACE_TIME 7
  PASSWORD_VERIFY_FUNCTION verify_function;

6.2 分配

ALTER USER alice PROFILE app_profile;

7. 密码策略

7.1 复杂度

CREATE OR REPLACE FUNCTION verify_function(
  username VARCHAR2,
  password VARCHAR2,
  old_password VARCHAR2
) RETURN BOOLEAN IS
BEGIN
  -- 长度
  IF LENGTH(password) < 8 THEN
    RETURN FALSE;
  END IF;
  
  -- 复杂度
  IF NOT REGEXP_LIKE(password, '[A-Z]') THEN
    RETURN FALSE;
  END IF;
  IF NOT REGEXP_LIKE(password, '[a-z]') THEN
    RETURN FALSE;
  END IF;
  IF NOT REGEXP_LIKE(password, '[0-9]') THEN
    RETURN FALSE;
  END IF;
  
  RETURN TRUE;
END;
/

7.2 默认

@?/rdbms/admin/utlpwdmg.sql

8. TDE（Transparent Data Encryption）

8.1 启用

-- 1. Wallet
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY ******
ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY ******

-- 2. 表空间
CREATE TABLESPACE secure_ts DATAFILE '...' SIZE 100M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

-- 3. 列
ALTER TABLE employees MODIFY (ssn ENCRYPT USING 'AES256');

详细见：Oracle TDE 透明数据加密。

9. VPD（Virtual Private Database）

9.1 策略

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema => 'scott',
    object_name => 'employees',
    policy_name => 'emp_policy',
    function_schema => 'sys',
    policy_function => 'emp_sec_func',
    statement_types => 'select, insert, update, delete'
  );
END;
/

9.2 函数

CREATE OR REPLACE FUNCTION emp_sec_func(
  schema_var IN VARCHAR2,
  table_var IN VARCHAR2
) RETURN VARCHAR2 IS
  v_user VARCHAR2(100);
BEGIN
  v_user := SYS_CONTEXT('USERENV', 'SESSION_USER');
  RETURN 'dept_id IN (SELECT id FROM departments WHERE mgr = ''' || v_user || ''')';
END;
/

详细见：Oracle VPD 虚拟专用数据库。

10. Database Vault

10.1 概述

- 分离职责
- 多层访问控制
- DBA 限制

10.2 Realm

- 保护对象
- Realm Owner
- Realm Authorization

详细见：Oracle Database Vault 配置。

11. Data Redaction

11.1 策略

BEGIN
  DBMS_REDACT.ADD_POLICY(
    object_schema => 'scott',
    object_name => 'employees',
    policy_name => 'redact_ssn',
    column_name => 'ssn',
    function_type => DBMS_REDACT.FULL,
    expression => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') != ''HR'''
  );
END;
/

详细见：Oracle Data Redaction 数据脱敏。

12. Label Security

-- 标签
SA_SYSDBA.CREATE_POLICY('EMP_POLICY', 'EMP_LABEL');

-- 表
SA_COMPONENTS.CREATE_LEVEL('EMP_POLICY', 100, 'S', 'SECRET');
SA_COMPONENTS.CREATE_LEVEL('EMP_POLICY', 90, 'C', 'CONFIDENTIAL');

-- 应用
SA_POLICY_ADMIN.APPLY_TABLE_POLICY('EMP_POLICY', 'SCOTT', 'EMPLOYEES');

13. 审计

13.1 传统

AUDIT SELECT ON employees BY ACCESS;
AUDIT CREATE TABLE BY alice BY ACCESS;
AUDIT ALL;
NOAUDIT ALL;

13.2 Unified Audit（12c+）

CREATE AUDIT POLICY emp_audit
  PRIVILEGES SELECT ANY TABLE
  ACTIONS SELECT ON scott.employees
  WHENEVER SUCCESSFUL;

AUDIT POLICY emp_audit BY alice;

详细见：Oracle Audit 审计配置。

14. 网络加密

14.1 Native

SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256)

14.2 SSL

SQLNET.AUTHENTICATION_SERVICES = (TCPS)
WALLET_LOCATION = (SOURCE = ...)

详细见：Oracle 网络加密与 SSL。

15. SQL Firewall（23ai+）

BEGIN
  DBMS_SQL_FIREWALL.CREATE_PROFILE('app_profile');
  DBMS_SQL_FIREWALL.ENABLE_PROFILE('app_profile');
  DBMS_SQL_FIREWALL.SET_MODE('app_profile', 'LEARN');
END;
/

16. 监控

16.1 登录

SELECT 
  username, 
  userhost, 
  terminal,
  timestamp,
  action_name
FROM dba_audit_session
WHERE timestamp > SYSDATE - 1;

16.2 失败登录

SELECT * FROM dba_audit_session WHERE returncode != 0;

16.3 用户

SELECT username, account_status, lock_date, expiry_date 
FROM dba_users;

17. 常见坑与排错

17.1 ORA-01017

- 用户名/密码错
- 大小写敏感
- sec_case_sensitive_logon

17.2 ORA-28000

- 账户锁定
- FAILED_LOGIN_ATTEMPTS
- ALTER USER ... ACCOUNT UNLOCK

17.3 ORA-28365

- Wallet 未打开
- ALTER SYSTEM SET WALLET OPEN

18. 最佳实践

  1. 最小权限：安全
  2. 角色管理：清晰
  3. 密码策略：复杂
  4. Profile：资源
  5. TDE：加密
  6. VPD：行级
  7. Database Vault：DBA 控制
  8. Redaction：脱敏
  9. 审计：监控
  10. 网络加密：传输

19. 参考资料

[1] Oracle Database Security Guide 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/