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;
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;
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;
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 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;
/
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;
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;
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.
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;
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;
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;
2. Next, run the query below to SELECT
and display all (*
) records FROM
FGA (DBA_FGA_AUDIT_TRAIL
).
SELECT * FROM DBA_FGA_AUDIT_TRAIL;
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 useORDER BY
andGROUP BY
as needed.
SELECT * FROM DBA_AUDIT_TRAIL WHERE USERNAME = 'USER_NAME';
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 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;
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$;
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:
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?