Instructor's Notes

(If applicable)

• Lesson 10: Introduction to Auditing
• The world today depends on security, and auditing is a critical component of securing an Oracle database. With auditing, we can track the user activity on our database.
• In this lesson you will become aware of and understand the common auditing techniques available. Then, you will be presented with a reasonably in-depth introduction to several common auditing techniques, including:
• Auditing SYSDBA connections
• The AUDIT SQL statement
• Auditing with triggers.
• However, this lesson does not pretend to be an exhaustive dissection on auditing. It is simply a real good place to start.
• Let’s get started…

Instructor's Notes

(If applicable)

• There are several techniques available for auditing user activity in an Oracle database. I have listed the common techniques here:
• Connections using SYSDBA privilege are automatically audited. We can also optionally audit the activities of these connections.
• With a simple AUDIT statement we can see who did it, when they did it (date and time), the statement executed and the object affected (if applicable).
• By coding our own triggers (or by using the supplied LOGMINER utility, which interrogates the redo log files), the DBA can also see the values that have been changed in database tables.
• Fine Grained auditing (FGA) is an Oracle9i feature that allows you to conditionally audit SELECT statements. We can even capture the SCN and bind variable values used. By using the SCN of the query with the flashback query feature, we have the possibility of seeing the actual data that the original query saw. I’ll provide just a brief introduction to FGA at the end of this lesson.
• You will learn about SYSDBA auditing, the AUDIT statement and simple audit triggers in this lesson. Let’s look at each in turn…

Instructor's Notes

(If applicable)

• Auditing is configured with initialization parameters. Use the AUDIT_TRAIL parameter to enable and specify the location of the audit trail. The audit trail is the collection of records that reveal user activity on the database. The parameter can have the following values:
• NONE or FALSE – Audit records not written.
• OS – On Unix or Linux servers, audit records are written to an OS file. Set init parameter AUDIT_FILE_DEST to control location of the OS file. The default location is $ORACLE_HOME/rdbms/audit. On Windows servers the Event Log/Viewer receives the audit records (see Control Panel, Administrative Tools, Event Viewer).
• DB or TRUE – Audit records are written to the SYS.AUD$ table.
• Use the AUDIT_SYS_OPERATIONS parameter to audit actions by SYSDBA connections.


• Notes for this slide continue on next page…

Instructor's Notes

(If applicable)

• Notes continued from the previous page:
• Note that AUDIT_TRAIL is a static parameter, so ‘SCOPE=SPFILE’ is on the ALTER SYSTEM statement and a database restart is required.
• Supplemental Notes
  • If necessary, run database script CATAUDIT.SQL to build the SYS.AUD$ table (usually run by CATALOG.SQL). CATNOAUD.SQL can be run to drop audit-related objects.
  • These scripts are found in the $ORACLE_HOME/RDBMS/ADMIN directory.

Instructor's Notes

(If applicable)

• All SYSDBA connections are automatically audited, regardless of the AUDIT_TRAIL value. They are written to the Event Log on Windows servers, and an OS file in $ORACLE_HOME/RDBMS/AUDIT directory.
• On the previous slide you learned that by setting parameter AUDIT_SYS_OPERATIONS=TRUE we can also audit the activities of SYSDBA connections. Similar to connection auditing, these activity audit records are always written to an OS file in the $ORACLE_HOME/RDBMS/AUDIT directory (or the Event Log for Windows servers), regardless of the AUDIT_TRAIL setting.
• In this Linux example (see slide), you see that we discovered that client user ‘SUZANNE’ dropped the TEST tablespace at 10:30 AM on April 7.

Instructor's Notes

(If applicable)

• Notes continued from the previous page:
• If you are using a Windows server, look in the Event Log (see Control Panel, Administrative Tools, Event Viewer, Application Log). It will look something like this:

Instructor's Notes

(If applicable)

• Since the auditing of SYSDBA connections is automatic and cannot be turned off, you will wind up with many small audit files in the $ORACLE_HOME/ RDBMS/ADMIN directory. You will probably want to delete these files every so often so that you do not consume all the space on that mount point.
• For security purposes, consider protecting the directory (at the OS level) that contains the audit files. By prohibiting all but the most trusted personnel from accessing that directory, you will prevent people from deleting the audit files without your permission and thus increase your chances of determining who did what when needed.
• Optional Mini-Workshop
  1. Enable SYSDBA auditing for your database.
  2. Insure you’re connected as SYSDBA
  3. Execute: GRANT ALTER USER TO PUBLIC;
  4. Execute: REVOKE ALTER USER FROM PUBLIC;
  5. Find the audit records for this activity.

Instructor's Notes

(If applicable)

