Unified Auditing Growth, Privilege Creep, and a Local TDE Wallet
The security team enables unified auditing on a 1.2TB production database. Fourteen audit policies capture every SELECT on 3,000 tables for all users. The audit tablespace grows 40GB per month and is now 88% full; the purge job fails because the tablespace cannot extend. Compliance requires 13 months of retention for privileged actions and access to three sensitive tables, not for all SELECTs. Separately, an application account has held the DBA role since a 2023 migration and is shared by four batch jobs. TDE tablespace encryption is being enabled, and the wallet is stored on the database host with auto-login enabled. The next audit committee review is in two weeks.
1. Redesign the audit configuration to meet the 13-month requirement without letting the audit tablespace grow unchecked.
Read the worked answer
The current policy is over-broad: auditing every SELECT on 3,000 tables for all users creates volume that is not required and obscures the events compliance actually cares about.
Narrow the policies: create or modify policies to audit privileged actions (for example, SYSDBA actions, GRANT/REVOKE, user creation, and changes to audited objects) and all access to the three sensitive tables. Exclude application accounts that do not touch those tables. Audit both successful and unsuccessful actions only where required; auditing everything successful multiplies volume.
Separate storage: put the unified audit trail in its own tablespace with autoextend and a monitored maximum. Do not let it share space with application data. Use DBMS_AUDIT_MGMT to set the retention interval and run the purge job on a schedule. For 13 months, keeping all records in the database is expensive; archive older records to a read-only repository or export them, then purge from the database after the archive is verified.
Verify: measure the new daily volume for a week, confirm the purge job runs, and document which policy supports which compliance requirement. A policy that cannot be mapped to a requirement should be removed.
2. The application account has had DBA for two years and is shared by four batch jobs. How do you remediate this safely?
Read the worked answer
Treat this as both a privilege and an accountability problem.
Discover actual usage first. Use DBMS_PRIVILEGE_CAPTURE to capture the privileges the account actually exercises over a representative period, including the four batch jobs. Review DBA_USED_PRIVS and DBA_UNUSED_PRIVS. Do not revoke DBA based on assumption; a batch job may depend on a hidden privilege.
Replace DBA with a least-privilege role. Grant only the system privileges and object privileges the capture shows are needed, then test each batch job. Revoke DBA after the new role is validated. If a job genuinely needs an administrative privilege, grant it through a dedicated role, not the full DBA role.
Fix the shared account. Four jobs sharing one password destroys accountability. Create separate accounts per job or use a controlled service account with a password managed in a vault. Enforce password rotation and remove the shared credential. If separate accounts are impossible in the short term, at minimum change the password, restrict logon by host or service, and log the exception with an owner and an expiry date. Present the remediation plan to the audit committee with evidence.
3. TDE is being enabled with the wallet on the database host and auto-login enabled. What are the risks and the required changes?
Read the worked answer
Auto-login keeps the master key encrypted in a local wallet that the database can open without human intervention. That is convenient, but it means anyone who gains root or oracle-level access to the host can also open the wallet and decrypt the data. Storing the wallet on the same host as the database removes much of the protection against host compromise.
Required changes: move the wallet to a separate, hardened host or, better, use a centralized key manager or HSM supported by the database. If a local wallet must remain, restrict filesystem permissions, monitor access, and ensure the wallet is included in backups. Back up the wallet and test restore; losing the wallet means losing the encrypted data.
Operational steps: enable TDE for the target tablespaces, verify V$ENCRYPTED_TABLESPACES, and confirm that application performance is acceptable. Rotate the master key with ADMINISTER KEY MANAGEMENT and document the schedule. Do not leave auto-login as the only control; require a password or external key manager for administrative operations, and record every wallet operation in the audit trail.