# Oracle Dba Security Encryption Auditing

> USE THIS SKILL WHENEVER the user mentions Oracle security, auditing, encryption, TDE / Transparent Data Encryption, wallet / keystore / TDE key rotation, ADMINISTER KEY MANAGEMENT, Unified or Standard/traditional auditing, audit policies, AUDIT/NOAUDIT, audit trail / AUD$ / UNIFIED_AUDIT_TRAIL / audit purge, failed-login or security-event review, CIS / STIG / security baseline / hardening, default passwords, privilege analysis (DBMS_PRIVILEGE_CAPTURE) / least privilege / unused privileges, sensitive-data discovery / PII, Data Redaction, Virtual Private Database (VPD / DBMS_RLS / row-level security), Database Vault, Audit Vault & Database Firewall (AVDF), network encryption (TLS / SSL / native / sqlnet.ora), security CVE / Critical Patch Update / datapatch, or errors like ORA-28365 (wallet not open), ORA-46630 / ORA-46658 (keystore), ORA-01017 (bad credential) — EVEN IF they do not name the specific task. Covers audit reporting, security-event review, Unified Audit configuration, bounded audit-trail purge, CIS

- Skill: `shreyas70773/oracle-dba-security-encryption-auditing` (Agent Skill, multi-file: 40 files)
- Install (CLI): `npx skillmds@latest add shreyas70773/oracle-dba-security-encryption-auditing`
- Raw SKILL.md: https://api.skillmd.com/api/skills/shreyas70773/oracle-dba-security-encryption-auditing/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Product & Planning
- Author: Shreyas70773 (https://skillmd.com/u/shreyas70773)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/shreyas70773/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

| Task | Level | Tier | Mechanism (script / sql / runbook path) | Idempotent? |
|------|-------|------|------------------------------------------|-------------|
| Audit report generation | L1 | T1 | `scripts/checks/audit_report.sh` / `.ps1` + `sql/audit_report.sql` | yes — read-only (emits metrics + findings, never mutates) |
| Failed login / security event review | L1 | T1 | `scripts/checks/security_event_review.sh` / `.ps1` + `sql/security_events.sql` | yes — read-only |
| Standard / Unified Auditing configuration | L2 | T2 | `scripts/configure_unified_audit.sh` / `.ps1` + `sql/unified_audit_manage.sql` (act) + `sql/unified_audit_status.sql` (pre/post) | yes — enable already-enabled policy -> noop; AUDIT⇄NOAUDIT inverses |
| Audit-trail purge (housekeeping) | L2 | T2 | `scripts/purge_audit_trail.sh` / `.ps1` + `sql/audit_purge.sql` | yes — retention-guarded (floor 7d); 0 old rows -> noop; DBMS_AUDIT_MGMT only |
| Security baseline / CIS hardening — assess | L2 | T1 | `scripts/checks/security_baseline_check.sh` / `.ps1` + `sql/security_baseline_check.sql` | yes — read-only scorecard (`ctl_*=PASS\|FAIL`) |
| Security baseline / CIS hardening — remediate | L2 | T2 | `scripts/harden_baseline.sh` / `.ps1` + `sql/baseline_param_apply.sql` + `sql/baseline_param_status.sql` | yes — allow-listed reversible params only; value matches -> noop; prior value captured |
| Security CVE patch — assess | L2 | T1 | `scripts/checks/cve_patch_assess.sh` / `.ps1` + `sql/patch_level_report.sql` | yes — read-only (DBA_REGISTRY_SQLPATCH + opatch lsinventory) |
| Security CVE patch — apply | L2 | **T3** | `references/runbooks.md#cve-patch` | no — OPatch/datapatch is host-level + structural + downtime |
| Sensitive data discovery | L2 | T1 | `scripts/checks/sensitive_data_discovery.sh` / `.ps1` + `sql/sensitive_data_discovery.sql` | yes — read-only metadata-only scan (reads no PII) |
| Privilege analysis — capture start/stop | L2 | T2 | `scripts/privilege_analysis.sh` / `.ps1` + `sql/privilege_capture_manage.sql` | yes — start on enabled -> noop; capture records only, grants/revokes nothing |
| Privilege analysis — report | L2 | T1 | `scripts/checks/privilege_analysis_report.sh` / `.ps1` + `sql/privilege_analysis_report.sql` | yes — read-only |
| Encryption posture / TDE status | L1 | T1 | `scripts/checks/encryption_status_check.sh` / `.ps1` + `sql/encryption_status.sql` | yes — read-only (no key material read) |
| Transparent Data Encryption (TDE) setup | L3 | **T3** | `references/runbooks.md#tde-setup` | no — encrypts datafiles; new master key; structural |
| Wallet / keystore management | L3 | **T3** | `references/runbooks.md#wallet-mgmt` | partial — OPEN is repeatable; CREATE/MERGE/migrate is one-shot |
| TDE key rotation | L3 | **T3** | `references/runbooks.md#key-rotation` | no — each rotation creates a new master key (forward-only) |
| Network encryption (TLS / native) setup | L3 | **T3** | `references/runbooks.md#net-encryption` | no — edits sqlnet.ora/listener; restarts listener |
| Data Redaction configuration | L3 | **T3** | `references/runbooks.md#data-redaction` | partial — policy add is one-shot; ALTER/DROP reverse it |
| Virtual Private Database (VPD) setup | L3 | **T3** | `references/runbooks.md#vpd` | partial — ADD_POLICY one-shot; DROP_POLICY reverses |
| Database Vault configuration | L3 | **T3** | `references/runbooks.md#database-vault` | no — enabling DV restructures the privilege model |
| Audit Vault & Database Firewall (AVDF) management | L3 | **T3** | `references/runbooks.md#avdf` | no — multi-host appliance + agent deployment |

## 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.