How to individually audit SQL statements?

How to audit the SQL statements individually?

Consider that an organization uses the SQL server to store customer details. The number of statements that are recorded in the database can be quite high. Though it is impossible to check every single statement in the server while auditing, it is essential to identify similar statements and group them, so that they can be audited.

SQL statement auditing is an auditing technique where related groups of SQL statements are selectively audited for a particular type of database structure or schema object. The structure or schema object of the database is not specifically mentioned.

The following categories of SQL statements can be audited.

• DDL statements: For instance, to audit all the CREATE and DROP TABLE statements, you can use the command AUDIT TABLE.
• DML statements: For instance, to audit all the SELECT ... FROM TABLE/VIEW statements regardless of the table or view, you can use the command AUDIT SELECT TABLE.

You can decide whether the statement auditing needs to be broad or focused, meaning you can either audit all the activities of all the users or select a list of users from the database.

How to configure the SQL statement auditing

The primary requirement to configure the SQL statement auditing is to have the AUDIT SYSTEM, privilege. Usually, only the security administrators will be granted this privilege. You can then use the AUDIT statement to configure the SQL statement auditing.

The following example shows how to audit the SELECT TABLE statement.

The following example shows how you can audit all the unsuccessful SELECT, INSERT, and DELETE statements on all the tables by all users, and by individual audited statements.

Guidelines to audit the SQL statements

• Auditing all SQL statements for individual users.

  The ALL STATEMENTS clause can be used to audit the top-level SQL statements. The ALL STATEMENTS behaves differently from the rest of the statement audit options. It does not audit an SQL statement that has been issued from inside a PL/SQL procedure. However, this audit option does not affect any other AUDIT options that already exist.

  For instance, to audit all the unsuccessful statements issued by users wrooney and amartial, enter the following:

  AUDIT ALL STATEMENTS BY wrooney, amartial BY ACCESS WHENEVER UNSUCCESSFUL;

• Auditing all the SQL statement shortcut activities performed by individual users.

  The ALL clause can be used to audit all the statement shortcut activities performed by an individual user.

  For instance,

  AUDIT ALL BY wrooney BY ACCESS;

• Auditing all SQL statements for the current session, irrespective of the user.

  In the audit ALL STATEMENTS option, you can use the IN SESSION CURRENT clause to audit top-level SQL statements during the lifetime of the user session. You must note that this option cannot be used for a specific user. Though the NO AUDIT statement cannot be used to cancel it, the auditing lasts only till the user session exists. When the user session ends, the auditing ends as well.

  For instance, to audit all successful statements in any current user session:

  AUDIT ALL STATEMENTS IN SESSION CURRENT BY ACCESS WHENEVER SUCCESSFUL;

  The AUDIT ALL STATEMENTS option can be used with the IN SESSION CURRENT clause in a database logon trigger as well. The SYS_CONTEXT function can be used to configure this auditing only under certain conditions such as a user login time between 7:00 pm to 8:00 am. This helps you to record the SQL statements performed by users who log into the database in the non-working hours.

  This type of auditing can be used to increase the collection of audit activities when there is a suspected unsecured connection or an internal threat. For instance, by using a database logon trigger, you can query contents of the connection context using the SYS_CONTEXT function.

  This logon trigger functionality can establish that this connection should be audited extensively. The following SQL command needs to be issued.

  AUDIT ALL STATEMENTS IN SESSION CURRENT;

  The audit remains in effect until the session ends.

• Auditing logon and logoff connections and disconnections.

  An individual audit record is generated for every logon logoff event using the AUDIT SESSION statement. This enables you to audit all successful and unsuccessful connections to and disconnections from the database, irrespective of the user. For instance,

  AUDIT SESSION BY ACCESS;

  This option can be set for individual users as well.

  AUDIT SESSION BY wrooney, amartial BY ACCESS;

• Auditing statements that fail because an object does not exist.

  When the target object doesn't exist, the NOT EXISTS option of the AUDIT statement specifies the auditing of all SQL statements that fail.

  For instance:

  AUDIT NOT EXISTS;

  The NO AUDIT option can be used to disable SQL statement auditing. However, privileges will still be audited. You must have the AUDIT SYSTEM system privilege to remove the SQL statement auditing. It is important to note that if you have configured the AUDIT ALL STATEMENTS option, then NOAUDIT AUDIT STATEMENTS statements doesn't affect the other options.

  f you have included the IN SESSION CURRENT clause in the AUDIT statement, it cannot be removed using the NOAUDIT statement. The following example shows how to use the NOAUDIT statement.

  NOAUDIT session;
NOAUDIT session BY daniel, scott;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE
PROCEDURE;