• The SQL AUDIT statement is used to enable the standard auditing feature of Oracle. It can audit the use of SQL statements or accesses to a specific object. Oracle captures the user, SQL statement, object name and date/time of the operation. I strongly recommend that you review the Oracle9i SQL Reference for complete details on this statement as there are many useful options.
• Let’s discuss the examples shown here. All of these examples require that the AUDIT_TRAIL parameter value be set to OS, DB or TRUE:
• AUDIT TABLE – This will cause all of the following statements to be audited: CREATE TABLE, DROP TABLE and TRUNCATE TABLE. Oddly ALTER TABLE is not audited with this statement; that is done with AUDIT ALTER TABLE.
• AUDIT ALL – “ALL” causes the audit of all SQL statements listed in Table 12.1 of the SQL Reference. This includes most forms of CREATE and DROP.



• Notes for this slide continue on next page…

Instructor's Notes

(If applicable)

• Notes continued from the previous page:
• AUDIT DROP ANY TABLE – This audits the use of the DROP ANY TABLE system privilege. For example, Oracle will audit user ‘SCOTT’ attempting to DROP a table owned by user ‘PRODUCTION’.
• AUDIT ALL ON SCOTT.EMP – This is an object-level audit. All activity against SCOTT.EMP will be audited. This includes SELECT, INSERT, UPDATE and DELETE. Refer to the SQL Reference for a complete list.
• The NOAUDIT SQL statement is used to turn off auditing. These statements will turn off the auditing enabled by the previous examples:
• NOAUDIT TABLE
• NOAUDIT ALL
• NOAUDIT DROP ANY TABLE
• NOAUDIT ALL ON scott.emp
• Next, we will learn about SQL statement and object auditing in more detail.
• Supplemental Notes
  • You need the following privilege to use the AUDIT statement:
    • AUDIT SYSTEM system privilege to audit SQL statements.
    • AUDIT ANY system privilege to audit objects (or be the owner of the object).
  • SQL statement auditing is active upon the next logon. Object auditing is active immediately.

Instructor's Notes

(If applicable)

• Use the AUDIT SESSION statement to enable auditing of both successful and unsuccessful logon attempts for all users (system-wide).
• As shown in this example, query DBA_STMT_AUDIT_OPTS to display the audit options currently in effect. Note that USER_NAME is NULL for system-wide auditing. This means that any user logon is audited. See the supplemental notes below for an example of limiting the auditing to a specific user.
• You can turn logon auditing off with the NOAUDIT statement:
• SQL> NOAUDIT SESSION;
• Noaudit succeeded.


• Supplemental Notes
  • There are many parameters for the AUDIT SESSION statement. Here we demonstrate several of these parameters:



• Notes for this slide continue on next page…

Instructor's Notes

(If applicable)

• Audit only successful login attempts:
• SQL> audit session whenever successful;
• Audit succeeded.


• SQL> select audit_option, success, failure
• 2 from dba_stmt_audit_opts
• SQL> /

• Add audit for unsuccessful attempts. Note that the AUDIT commands are cumulative:
• SQL> audit session whenever not successful;
• Audit succeeded.


• SQL> select audit_option, success, failure
• 2 from dba_stmt_audit_opts;

• The BY USER clause is supported for all statement and privilege auditing. For example, audit only logins by user 'SCOTT':
• SQL> noaudit session;
• Noaudit succeeded.


• SQL> audit session by scott;
• Audit succeeded.


• SQL> select user_name, audit_option, success, failure
• 2 from dba_stmt_audit_opts;

Instructor's Notes

(If applicable)

• As stated earlier in this lesson, audit records are written to an OS file or the SYS.AUD$ table, depending on the value of the AUDIT_TRAIL parameter. Instead of directly querying SYS.AUD$, there is a convenient set of views that are easier to use.
• If AUDIT_TRAIL=DB, query the DBA_AUDIT_SESSION dictionary view to display audit records related to logon (and logoff) attempts (as shown in the example above). This query is available to you in your supplied scripts as AUD_LOGINS.SQL.
• Supplemental Notes
  • The DBA_AUDIT_TRAIL view is an aggregate of all audited activity. It has many uses, including finding the IP address of the user connecting to your database:
  • SQL> select comment_text from dba_audit_trail;
  • COMMENT_TEXT
  • ---------------------------------------------------------
  • Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.3)(PORT=1503))
  • Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.3)(PORT=1502))

Instructor's Notes

(If applicable)

• The AUDIT SYSTEM GRANT statement captures all attempts to grant or revoke system privileges or roles. This is important because if someone acquires the ALTER USER privilege (shown in the second example above), that user can now alter the password for any user, then login to that user with the new password (knowledge of the original password is not required). Hence, ALTER USER privilege in the wrong hands could be disastrous.
• Supplemental Notes
  • There are many variations of the AUDIT statement, and many overlap in functionality. For example, to specifically audit any use of the ALTER USER command, this statement will do nicely:
  • AUDIT ALTER USER;

Instructor's Notes

(If applicable)

