Advanced Security

41.1 Security Layers

Network (TLS/NNE, firewall, CMAN) → Authentication (passwords, Kerberos, PKI, IAM/Entra ID, MFA)
→ Authorization (privileges, roles, VPD, OLS, Database Vault) → Encryption (TDE) → Masking/Redaction
→ Auditing (Unified Auditing) → Monitoring (Audit Vault & DB Firewall, SQL Firewall, Data Safe)

41.2 Transparent Data Encryption (TDE)

Key management: software keystore, Oracle Key Vault, OCI Vault, HSM. Back up the wallet!

TDE requires the Advanced Security option on-premises (it's included in Oracle Cloud).

-- Keystore (wallet) setup (19c+: WALLET_ROOT + TDE_CONFIGURATION)
ALTER SYSTEM SET wallet_root = '/u01/app/oracle/admin/ORCL/wallet' SCOPE = SPFILE;
-- restart, then:
ALTER SYSTEM SET tde_configuration = 'KEYSTORE_CONFIGURATION=FILE' SCOPE = BOTH;
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY "Wallet#Pwd";
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "Wallet#Pwd" CONTAINER = ALL;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "Wallet#Pwd" WITH BACKUP CONTAINER = ALL;
ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY "Wallet#Pwd";

CREATE TABLESPACE secure_ts DATAFILE SIZE 1G ENCRYPTION USING 'AES256' ENCRYPT;   -- tablespace encryption
ALTER TABLESPACE users ENCRYPTION ONLINE USING 'AES256' ENCRYPT;                   -- online conversion
ALTER TABLE customers MODIFY (email ENCRYPT USING 'AES256');                       -- column encryption
SELECT * FROM v$encryption_wallet;

41.3 Data Redaction and Masking

Data Masking and Subsetting (static masking for non-production copies) through OEM or Data Safe.

BEGIN
  DBMS_REDACT.ADD_POLICY(object_schema => 'SHOP', object_name => 'CUSTOMERS', policy_name => 'redact_email',
      column_name => 'EMAIL', function_type => DBMS_REDACT.REGEXP,
      regexp_pattern => DBMS_REDACT.RE_PATTERN_EMAIL_ADDRESS,
      regexp_replace_string => DBMS_REDACT.RE_REDACT_EMAIL_NAME,
      expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') <> ''SUPPORT_ADMIN''');
END;
/

41.4 Virtual Private Database (VPD / Row-Level Security)

Column-level VPD and policy types (STATIC, SHARED, CONTEXT_SENSITIVE, DYNAMIC).

Real Application Security (RAS) is the modern successor for application users.

Oracle Label Security (OLS): multi-level, label-based row access.

CREATE OR REPLACE FUNCTION tenant_policy (p_schema VARCHAR2, p_obj VARCHAR2) RETURN VARCHAR2 IS
BEGIN
  RETURN 'tenant_id = SYS_CONTEXT(''APP_CTX'', ''TENANT_ID'')';
END;
/
BEGIN
  DBMS_RLS.ADD_POLICY(object_schema => 'SHOP', object_name => 'ORDERS', policy_name => 'orders_tenant',
                      function_schema => 'SEC', policy_function => 'TENANT_POLICY',
                      statement_types => 'SELECT,INSERT,UPDATE,DELETE', update_check => TRUE);
END;
/
-- Application context
CREATE CONTEXT app_ctx USING sec.ctx_pkg;
-- inside sec.ctx_pkg: DBMS_SESSION.SET_CONTEXT('APP_CTX', 'TENANT_ID', v_tenant);

41.5 Database Vault

Realms protect schemas even from DBAs and ANY privileges.

Command rules, rule sets, factors, separation of duties (DV_OWNER, DV_ACCTMGR), operations control (keeps common users out of PDB data).

41.6 Unified Auditing (12c+)

Fine-Grained Auditing (DBMS_FGA.ADD_POLICY) audits based on conditions or columns.

Mandatory auditing of SYS operations. Traditional auditing is desupported in 23ai.

CREATE AUDIT POLICY pol_sensitive
  ACTIONS SELECT ON shop.customers, UPDATE ON shop.customers, DELETE ON shop.customers
  WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') <> ''APP_USER''' EVALUATE PER SESSION;
AUDIT POLICY pol_sensitive;
AUDIT POLICY ora_logon_failures WHENEVER NOT SUCCESSFUL;
CREATE AUDIT POLICY pol_ddl ACTIONS CREATE TABLE, DROP TABLE, ALTER TABLE;
-- 23ai: column-level audit policies: ACTIONS UPDATE(salary) ON hr.employees
SELECT event_timestamp, dbusername, action_name, object_name, sql_text FROM unified_audit_trail ORDER BY event_timestamp DESC;
EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(...);

41.7 Other Security Features

SQL Firewall (23ai): learns the allowed SQL per user and blocks or logs anything else (protects against SQL injection and credential theft).

Blockchain and immutable tables: tamper-proof, insert-only tables with crypto-hash chains.

Strong authentication: Kerberos, RADIUS, PKI/TLS certificates, Centrally Managed Users with Active Directory, OCI IAM and Azure Entra ID tokens, MFA for privileged users.

Password hashing (12c SHA-2 verifiers, SQLNET.ALLOWED_LOGON_VERSION_SERVER).

DBSAT (Database Security Assessment Tool), Oracle Data Safe (cloud security assessments, user risk, discovery, masking, auditing).

Audit Vault and Database Firewall (AVDF). Secure configuration: remove default accounts and passwords (DBA_USERS_WITH_DEFPWD), apply quarterly patches, keep O7_DICTIONARY_ACCESSIBILITY and REMOTE_OS_AUTHENT off, set network ACLs for UTL_* packages, use least privilege.