Oracle Dba Security Encryption Auditing

Oracle DBA - Security, Encryption & Auditing

Auditing (Unified + Standard), security monitoring (failed logins, security events), CIS/STIG baseline assessment & reversible remediation, sensitive-data discovery, privilege analysis (least privilege), CVE/patch-level assessment, and the plan-only Tier-3 security-architecture runbooks (TDE, wallet/keystore, key rotation, network encryption, Data Redaction, VPD, Database Vault, AVDF, CVE patch apply) for Oracle 19c and 23ai (EE; single-instance and Data Guard; on-prem). Everything self-executing is T1 (observe) or T2 (reversible, dry-run-default). Every encryption/security architecture build-out and every CVE patch APPLY is T3 and lives only in references/runbooks.md — it prints commands for a human and never self-executes.

Scope & risk map

Preconditions

• SQLcl MCP connection dba_ai_conn is reachable and wallet-backed. All in-DB prechecks, postchecks, idempotency probes, and sql/*.sql run through it via connect_db / Connect-Db ("sqlcl-mcp" routing — in-DB SQL/PL-SQL). Never SYS/SYSTEM — except inside a T3 runbook that explicitly states AS SYSDBA / AS SYSKM (TDE key management genuinely needs SYSKM/SYSDBA), supplied interactively by the operator, never the default and never auto.
• Secrets resolve from the Oracle Wallet / external password store. No plaintext anywhere — and NO key material, keystore password, or TDE secret in any file, arg, env, or log. The helpers redact credential-shaped text; the scripts reject plaintext-credential argument patterns with ERR_SECRET (8). Keystore passwords for T3 keystore ops are typed interactively by the operator at runtime only.
• Helpers are sourced from _common (scripts/lib.sh / lib.ps1): connect_db, log_event, emit_metric, require_dry_run, guard_blocked_dry_run, require_approval_token, precheck, postcheck, print_banner. Not reinvented.
• Logging: structured key=value lines to the canonical paths /var/log/oracle-dba/security/security.log (Linux) / C:/ProgramData/oracle-dba/logs/security/security.log (Windows). Skill segment security for all tasks here.
• T2 dry-run default: every T2 script previews and changes nothing unless given --execute. Host operations that cannot go through SQLcl MCP (OPatch/datapatch, sqlnet.ora/TLS, AVDF agent, keystore files at the OS) are restricted and routed to scripts or — when structural/destructive — to the Tier-3 runbooks.

Procedures

Scripts ship as behaviour-equivalent .sh (Linux) / .ps1 (Windows) pairs. T2 scripts default to --dry-run; pass --execute to act. See each script's --help.

Audit report generation (T1)

• Trigger: daily audit review; investigating who did what.
• Precheck: dba_ai_conn reachable (read-only probe).
• Action: scripts/checks/audit_report.sh [--days N] [--max-failed N] / .ps1 + sql/audit_report.sql.
• Postcheck: n/a — emits KEYVAL metrics (enabled policies, events, failed actions) + findings.
• Rollback note: none — read-only.

Failed login / security event review (T1)

• Trigger: daily; brute-force suspicion; after an alert.
• Precheck: dba_ai_conn reachable.
• Action: scripts/checks/security_event_review.sh [--days N] [--max-failed-logons N] / .ps1 + sql/security_events.sql.
• Postcheck: n/a — surfaces failed logons, locked accounts, grant/revoke activity.
• Rollback note: none — read-only.

Standard / Unified Auditing configuration (T2)

• Trigger: enable/disable a Unified Audit policy (e.g. turn on ORA_SECURECONFIG, ORA_LOGON_FAILURES).
• Precheck: dba_ai_conn reachable; the policy must already be DEFINED (status probe).
• Action: scripts/configure_unified_audit.sh --policy NAME --action enable|disable [--execute] / .ps1.
• Postcheck: sql/unified_audit_status.sql shows the policy in the requested state.
• Rollback note: re-run with the opposite --action (AUDIT POLICY ⇄ NOAUDIT POLICY). CREATEing custom policies -> #unified-audit.

Audit-trail purge / housekeeping (T2)

• Trigger: scheduled trim of audit records older than retention (FRA/AUD$ pressure).
• Precheck: dba_ai_conn reachable; --before-days >= 7 (retention floor).
• Action: scripts/purge_audit_trail.sh [--before-days N] [--trail UNIFIED|STANDARD|OS|ALL] [--execute] / .ps1 -> DBMS_AUDIT_MGMT.
• Postcheck: purge API reported AUDIT_PURGE=DONE.
• Rollback note: only records older than retention are removed (recoverable only from a DB backup). Never TRUNCATE AUD$.

Security baseline / CIS hardening — assess (T1) & remediate (T2)

• Trigger: posture audit (assess); drift-correct one reversible parameter (remediate).
• Precheck: dba_ai_conn reachable. Remediate: param must be on the reversible allow-list.
• Action (assess): scripts/checks/security_baseline_check.sh [--max-fail N] / .ps1 + sql/security_baseline_check.sql -> ctl_*=PASS|FAIL scorecard.
• Action (remediate): scripts/harden_baseline.sh --param NAME --value VAL [--scope BOTH|SPFILE|MEMORY] [--execute] / .ps1.
• Postcheck: assess re-scores; remediate verifies the running value (MEMORY/BOTH).
• Rollback note: prior value is captured + logged; re-run with the prior value. Structural/Vault controls -> runbooks.

Security CVE patch — assess (T1)

• Trigger: monthly CPU review; confirm RU level vs the latest advisory.
• Precheck: dba_ai_conn reachable.
• Action: scripts/checks/cve_patch_assess.sh [--oracle-home DIR] / .ps1 + sql/patch_level_report.sql (+ opatch lsinventory).
• Postcheck: n/a — reports RU level, applied patches, invalid registry components.
• Rollback note: none — read-only. APPLYING a patch is T3 -> #cve-patch.

Sensitive data discovery (T1)

• Trigger: PII inventory; pre-redaction/TDE planning; compliance.
• Precheck: dba_ai_conn reachable.
• Action: scripts/checks/sensitive_data_discovery.sh / .ps1 + sql/sensitive_data_discovery.sql (dictionary metadata only — reads no PII).
• Postcheck: n/a — flags candidate sensitive columns + already-protected columns.
• Rollback note: none — read-only. Deep content scan (Data Safe) -> #tsdp.

Privilege analysis — capture (T2) & report (T1)

• Trigger: build a least-privilege baseline (start capture), then review unused privileges (report).
• Precheck: dba_ai_conn reachable.
• Action (capture): scripts/privilege_analysis.sh --action start|stop --capture NAME [--execute] / .ps1.
• Action (report): scripts/checks/privilege_analysis_report.sh [--capture NAME] / .ps1 + sql/privilege_analysis_report.sql.
• Postcheck: capture state verified in DBA_PRIV_CAPTURES; report lists USED vs UNUSED.
• Rollback note: a capture grants/revokes nothing; stop disables it. Acting on UNUSED (REVOKE) -> #priv-analysis.

Encryption posture / TDE status (T1)

• Trigger: before/after any keystore or key operation; encryption audit.
• Precheck: dba_ai_conn reachable.
• Action: scripts/checks/encryption_status_check.sh / .ps1 + sql/encryption_status.sql (metadata only — no key material).
• Postcheck: n/a — reports keystore state, master keys, encrypted tablespaces/columns.
• Rollback note: none — read-only. All mutating TDE/keystore ops are T3 runbooks.

Tier-3 runbooks

All security/encryption architecture build-out and CVE patch APPLY are Tier 3 and live in references/runbooks.md: #tde-setup, #wallet-mgmt, #key-rotation, #net-encryption, #data-redaction, #vpd, #database-vault, #avdf, #cve-patch, plus the operator-driven #unified-audit (create custom policy), #priv-analysis (REVOKE unused), and #tsdp (deep sensitive-data discovery).

These runbooks PRINT exact SQL / ADMINISTER KEY MANAGEMENT / OPatch / sqlnet.ora / DBMS_RLS / DBMS_REDACT / DVSYS commands for a human operator and NEVER self-execute. They are gated by require_approval_token / Require-ApprovalToken: an automated agent's only sanctioned action is to print the relevant section and stop with ERR_APPROVAL (6) until a human supplies a real change ticket/token (--token <TICKET> or ODB_APPROVAL_TOKEN; placeholders are rejected). The T2 scripts deliberately refuse structural escape hatches (non-allow-listed parameters, --before-days below the retention floor, TRUNCATE AUD$) and point to the runbook instead. SYSDBA / SYSKM is a T3-only, interactively-supplied exception; keystore passwords are never stored or logged. When in doubt, runbook.

See references/version-notes.md for 19c-vs-23ai behavioural deltas.