Audit/Logging Policies in Oracle Database 19c

The ability to audit database activities is among the most important of all database security features. Auditing has costs in storage for the audit trail and may impact the performance of database or system on audited operations. There is also the additional cost to manage (store, backup, secure) and review the data in the audit trail. Audits may be targeted for alteration or destruction to hide unauthorised activity.

The basic types of standard auditing are object, statement and privilege auditing. Auditing “by session” usually creates fewer and slightly smaller audit records which is discouraged due to loss of fidelity.

Object auditing applies to specific objects for which it is invoked and always applies to all users.This is employed to audit sensitive objects and audit can be stored in in the database.

Privilege auditing audits the use of specific system privileges, but typically only if the user actually possesses the audited privilege.

Statement auditing audits the issuance of certain types of statements, usually without regard to privilege or lack thereof. Both privilege and statement audits may be specified for specific users or all users (the default).

Traditional Auditing

1. Enable ‘USER, ROLE’, ‘PROFILE, SYSTEM GRANT’ action audit. This will create audit while creating users, Setting up user limits, assigning privileges to a role and assign roles to users.
2. Enable ‘DATABASE LINK’, ‘PUBLIC DATABASE LINK’ action audit. This will create audit while creation or dropping database link for a user.
3. Enable ‘SYNONYM’, ‘PUBLIC SYNONYM’ action audit. This will create audit while creation of alternative name and description for a database object.
4. Enable ‘DIRECTORY’, ‘SELECT ANY DICTIONARY’ action audit. This will create audit while creation and dropping directories and while viewing the definitions of all schema objects.
5. Enable ‘GRANT ANY OBJECT PRIVILEGE’, ‘GRANT ANY PRIVILEGE’ action audit. This will create audit while user to grant any system privilege or compete object privileges.
6. Enable ‘PROCEDURE’, ‘DROP ANY PROCEDURE’ action audit. This will create audit while creating or dropping procedures, function, package or library.
7. Enable ‘ALL SYS.AUD$’, ‘ALTER SYSTEM’ action audit. This will create audit while changing the security settings of the system and while making attempts to read/update/delete/view audit trail.
8. Enable ‘TRIGGER’ action audit. This will create audit while create, drop, enable or disable any schema trigger and during DML actions.
9. Enable ‘CREATE SESSION’ action audit . This will create audit during all attempts to connect to the database including session disconnects.

Unified and Conditional Auditing

Unified Auditing enables selective and effective auditing inside the Oracle database using policies and conditions.

1. Enable ‘CREATE USER’, ‘ALTER USER’, ‘DROP USER’ action audit.
2. Enable ‘CREATE ROLE’, ‘ALTER ROLE’, ‘DROP ROLE’ action audit.
3. Enable ‘CREATE PROFILE’, ‘ALTER PROFILE’, ‘DROP PROFILE’ action audit.
4. Enable ‘CREATE SYNONYM’, ‘ALTER SYNONYM’, ‘DROP SYNONYM’ action audit.
5. Enable ‘CREATE DATABASE LINK’, ‘ALTER DATABASE LINK’,’DROP DATABASE LINK’ action audit.
6. Enable ‘CREATE PROCEDURE’, ‘ALTER PROCEDURE’, ‘DROP PROCEDURE’ action audit.
7. ‘Enable ‘CREATE TRIGGER’, ‘ALTER TRIGGER’, ‘DROP TRIGGER’ action audit.
8. Enable ‘GRANT’, ‘REVOKE’ action audit.
9. Enable ‘ALTER SYSTEM’ action audit.
10. Enable ‘AUDSYS.AUD$UNIFIED’ action audit.
11. Enable ‘LOGON’ AND ‘LOGOFF’ action audit.

Source : [1] CIS Oracle Database 19c Benchmark v1.0.0 — https://learn.cisecurity.org/l/799323/2020-09-28/2x5pd

[2] https://www.oracle.com/in/database/technologies/security/db-auditing.html

[3]https://www.avanade.com/nl-nl/blogs/be-orange/technology/enable-server-and-database-auditing