How To Use the Oracle Audit Operations Command

X Facebook LinkedIn

Do you ever feel frustrated by the limits of traditional relational databases? Leave those frustrations behind, and look no further than the Oracle AUDIT command!

Oracle auditing is critical in safeguarding databases and meeting the requirements of present applications. And in this tutorial, you will learn about Oracle auditing’s adaptive features and ability to oversee numerous database processes.

Read on and confidently protect your data with Oracle database auditing!

Prerequisites

This tutorial comprises hands-on demonstrations. To follow along, be sure you have a Windows 10 OS or later with Oracle Database installed.

Preparing the Oracle Environment for Auditing

Auditing in Oracle databases is crucial in maintaining security and ensuring compliance with industry regulations. By closely monitoring database activity, you can quickly identify and address any issues that may arise.

On that note, proper setup and configuration of Oracle auditing are vital to ensuring the effectiveness of your auditing strategy.

To prepare your environment for auditing, follow the steps below:

1. Open your command prompt and run the following sqlplus command to connect to your SQL shell as a database administrator (as sysdba).

sqlplus / as sysdba

2. Next, run the below ALTER SYSTEM command to SET the audit_trail initialization parameter value to DB, which enables Oracle database editing.

💡 Optionally, you can manually edit the init.ora file to change the audit_trail initialization parameter value.

ALTER SYSTEM SET audit_trail = DB SCOPE=SPFILE;

3. Now, run each command below to restart the Oracle database to apply the changes.

SHUTDOWN IMMEDIATE;
STARTUP;

Configuring a Statement-level Auditing

Auditing helps maintain database security, allowing you to monitor user activity and detect potential security threats or breaches. The good news is that Oracle offers various types of auditing. But for a start, you will configure statement-level auditing.

Statement-level auditing helps monitor specific types of SQL statements, regardless of the schema objects they access. Conclusively, this level of auditing enables you to ensure that only authorized SQL commands are executed.

To configure statement-level auditing:

Run the following command to create an AUDIT policy that tracks SELECT, INSERT, and DELETE commands on any table and the execution of any procedure in the database.

This command applies this audit policy to the user CTXSYS and logs successful executions of these statements.

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY CTXSYS BY SESSION WHENEVER SUCCESSFUL;

Creating an audit policy for statement-level auditing

Now, run the query below to view all (*) audit records (DBA_AUDIT_TRAIL) for the CTXSYS user.

You can modify this query to filter for specific actions, dates, or other criteria as necessary.

💡 Remember to limit auditing to essential operations to minimize performance overhead. Auditing can have a performance impact on your database.

SELECT * FROM DBA_AUDIT_TRAIL WHERE USERNAME = 'CTXSYS';

Configuring an Object-level Auditing

When your goal is to focus on specific database objects like tables or views, object-level auditing will do the trick. Object-level auditing is also perfect for tracking activity on sensitive or critical data.

To enable auditing for various operations, use the AUDIT SQL statements as follows:

In the Oracle SQL shell, run the statement below to track all SELECT statements on any TABLE in your database.

AUDIT SELECT TABLE;

Tracking all SELECT statements on any TABLE

Now, run the below AUDIT SQL statements to enable auditing for INSERT, UPDATE, and DELETE operations on a specific table named employees (or your own table).

AUDIT INSERT ON employees;
AUDIT UPDATE ON employees;
AUDIT DELETE ON employees;

Enabling object-level auditing for different operations

Configuring a System/Privilege-level Auditing

Unlike object-level auditing, system/privilege-level auditing monitors the use of user-system privileges. This auditing type ensures users are not misusing their privileges or performing unauthorized actions. People sneaking around behind your back is not a good thing.

To configure system/privilege-level auditing, use an AUDIT SQL statement with the desired system privilege.

Run the following SQL statement to enable system/privilege-level auditing for all users granted or revoked the ALTER SYSTEM privilege.

AUDIT ALTER SYSTEM;

Configuring System/Privilege-level Auditing

Configuring Fine-Grained Auditing (FGA)

When you need more precise control over auditing, the FGA will do the trick. FGA allows you to create policies for more specific auditing requirements.

With the proper configuration, you can create specific audit policies tailored to your organization’s needs. Focus on high-priority areas and capture the most relevant information for your security and compliance goals.

To see how FGA works, follow these steps:

Run the below statement to audit all SELECT statements on the SALARY column of the EMPLOYEES table.

BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema   => 'HR',
    object_name     => 'EMPLOYEES',
    policy_name     => 'SALARY_AUDIT',
    audit_column    => 'SALARY',
    audit_condition => NULL,
    handler_schema  => NULL,
    handler_module  => NULL);
END;
/

Now, run the following statement, where you specify an audit_condition.

This statement audits all SELECT statements on the SSN column of the EMPLOYEES table. Note that the audit only happens when the accessed SALARY exceeds (>) 100000.

BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema   => 'HR',
    object_name     => 'EMPLOYEES',
    policy_name     => 'SSN_AUDIT',
    audit_column    => 'SSN',
    audit_condition => 'SALARY > 100000',
    handler_schema  => NULL,
    handler_module  => NULL);
END;
/

Auditing SELECT statements only when a specified condition is met

Enabling Unified Auditing in Oracle Database 12c

Oracle’s flexibility with database auditing is its strong suit, but that flexibility complicates auditing at one point. Fortunately, Oracle Database 12c introduced a single framework called Unified Auditing. This framework consolidates all audit records, simplifying audit management and enhancing performance.

To enable and configure Unified Auditing, follow these steps:

1. Run the below command to SHUTDOWN your database. Shutting down the database ensures data consistency and prevents loss while enabling and configuring Unified Auditing.

SHUTDOWN IMMEDIATE;

2. Next, run the following command to restart the database in UPGRADE mode.

Restarting in upgrade mode allows you to perform administrative tasks, such as enabling Unified Auditing, that require changes to the database metadata or configuration.

STARTUP UPGRADE;

3. Once restarted, run the following statement to create a policy called my_policy (arbitrary) enabling Unified Auditing.

CREATE AUDIT POLICY my_policy
     ACTIONS ALL 
     WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') != ''SYS'''
     EVALUATE PER STATEMENT;

Creating a policy that enables Unified Auditing

4. Now, execute the below command to enable the new policy (my_policy) for the CTXSYS user (or your target users).

AUDIT POLICY my_policy BY CTXSYS;

Enabling the policy that enables Unified Auditing for target users

5. Lastly, run the below commands to restart the database in normal mode:

SHUTDOWN IMMEDIATE;
STARTUP;

Configuring Auditing for SYS User Activities

With Unified Auditing enabled, you can now configure auditing for user activities of the SYS user, the one with the highest level of privileges in an Oracle database.

Configuring SYS auditing lets you monitor SYS user actions, which helps ensure elevated privileges are not misused or abused.

Run the below command to enable the audit_sys_operations initialization parameter by setting its value to TRUE. This command enables the auditing of SYS user activities in your database.

ALTER SYSTEM SET audit_sys_operations = TRUE SCOPE=SPFILE;

After enabling SYS auditing, the corresponding audit records, such as CREATE, ALTER, and DROP TABLE operations performed by the SYS user, will be automatically generated.

The audit records are stored in the unified audit trail or the OS files, depending on your auditing configuration.

Configuring Auditing for SYS User Activities

Disabling Auditing Operations

After setting up and configuring your Oracle auditing policies, you may find situations where you need to enable or disable specific auditing rules temporarily or permanently.

Suppose there is planned system maintenance or upgrade in progress. You can avoid flooding your logs with routine notifications by disabling specific auditing rules.

To disable auditing operations, use the NOAUDIT statement as follows:

1. Run the below statement to disable auditing for all SELECT statements on any TABLE in your database.

NOAUDIT SELECT TABLE;

Disabling auditing for all SELECT statements on any TABLE

2. Next, run each statement below to disable auditing for INSERT, UPDATE, and DELETE operations ON a specific table (employees).

NOAUDIT INSERT ON employees;
NOAUDIT UPDATE ON employees;
NOAUDIT DELETE ON employees;

Disabling auditing for different operations

3. Now, run the below statement to disable specific policies, like the SALARY_AUDIT policy for FGA with the DBMS_FGA.DROP_POLICY procedure.

BEGIN
  DBMS_FGA.DROP_POLICY(
    object_schema => 'HR',
    object_name   => 'EMPLOYEES',
    policy_name   => 'SALARY_AUDIT');
END;
/

Managing Oracle Audit Trails

Audit trails let you trace the source of attacks and assess their impact on your database. But how do you manage audit trails? Oracle audit trails are records of audited activities in the database.

Trails can be stored in two ways:

Storage	Details
Database	Using `DB` or `DB_EXTENDED` setting for `AUDIT_TRAIL`.
OS Files	Using the `OS` or `XML` setting for `AUDIT_TRAIL`.

