Lesson 9

This module discussed the use of auditing and how it is used in Oracle. Now you should be able to:

1. List the types of things that can be audited
2. Enable the audit feature
3. Audit the use of a SQL statement
4. Generate audit trail reports
5. Audit the use of a specific Oracle privilege
6. Audit the use of specific database objects
7. Delete old audit trail records

Auditing allows you to discover who did what with your data. Auditing is the process of creating a history of access that can be used to understand database operations as well as spot access violations and attempts. When you are configuring Oracle Database 11g, youwill be asked if youwant to keep the default security settings. If you do, auditing will be enabled and a new default password profile option will be in place. A number of other database initialization parameters will be reset at this time.

As an Oracle DBA, you have several options for auditing the use of a specific privilege in your database. Here are the three main approaches:

1. Traditional Auditing:
   This method involves configuring the database to record audit records whenever the specified privilege is used.
   Steps:
   1. Enable auditing: Set the `AUDIT_TRAIL` initialization parameter to a non-default value like `DB`.
   2. Specify the privilege to audit: Use the `AUDIT` statement to define the privilege you want to monitor. For example, `AUDIT CREATE SESSION;` will record every time the `CREATE SESSION` privilege is used.
   3. Review audit records: Analyze the audit records stored in the `AUDSYS` schema to identify instances where the specified privilege was used. You can use SQL queries like `SELECT * FROM SYS.AUDITING$ WHERE OBJTYPE='PRIVILEGE' AND PRIVILEGE_NAME='CREATE SESSION';` to filter the data.
2. DBMS_PRIVILEGE_CAPTURE Package:
   This PL/SQL package provides more granular control over privilege analysis compared to traditional auditing.
   Steps:
   1. Grant necessary roles: You need the `CAPTURE_ADMIN` role to create and manage privilege analysis policies.
   2. Create a policy: Define the privilege and other parameters for monitoring using the `DBMS_PRIVILEGE_CAPTURE.CREATE_POLICY` procedure.
   3. Enable the policy: Run the `DBMS_PRIVILEGE_CAPTURE.ENABLE_POLICY` procedure to start recording usage data.
   4. Generate reports: Use the `DBMS_PRIVILEGE_CAPTURE.GENERATE_REPORT` procedure to create reports summarizing the captured data. These reports are stored in the `DBA_PRIVILEGE_CAPTURE_*` views.
3. Enterprise Manager Cloud Control (EMCC): If you are using Oracle Enterprise Manager Cloud Control, you can leverage its built-in privilege analysis features.
   Steps:
   1. Navigate to the Security menu: Log in to EMCC and go to the `Security` section.
   2. Select Privilege Analysis: Choose the `Privilege Analysis` option within the Security menu.
   3. Create or select a policy: Define a new policy for the specific privilege or choose an existing one.
   4. Generate reports: Click the `Generate Report` button to create a detailed report on the privilege usage.

Choosing the right method:
The best approach for your situation depends on factors like:

• Required level of detail: Traditional auditing provides basic information, while DBMS_PRIVILEGE_CAPTURE and EMCC offer more granular reports.
• Complexity of analysis: Traditional auditing is simple to set up, while the package and EMCC require more configuration.
• Available resources: EMCC requires a licensed Oracle Management Cloud subscription.

Remember to choose the method that best suits your specific needs and provides the necessary insights into how the chosen privilege is being used in your Oracle database environment.

The database initialization parameter AUDIT_TRAIL controls the enabling and disabling of auditing. The default setting for this parameter is NONE, which means that no auditing will be performed, regardless of whether or not AUDIT commands are issued. It is important to remember that any auditing statements issued will not be performed if

AUDIT_TRAIL=NONE. 

Unless auditing is enabled in the database parameter initialization file, any auditing options that have been turned on will not create records in the audit trail. Auditing is not completely disabled unless it is set to NONE in the database parameter initialization file. You must set the database initialization parameter AUDIT_TRAIL to DB or OS in order to enable auditing. The DB setting means the audit trail records are stored in the database in the SYS.AUD$ table. OS will send the audit trail records to an operating system file and the OS setting is operating system-dependent and is not supported on all operating systems. Creating an audit trail is an invaluable tool for a variety of diagnostic and analytical tasks. You may not have to worry about auditing on a daily basis, but when something in your Oracle database requires further examination, auditing can help you immensely. In the next module, you will learn how Oracle supports other languages.

Before you go on, click the Quiz link below to test your knowledge of Oracle auditing.
Auditing Database Privileges - Quiz