Enable auditing for sysdba priviliges users in Oracle

Enable auditing for sysdba priviliges users in Oracle

In Oracle, we need to set the audit parameter for auditing purpose. But audit parameter has some limitation, when we set the audit parameter at DB value then it save the infromation in SYS schema view aud$ but it does not trace the sys schema commands during audit process.
For enable auditing of SYS or sysdba users commands, we need to set the audit parameter to OS or XML level. These parameter generate the audit output in file format that save in Operating system location.

For Enable the Auditing of SYS or Sysdba Priviliges users
Audit is generate log file at location specify. After setting the following database need to restart the DB.
Note:
OS value audit generate file in text format which can be read manually with notepad.
XML value generate log in XML format which can be read with help of V$XML_AUDIT_TRAIL view.

-- Set the location of audit in Operating system
ALTER SYSTEM SET AUDIT_FILE_DEST = 'c:\auditlog' SCOPE=SPFILE;

-- Enable the audit for SYS operations
ALTER SYSTEM SET AUDIT_SYS_OPERATIONS = TRUE SCOPE=SPFILE;

-- We can set the audit trail parameter for XML or OS level to start SYS or SYSDBA priviliges users.
ALTER SYSTEM SET AUDIT_TRAIL= XML SCOPE=SPFILE;
OR
ALTER SYSTEM SET AUDIT_TRAIL= OS SCOPE=SPFILE;
OR
ALTER SYSTEM SET AUDIT_TRAIL= XML,EXTENDED SCOPE=SPFILE;

Note: After changes, Oracle Database need to restart.

Read the Audit with XML view if Audit_trail parameter is XML

SELECT sql_text FROM v$XML_AUDIT_TRAIL WHERE EXTENDED_TIMESTAMP >= sysdate-1;


-- Also used to read audit XML format also.
SELECT * FROM DBA_COMMON_AUDIT_TRAIL;

Disable the Auditing

ALTER SYSTEM SET AUDIT_SYS_OPERATIONS = FALSE SCOPE=SPFILE;
ALTER SYSTEM SET AUDIT_TRAIL= NONE SCOPE=SPFILE;

Note: After changes, Oracle Database need to restart.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.