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.
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.
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.
AUDIT SELECT TABLE BY ACCESS;
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.
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE
WHENEVER NOT SUCCESSFUL;
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;
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;
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.
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;
When the target object doesn't exist, the NOT EXISTS option of the AUDIT statement specifies the auditing of all SQL statements that fail.
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 BY daniel, scott;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE
Manage logs, comply with IT regulations, and mitigate security threats.
Our support technicians will get back to you at the earliest.
Zoho Corporation Pvt. Ltd. All rights reserved.