Security and Auditing
Security and Auditing
This domain tests how access is controlled and recorded: authentication paths, privilege and role behaviour, profiles, encryption and redaction, and the unified auditing framework.
Core rules
- Privileged connections (SYSDBA, SYSOPER) authenticate through a password file with REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE; the OS group (OSDBA) allows local OS authentication. The DBA role is not SYSDBA.
- Roles apply at login but are disabled inside definer's rights PL/SQL, so objects referenced there need direct grants.
- Profiles enforce password and resource limits: FAILED_LOGIN_ATTEMPTS, PASSWORD_LIFE_TIME, PASSWORD_VERIFY_FUNCTION, SESSIONS_PER_USER, IDLE_TIME.
- Unified auditing is enabled by default in 19c and writes to the AUDSYS schema, queried via AUDIT_UNIFIED_POLICIES, AUDIT_UNIFIED_ENABLED_POLICIES and UNIFIED_AUDIT_TRAIL. Legacy AUDIT_TRAIL settings can still record traditional audit rows in SYS.AUD$ in mixed mode.
- Policies are created with CREATE AUDIT POLICY, enabled with AUDIT POLICY, and can include a WHEN condition using SYS_CONTEXT. AUDIT_ADMIN manages policies; AUDIT_VIEWER reads the trail.
- Fine-grained auditing (DBMS_FGA) records only rows matching a condition, including the SQL text; it complements object-level audit policies.
- Transparent Data Encryption protects data at rest: a keystore (software wallet or external), a master key, then column, tablespace or RMAN backup encryption. Data Redaction (DBMS_REDACT) masks values at query time without changing stored data.
Worked example
You must audit updates to HR.EMPLOYEES made by anyone other than HR. Run CREATE AUDIT POLICY pol_emp_upd ACTIONS UPDATE ON hr.employees WHEN 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''HR'''; then AUDIT POLICY pol_emp_upd; and confirm with a query on AUDIT_UNIFIED_ENABLED_POLICIES. Reasoning: the policy must exist first, then be enabled; a created but unenabled policy produces no rows.
Traps that cost marks
- A policy created but never enabled, or enabled in the wrong container.
- Expecting roles to satisfy grants inside definer's rights procedures.
- Treating TDE or redaction as protection from a user with keystore access or EXEMPT REDACTION POLICY.