The following strategies can be employed for managing audit trail data:

Regularly review and analyze audit trail data. By actively working on your audit trail data, you can ensure that your auditing strategy remains effective and helps you maintain a secure database environment.

💡 Auditing is only effective if you actively review and manage the audit trail data. The regular analysis allows you to identify patterns, detect anomalies, and respond to potential security threats.

Use Oracle’s built-in tools, such as Oracle Enterprise Manager, to manage and analyze audit trails.
Archive and purge audit trail data periodically to maintain performance.

Read on to better understand how managing Oracle audit trails work.

Auditing All Oracle Users’ Viewing Activities

Auditing Oracle users’ viewing activities is critical to database security and regulatory compliance. This task allows monitoring of data access patterns, helps detect unauthorized access to sensitive data, and aids in forensic analysis during security breaches.

By tracking who is viewing what data and when it occurred, you enhance security and ensure compliance with various regulations. In turn, you have accumulated valuable information during security incidents.

To view audit records of Oracle user viewing activities, use SQL queries as follows:

1. Run the below query to SELECT and display all (*) audit records FROM statement, object, and system/privilege-level auditing (DBA_AUDIT_TRAIL).

SELECT * FROM DBA_AUDIT_TRAIL;

Viewing audit records from statement, object, and system/privilege-level auditing

2. Next, run the query below to SELECT and display all (*) records FROM FGA (DBA_FGA_AUDIT_TRAIL).

SELECT * FROM DBA_FGA_AUDIT_TRAIL;

Viewing audit records from FGA audit trails

3. Now, run the following query to SELECT and display all (*) audit records FROM statement, object, and system/privilege-level auditing (DBA_AUDIT_TRAIL). But this time, filter the results WHERE USERNAME equals a specific username (USER_NAME).

Would this method seem like you are singling out that user? Not really, especially when the goal is securing databases.

💡 Besides the WHERE SQL clause, you can also use ORDER BY and GROUP BY as needed.

SELECT * FROM DBA_AUDIT_TRAIL WHERE USERNAME = 'USER_NAME';

Viewing audit records filtered by USERNAME

Auditing All Oracle Users’ Data Change Activities

Monitoring changes to data and tracking access to sensitive information helps maintain the integrity of your database and prevent unauthorized modifications or data leaks.

To audit all Oracle user data change activities:

Run the below commands to AUDIT all INSERT, UPDATE, and DELETE statements executed by all users on ANY TABLE.

AUDIT INSERT ANY TABLE;
AUDIT UPDATE ANY TABLE;
AUDIT DELETE ANY TABLE;

Auditing all executed INSERT, UPDATE, DELETE statements

Auditing All Oracle Users’ Access Activities

Auditing all Oracle users’ access activities provides valuable insights into user behavior, aids in detecting potential security breaches, and ensures compliance with data protection regulations.

To audit all Oracle users’ access activities:

Run each command below to audit all connection (CONNECT) and disconnection (DISCONNECT) events for all users.

AUDIT CONNECT;
AUDIT DISCONNECT;

Auditing all user connections and disconnections

Removing Audit Records

Keeping outdated audit records only leads to confusion. Why not remove them? Removing audit records is also part of keeping your auditing policies up-to-date. Doing so ensures policies stay aligned with your organization’s security requirements and any changes in regulatory standards.

💡 Regularly reviewing and updating your policies helps you adapt to evolving security threats and maintain a robust defense against potential breaches.

To remove audit records, use the DELETE statement as follows:

1. Run the below statement to DELETE all audit records FROM statement, object, and system/privilege-level auditing (SYS.AUD$).

DELETE FROM SYS.AUD$;

Deleting all audit records from statement, object, and system/privilege-level auditing

2. Next, run the following statement to DELETE all audit records FROM FGA (SYS.FGA_LOG$).

DELETE FROM SYS.FGA_LOG$;

3. Finally, run the below statement to COMMIT the changes.

COMMIT:

COMMIT:

Committing all policy changes

Conclusion

Throughout this tutorial, you have learned the significance of the Oracle AUDIT operations command and strategies for maintaining database security. By implementing these strategies, you are taking critical steps toward data security and regulatory compliance.

Keep in mind that security threats are continually developing as time goes on. Ensure your database is safe by constantly enhancing and modifying your auditing strategies.

Now, since security is a broad subject, apart from database auditing, why not delve deeper into Oracle database security?

Hate ads? Want to support the writer? Get many of our tutorials packaged as an ATA Guidebook.

Explore ATA Guidebooks