• If AUDIT_TRAIL=DB, dictionary view DBA_AUDIT_STATEMENT contains audit records describing the use of GRANT, REVOKE, AUDIT, NOAUDIT and ALTER SYSTEM statements.
• This example illustrates that you can easily monitor attempts to grant privileges. The columns and values shown above are described here:
• USERNAME – The name of the Oracle user that attempted the statement. Users ‘DAVE’ and ‘SYSTEM’ have both attempted to execute the ALTER USER statement. (Only SYSTEM was successful.)
• ACTION_NAME – Loosely, the statement that was executed. It is actually the statement “type”; you can infer from SYSTEM GRANT that GRANT of a system privilege was executed.
• SYS_PRIVILEGE – System privilege that was granted or revoked by the user.
• GRANTEE – The user receiving the privilege.
• See supplied script AUD_GRANTS.SQL.

Instructor's Notes

(If applicable)

• It is also possible to audit activity on a specific object. This is called "object-Level auditing". Object-level audits can be either by session or by access:
• By session writes 1 audit record for each session, irregardless of the number of times the session accesses the object. This is the default. Since 'by access' is not specified in the example above, the auditing is 'by session'.
• By access writes 1 audit record for each access to the object. Use caution with this type of auditing because many rows/records of audit activity can be generated very quickly, overwhelming your SYS.AUD$ table or OS file.
• This example (see slide) configures auditing for the SCOTT.EMP table. We are auditing all UPDATE, DELETE and INSERT attempts (by session) and all successful SELECT attempts (by access).

Instructor's Notes

(If applicable)

• The DBA_OBJ_AUDIT_OPTS dictionary view records the object-level auditing in effect for the database. The following is the key to reading the output from the DBA_OBJ_AUDIT_OPTS view.
• Each audit option has two possible settings,
• WHENEVER SUCCESSFUL and
• WHENEVER NOT SUCCESSFUL, separated by “/”.
• On either side of the "/" you will find the following characters:
• The character “–” indicates that the audit option is not set.
• The character “S” indicates that the audit option is set, BY SESSION.
• The character “A” indicates that the audit option is set, BY ACCESS.
• In the example shown in this slide, the following audit options are in effect for the SCOTT.EMP table:
• BY SESSION for successful and unsuccessful DELETE, INSERT and UPDATE statements
• BY ACCESS for successful SELECT statements; no auditing is done for unsuccessful SELECT statements.

Instructor's Notes

(If applicable)

• The DBA_AUDIT_OBJECT dictionary view records what activity has occurred on an object that has been configured for auditing.
• Let's dissect this example (see slide); the 1st and 3rd rows are 'BY ACCESS" rows. The show that at 3:32 PM and 3:34 PM, users DAVE and GARY executed a SELECT (see the ACTION_NAME column) against the SCOTT.EMP table. There will be one row for each time a user SELECTs from the SCOTT.EMP table.
• However, when the ACTION_NAME column contains 'SESSION_REC', this indicates a 'BY SESSION' audit. The time contains ______. The offset of the 'S' or 'F' in the SES_ACTIONS column indicates type of activity performed by the user. 'F' is failure, 'S' is successful, and B indicates that the user was both unsuccessful and successful in the same session (perhaps the DBA granted privileges to the user).
• Turn to the next page for the meaning of each position (offset) within the SES_ACTIONS column.


• Notes for this slide continue on next page…

Instructor's Notes

(If applicable)

• Notes continued from the previous page:
• The offset / meaning are as follows:
• Offset Option
• 1 ALTER
• 2 AUDIT
• 3 COMMENT
• 4 DELETE
• 5 GRANT
• 6 INDEX
• 7 INSERT
• 8 LOCK
• 9 RENAME
• 10 SELECT
• 11 UPDATE


• So, we can see that user ‘DAVE’ attempted an INSERT but was unsuccessful; user ‘GARY’ was unsuccessful with DELETE, but successful with UPDATE.
• See supplied script AUD_OBJECTS.SQL for a copy of the script shown in this slide.

Instructor's Notes

(If applicable)

• When collecting audit records to the database (AUDIT_TRAIL=DB), make sure the SYS.AUD$ table has plenty of free space (you will learn how to monitor free space in the Tablespaces lesson; also see the supplied script FREE_SPACE.SQL for assistance). If the audit table fills up, SQL operations will fail and users will not be able to connect. Only SYSDBA connections are permitted.
• So, periodically delete rows from the SYS.AUD$ table. Consider exporting or copying to a history table first.
• Here is a helpful metalink document on this subject: Note:73408.1 How to Truncate, Delete, or Purge Records from Audit Trail
• Warning: Oracle Metalink states that “SYS.AUD$ is the only SYS object that should ever be directly modified” See Metalink Note:73408.1.

Instructor's Notes

(If applicable)

• Triggers provide the ability to record the changed values. However, keep in mind that the row trigger will slow down the DML operation. Be sure to test that it performs adequately in your environment.
• The trigger shown here will capture all actions on the EMP table. Then you can view information from the AUDIT_HISTORY_EMP: