Auditing Specific SQL Statements in Oracle

To audit the use of an SQL statement in Oracle 12c, follow these specific steps:

1. Enable Database Auditing: Ensure that the database auditing feature is enabled. Set the `AUDIT_TRAIL` initialization parameter to an appropriate value, such as `DB` or `DB, EXTENDED`. This can be done by executing an `ALTER SYSTEM` command, like:
   

   ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE;
   

   
   After setting this parameter, restart the database for the change to take effect.
2. Use the AUDIT Statement: To audit specific SQL statements, use the `AUDIT` SQL command. For example, if you want to audit all SELECT statements issued against a specific table, execute a command like:
   

   AUDIT SELECT ON schema.table;
   

   
   Replace `schema.table` with the appropriate schema and table name. This command will track all SELECT operations performed on the specified table.
3. Check Audit Records: Audit records are stored in the `DBA_AUDIT_TRAIL` view. To review the audit records, execute a query such as:
   

   SELECT * FROM DBA_AUDIT_TRAIL WHERE SQL_TEXT LIKE '%SELECT%';
   

   
   This query retrieves audit records for SELECT statements. Modify the `WHERE` clause to filter the records according to your requirements.
4. Fine-Grained Auditing (FGA): For more specific control, consider using Fine-Grained Auditing. FGA allows you to audit based on specific conditions. To set up FGA, use the `DBMS_FGA` package. For example:
   

   BEGIN
   DBMS_FGA.ADD_POLICY(
   object_schema   => 'schema',
   object_name     => 'table',
   policy_name     => 'Your_Policy_Name',
   audit_condition => 'your_condition',
   audit_column    => 'column_name',
   statement_types => 'SELECT'
   );
   END;
   

   Replace the placeholders with the actual schema, table, policy name, conditions, and column names.
5. Regularly Review and Manage Audit Data: Audit data can grow quickly, so it's important to regularly review and manage this data. Implement procedures for regularly checking the `DBA_AUDIT_TRAIL` or `DBA_FGA_AUDIT_TRAIL` views, and archive or purge old records as necessary.
6. Audit Trail Maintenance: Ensure that the audit trail is backed up and maintained as part of your regular database backup and maintenance procedures.

By following these steps, you can effectively audit the use of SQL statements in Oracle 12c, allowing for enhanced monitoring and security within your database environment. Always refer to the latest Oracle documentation for any specific considerations related to your database version and configuration.

One of the three things you can audit in your Oracle database is the use of specific SQL statements. You can turn on auditing for a SQL statement with the following syntax:

AUDIT statement

where statement is either a specific type of SQL statement or a statement option that groups together multiple related statements. As an example, you could issue an AUDIT command with the statement option of TABLE, which would audit the CREATE TABLE, DROP TABLE, and TRUNCATE TABLE statements. You could also issue an AUDIT command for specific statements, such as the ALTER TABLE, DELETE TABLE, COMMENT TABLE, INSERT TABLE, SELECT TABLE, or UPDATE TABLE statement. Each of these AUDIT commands would create an audit record for each individual SQL statement. You can also use the keyword ALL to monitor all statements that can be specified with a statement option, which includes most Data Definition Language (DDL) operations.

The syntax above is the minimum syntax required for the AUDIT statement. You can also add any of the following optional qualifying clauses to shape the way that the AUDIT operation works:

1. The BY USER user clause is used to limit the auditing to statements issued by a particular user or list of users, separated by commas. If you do not use this clause, all users' statements will be audited.
2. You can choose to audit BY SESSION or BY ACCESS. These two options control the number of audit records created. The BY SESSION option, which is the default, writes a single record for all the same type of SQL statements written in a particular database session. The BY ACCESS option creates a record for each individual access. The BY ACCESS option normally creates many more audit records, but provides a finer-grained picture of database operations.
3. The WHENEVER [NOT] SUCCESSFUL clause limits auditing to either successful SQL statements or SQL statements that fail because of a lack of user privileges or a nonexistent database object in the statement. If a statement fails because of invalid syntax, it is not audited. If you do not specify either of these options, all SQL statements are audited.

To end auditing, you use the NOAUDIT command. This command has almost the same syntax and options as the AUDIT command, except that the NOAUDIT command does not allow the SESSION/USER option. Each NOAUDIT command turns off its corresponding AUDIT command. In the next lesson, you will learn how to view the audit trail created by auditing.

Click the Exercise link below to practice using auditing.
Auditing Specific SQL Statements - Exercise