Database audit

Database auditing involves observing a database so as to be aware of the actions of database users. Database administrators and consultants often set up auditing for security purposes, for example, to ensure that those without the permission to access information do not access it.^[1]

Issues

The primary security concerns of the auditor when conducting a database audit includes authentication and authorization issues. The following general principles for developing an audit strategy, auditing suspicious database activity, and auditing normal database activity can guide the auditor throughout the audit.

General Principles for Developing an Audit Strategy:

• Evaluate your purpose for auditing – In order to have an appropriate auditing strategy and to avoid unnecessary auditing, you must have a clear understanding of the reasons for auditing.
• Audit knowledgeably – In order to prevent unnecessary audit information from cluttering the meaningful information, it is important to audit the minimum number of statements, users, or objects required to get the targeted information.

General Principles for Auditing Suspicious Database Activity:

• Audit generally, then specifically – Enable general audit options at first, then use more specific audit options. This will help the auditor gather the evidence required to make concrete conclusions regarding the origins of suspicious database activity.
• Protect the Audit Trail – Protect the audit trail so that audit information cannot be added, changed, or deleted without being audited.^[2]

General Principles for Auditing Normal Database Activity: Auditing normal database activity refers to the process of gathering historical information about particular database activities.

• Audit only pertinent actions – In order to avoid cluttering the meaningful information with useless audit information, audit only the targeted database activities.
• Archive audit records and purge the audit trail – After you have collected the required information, archive audit records that are of interest and purge the audit trail of this information.

Modes of auditing

Automated auditing

In order to ensure that unauthorized users have not accessed the database, the auditor will need to audit user activity. Auditing user activity provides the auditor with assurance that the policies, procedures, and safeguards that management has enacted are working as intended (Mazer). This also helps the auditor to identify any violations that may have occurred.

Auditing user activity can be accomplished via continuous data auditing. Continuous data auditing is the process of monitoring, recording, analyzing, and reporting database activity on a periodic basis. This is a critical concept because unauthorized access to the database and the information contained within can occur at any time. If the auditor is using a testing schedule, violators can easily sidestep that schedule. This is not the case, however, with continuous data auditing. The auditor and management must be able to identify which behavior is suspicious versus which behavior is routine. Any behavior that is not identified as routine and valid access to the database must be examined and analyzed further.

Before beginning the audit, the auditor should access the database environment. This includes identifying and prioritizing the users, data, activities, and applications to be monitored (Mazer). The Internal Audit Association lists the following as key components of a database audit:

• Create an inventory of all database systems and use classifications. This should include production and test data. Keep it up-to-date.
• Classify data risk within the database systems. Monitoring should be prioritized for high, medium, and low risk data.
• Implement an access request process that required database owners to authorize the "roles" granted to database accounts. (roles as in Role Based Access not native database roles).
• Analyse access authority. Users with higher degrees of access permission should be under higher scrutiny, and any account for which access has been suspended should be monitored to ensure access is denied and attempts are identified.
• Access application coverage. Determine what applications have built-in controls, and prioritize database auditing accordingly. All privileged user access must have audit priority. Legacy and custom applications are the next highest priority to consider, followed by the packaged applications.
• Ensure technical safeguards. Make sure access controls are set properly.
• Audit activity. Monitor data changes and modifications to the database structure, permission and user changes, and data viewing activities. Consider using network based database activity monitoring appliances instead of native database audit trails.
• Archive, analyse, review, and report audit information. Reports to auditors and IT managers must communicate relevant audit information, which can be analysed and reviewed to determine if corrective action is required. Organizations that must retain audit data for long-term use should archive this information with the ability to retrieve relevant data when needed.

The first five steps listed are to be performed by the auditor manually, while the last two steps are best achieved by using an automated solution.

The ideal approach to effectively capture and analyze database activity, is through non-trigger audit agents associated with each database server. Non-trigger audit agents capture all relevant activity, regardless of the application used (Mazer). In comparison, database triggers-an automatic procedure that occurs when data has been altered in a table-are not recommended, as database administrators can easily disable them. The non-trigger database audit agents gather information through three means:

1. database transaction log – Each database maintains a database transaction log through the normal course of its operation, which gathers data modifications and other activity. This approach is not practical however as these logs are used for forward recovery and their formats are largely undocumented. Additionally, SQL SELECT access to database objects is not logged.
2. the database's built-in native logging– Obtains additional information, such as permission changes and data viewing activities. Each database management system has some type of audit trace capability such as Oracle's Fine Grain Auditing (FGA) capability.[1]
3. third-party tools that monitor network and/or system activity looking for database access. Some solutions use agents which enable both local access as well as network access to be monitored; while others are restricted to just monitoring network traffic. These solutions are typically called database activity monitoring.

References

KK Mookhey (2005). IT Audit. Vol. 8. Auditing MS SQL Server Security.
IT Audit. Vol. 8 Murray Mazer. Database Auditing-Essential Business Practice for Today’s Risk Management May 19, 2005.

• Oracle. 1999. Version 8.0. Auditing Database Use. May 9, 2005.^{[broken citation]}
• Soft Tree Technologies. DB Audit. May 2005. Soft Tree Technologies. May 17, 2005.

Source

Gallegos, F. C. Gonzales, D. Manson, and S. Senft. Information Technology Control and Audit. Second Edition. Boca Raton, FL: CRC Press LLC, 2000.
Ron Ben-Natan, IBM Gold Consultant and Guardium CTO. Implementing Database Security and Auditing. Digital Press, 2005.

Other works

• AuditDatabase 2006. Free web application that automates the generation of audit triggers.

Footnotes

1. ^ Mullins, Craig (2002). Database administration: the complete guide to practices and procedures. Addison-Wesley. p. 703. ISBN 9780201741292. http://books.google.com/books?id=KVHUbiSMYjAC. Retrieved January 19, 2011. "Audit trails help promote data integrity by enabling the detection of security breaches [...]." 
2. ^ The IT regulatory and standards compliance handbook. Syngress. 2008. http://books.google.com/books?id=RizmpbOHvf4C&pg=PA373&dq=%22Database+audit%22&hl=en&ei=lWQ3TY6JNYiq8AaEoN3CAw&sa=X&oi=book_result&ct=result&resnum=8&ved=0CFEQ6AEwBzgU#v=onepage&q=%22Database%20audit%22&f=false. Retrieved January 19, 2011.