Enable Auditing in Oracle: Step-by-Step Guide

1 year ago
William Carter
1 minute

To enable auditing in Oracle, you can follow the steps below:

Login to the Oracle database using the sysdba privilege.

Run the following command to enable standard audit function:

SQL> ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

3. Execute the following command to enable auditing functionality:

SQL> AUDIT SELECT TABLE, INSERT TABLE, UPDATE TABLE, DELETE TABLE BY ACCESS;
SQL> AUDIT SELECT TABLE, INSERT TABLE, UPDATE TABLE, DELETE TABLE BY SESSION;

Run the following command to enable the auditing feature:

SQL> AUDIT DROP ANY TABLE BY ACCESS;
SQL> AUDIT CREATE ANY TABLE BY ACCESS;
SQL> AUDIT ALTER ANY TABLE BY ACCESS;
SQL> AUDIT DROP ANY PROCEDURE BY ACCESS;
SQL> AUDIT CREATE ANY PROCEDURE BY ACCESS;
SQL> AUDIT ALTER ANY PROCEDURE BY ACCESS;

Enable auditing on the tables that require auditing.

SQL> AUDIT SELECT, INSERT, UPDATE, DELETE ON <table_name> BY ACCESS;

Restart the database to apply the changes.

Please note that enabling the audit function may have some impact on the system performance, so it is recommended to only enable it when necessary.

bannerAds