Best practices on auditing - Typical db activities,
suspicious db activities

Auditing database is an essential practice to spot malicious events and to keep track of all activities that happen in the database. The effectiveness of the database auditing depends on the way it is conducted. Therefore it is essential to follow certain best practices for conducting effective auditing. This article gives you some best practices of database auditing.

General guidelines for auditing the database activities.

• Audit only the relevant activities

  One of the major steps to obtain meaningful information is to ensure that only relevant activities are enabled for audit. By auditing only the targeted database activities, you can reduce the irrelevant logs getting generated and to reduce the effort to detect suspicious activities. You need to enable auditing for specific events taking into account your business context.

  For instance, if an employee of a hospital tries to access patient details, the system administrator must monitor and identify whether it was unauthorized. If yes, they must also identify what data was accessed. Merely knowing that the employee had used the SELECT privilege on data doesn't help. Hence, the administrator must use fine-grained auditing techniques which provide deeper insight.

• Archive the audit records, but not the trials

  Once an audit record is obtained, it is recommended to archive the records and purge the audit trials. Storing the audit trials need not increase the value of the information. Moreover, these supporting proof of activities may not be necessary in most cases.

  To archive audit records, you copy the relevant records to a database table, for instance, using INSERT INTO table SELECT ... FROM SYS.AUD$ ... for the standard audit trail. It is important to note that the fine-grained audit records are in the SYS.FGA_LOG$ table. Alternatively, you can also export the audit trail table to an operating system file.

  To exclude the audit trials, you can delete the standard audit records from the SYS.AUD$ table and fine-grained audit records from the SYS.FGA_LOG$ table. For instance, to delete all the audit records from the standard audit trail, enter the following statement:

  DELETE FROM SYS.AUD$;

• Abide the privacy policies of your organization

  Every organization will have to be compliant of the privacy regulations that have been imposed by the authorities, besides their privacy policies. Privacy laws require businesses to keep track of access to the personally identifiable information of their customers and employees, This can be seen as the foremost step in the auditing process. Every business should ensure the security of the data by monitoring the access to the data base constantly.

How to drill down suspicious activities by conducting database audit?

• Consider all the available information before narrowing down

  While auditing the database for suspicious activities, set the audit options more generally first. This will enable you to record and analyze the preliminary information. Once this is done, disable general auditing and focus on specific actions. You can use the fine-grained auditing technique for this. Continue this process until you identify the origin of the suspicious activity.

• Protect the audit trail

  Always protect the audit trails while auditing the database for suspicious activities. This will ensure that no audit information gets modified (added, changed, or deleted) without being audited.

  The standard audit trails can be audited using the AUDIT SQL statement.

  For instance,

  sqlplus sys as sysdba
Enter password: password
SQL> AUDIT SELECT ON SYS.AUD$ BY ACCESS;

Wouldn't it be convenient if you could obtain comprehensive reports on the database activities?

EventLog Analyzer, a comprehensive log management solution helps you with exhaustive reports on your database thereby making auditing hassle free.