Audit generally, then specifically. When reviewing a database, you should start to gather the evidence required as to the system config. Some of the key checks include:

A view is a subset of a database that is presented to one or more users. A view is created through the querying of one or more database tables, producing a dynamic result table for the user at the time of the request. As a result a view is always based on the current data in the base tables. The main advantage of a view is they may be built to present only certain data. They can be used to restrict the columns and rows that are presented to the user rather than the full table. This prevents the user from viewing other data in the table that may be considered confidential.

Views may be granted to a user without giving the user access to the base tables. Consequently the user cannot directly access the table and find out the other information that they contain. Even in large databases it is essential to take a sample of various views in the database and ensure that the select statements that are used to create the view do not call excessive data. Unfortunately this is a business derived process and cannot be simply integrated into tools. The analysis of views is complex because it is derived from business rules. And as business rules will vary between organizations, and even between departments within an organization, it is not possible for a single tool to automatically check all possible view states.

Integrity controls aid by protecting data from unauthorized use and update. CASE tools can be used to take samples of the integrity controls used across a database and ensure that these match the business requirements. Integrity controls can be used to limit the values a field may hold and also the actions that may be performed on the data. They may also trigger the execution of other procedures. For instance, integrity controls may be used to place an entry into a log to record access to tables. In this way user access may be recorded. It is possible to record information across different tables.

One way of monitoring changes to a database even from the administrative staff would be to have tables with restricted access. These tables could be mirrored on another database and accessible only by security and audit staff. An example of this would be to record all changes made by the database administrator to such a table and have them as a record for posterity. One form of integrity control is a domain. A domain is a method of creating a user defined data type.

When a domain is defined any field may be assigned to that domain as its data type. An advantage of a domain is that if it ever changes it can be changed in one place, the domain definition, and all fields within this domain will be changed automatically. Next, a single check clause may be used within a constraint on various fields. If the limits of the check were to change, a DVA would have to find every instance of the integrity control and change it in place separately. A check would enable this to occur or be logged or have other controls automatically.

Assertions are constraints that enforce certain database conditions. Assertions are checked automatically by the DBMS when transactions are run that can involve tables or fields where assertions exist. Assertions are often extremely complex and involve detailed investigations against business rules. Unfortunately it is generally not possible to use tools to check assertions.

Next, database triggers are also effective in adding security controls to a database. A trigger can include an event, condition and action. Triggers may be more complex than an assertion but will allow the database to automatically prohibit inappropriate actions, automatically start handling procedures using stored procedures or other processes or write a row to a log file. This may be used to reflect information about the user and transaction that has been created. This log may then be displayed in a format that can be read by humans or using automated procedures and tools. Like any stored procedure domains and triggers can be used to enforce controls for all users and all database activities.

These controls do not have to be coded into each query or program. This makes it difficult for individual users or even malicious code to circumvent controls around the database. Even with assertions, triggers and stored procedures on a database other forms of integrity control are necessary. It is still not possible to stop all malicious or unauthorized access to a database. As such a change audit process is still necessary. To do this, all user activity should be logged and monitored. The reason for this is to check that all policies and constraints are being enforced across the database.

The difficulty in this method is that every database query and transaction needs to be logged to record the characteristics of all data use. It is essential that all modifications to the database include who accessed the data, the time the data was accessed and if a program or query was used to run this, what that query or program was. It is also essential to log the network address or location where the request was generated from. There are also other parameters depending on the business and database structure that may be used to aid an investigation of a suspicious data change. The problem with this sort of structure is that it creates extra tables, extra maintenance.

This additional cost often puts people off this. However the savings in the long run and the increased ease at which databases may be verified can make it worthwhile.

Authorization rules are controls which are incorporated into the data management systems to restrict access to data and may also restrict the actions taken by the users when they are accessing the data. For instance, an authorization rule could be used to restrict a user with a particular user name and password to read any record in the database but not to modify those records.

In Oracle the privileges are:

Some database management systems include user exits or interfaces that will allow system designers or users to create their own user defined procedures for security. Many web systems include user defined procedures to validate users who may have forgotten their password. One such method is to ask a series of questions about the user that only the user should know. These are things such as user's first pet, high school, mother's maiden name and other such information.

Data encryption is one of the many features that are necessary to protect information and may be necessary for many compliance requirements. Most modern databases including Oracle, Microsoft SQL and MySQL include procedures for the encryption and decryption of data. In addition to this, most databases include functions for hashing data.

Hashing and encryption are similar and related but is not the same thing. Hashing is a one way function that takes data and provides a cryptographic fingerprint of the data that cannot be reversed and uniquely identifies the information to the fingerprint. Encryption is reversible. The use of a key will either lock or unlock the data, protecting it from prying eyes.

Databases are generally run in a distributed environment. In the past databases were configured on mainframes. Mainframe mentality still permeates the database world but unfortunately the controls associated with mainframes have long passed. Networks are often not secure and the database administrator cannot control all aspects of the path from a client to the database. In particular, many modern applications involve users at remote destinations, even on the other side of the world. Database security is a combination of system security, the security of the database itself, web security and the security of the network between the client and the server. As a consequence database security is not just about the aspects of the database itself covered in this chapter. It must also involve aspects of security concerning the network, routers, firewalls and systems that the database is involved with.

One of the key tenements of security is availability. To ensure the availability of a database it is important to maintain backup and recovery processes. Database recovery involves including mechanisms to restore the database quickly and accurately after loss or damage. This ensures both availability in the case of an outage and more importantly data integrity. The basic recovery facilities for a database management system should include the four basic facilities for backup and recovery of any database. These are:

The goal of maintaining database transaction integrity is to ensure that no unauthorized changes occur either through user interaction or system error. In general process following well accepted properties is called the ACID principle.

The ACID principle stands for:

This means that the individual transactions cannot be subdivided, hence atomic. A process must be included in its entirety or not at all. Next it needs to be consistent. This means that any database constraints must be true. Before the transaction must also be true post the transaction. Next transaction should be isolated. This means that changes to the database are not revealed to users until the transaction is committed to the database. And finally transactions need to be durable. Durable transactions means the change has to be permanent. Once a transaction is committed no subsequent failure of the database will end up in reversing the effect of the transaction. This is important in case of failures where transactions may be lost.

To make certain that unauthorized access to the database is not occurring, the auditor has to audit user activity. User activity audits grant a level of assurance over the performance of the policies, procedures, and controls and help the organization to discover any contravention of the controls that may have transpired.

Auditing user activity is best achieved using continuous data auditing. Continuous data auditing is the practice of monitoring, recording, analyzing, and reporting database activity as changes and access takes place. This is becoming more critical. Unauthorized access to data can take place at any time. Scheduled audit can miss many violations and generally at best samples access. This is a check of only a small fraction of all system accesses.

Continuous data auditing does not work in this way. An organization wanting to use continuous audit techniques needs to have auditors and management work in concert to predefine both suspicious and routine behavior. Where an action occurs that has not been classified to be a routine access, a resultant access to the database would then have to be examined and analyzed further – the result being an addition to either the list of suspicious or routine behavior. Where an unclassified access does not gain access to data, this is an automatic suspicious action. It either points to an illicit attempt to access data or an error in the system or processes. Either is a cause for concern.

The database environment should be evaluated prior to the start of the audit. This involves the identification and prioritization of the users, data, applications, and activities to be validated. The Internal Audit Association (IIA) defines the key components of a database audit to include:

Steps one to five above are most effectively performed by the auditor manually. Re-performance can be completed using baselines. Steps seven and eight are most effectively achieved with the implementation of an automated solution.

The best approach to auditing database activity through the use of non-trigger audit agents connected to every database server. Non-trigger audit agents capture all significant actions that occur on the database, without concern as to what application is used. These differ from database triggers in that database administrators cannot disable non-trigger audit agents without setting off alarms and raising alerts that may tip off security administrators to these actions. Also, the disabling of a non-trigger audit agent is an event in itself. Triggers are automatic procedure that occurs when data has been altered in a table. Non-trigger database audit agents are uncommon at present. They work by:

SQL injection is covered in more detail in the chapter on web exploits. SQL Injection has three primary goals:

The goal of the attacker and the likelihood of each will vary dependant on the composition of the organization running the database. The most common form of SQL injection is through the addition of the SQL command, “OR 1=1” to an input field. The addition of this clause to the last part of a query may make the query true.

For example, with a query such as:

An attacker could attempt to add ‘OR ‘’ = ‘ changing the SQL statement to:

This could potentially allow the attacker to bypass the database authentication.

Three popular database auditing solutions include:

DB Audit (www.softtreetech.com/) is easy to tailor and does not require installation of any additional software or services on the database server or network. It supports Oracle, Microsoft SQL Server, Sybase ASE, Sybase ASA and IBM DB2. It is implemented on the database back-end to reduce the risk of back door access that would be unrecorded.

Lumigent Audit DB (www.lumigent.com/) provides comprehensive monitoring and auditing of data access and modifications. It provides an audit trail of who has accessed or modified what data, and supports best auditing practices including segregation of duties. Audit DB supports IBM DB2, Microsoft SQL Server, Oracle and Sybase databases.

DbProtect by Application Security (www.appsecinc.com/products/dbprotect/) uses a network-based, vulnerability assessment scanner to test database applications. It also provides structured risk mitigation, and real-time intrusion monitoring, coupled with centralized management and reporting. DbProtect provides security and auditing capabilities for complex, diverse enterprise database environments.

Case tools can be a great aid to auditing database systems. CASE or Computer Assisted Software Engineering tools not only help in the development of software and database structures but can be used to reverse engineer existing databases and check them against a predefined schema. There are a variety of both open source and commercial CASE tools. In this chapter we'll be looking at Xcase (www.xcase.com/).

Many commercial databases can run into the gigabyte or terabyte in size. Standard command line SQL coding is unlikely to find all of the intricate relationships between these tables, stored procedures and other database functions. A CASE tool on the other hand can reverse engineer existing databases to produce diagrams that represent the database. These can first of all be compared with existing schema diagrams to ensure that the database matches the architecture that it is originally built from and to be able to quickly zoom in on selected areas.

Visual objects, colors and better diagrams may all be introduced to further enhance the auditor's capacity to analyze the structure. Reverse engineering a database will enable the auditor to find out the various structures that have been created within the database. Some of these include:

Each of the tables will also display detailed information concerning the structure of each of the fields that may be viewed at a single glance. In large databases a graphical view is probably the only method that will adequately determine if relationships between different tables and functions within a database actually meet the requirements (see Figure 15.1). It may be possible in smaller databases to determine the referential integrity constraints between different fields, but in a larger database containing thousands of tables, there is no way to do this in a simple manner using manual techniques.

When you are conducting an audit of a database for compliance purposes, it is not just security functions such as cross-site scripting and sequel injection that need to be considered. Relationships between various entities and the rights and associated privileges that are associated with various tables and roles also need to be considered. The CASE tools allow us to visualize the most important security features associated with a database. These are:

CASE tools also contain other functions that are useful when auditing a database. One function that is extremely useful is model comparison. Figure 15.2 is an example of reverse engineering databases into diagrams.

Case tools allow the auditor to:

Case tools can generate code automatically and also store this for review and baselining. This includes:

The auditor can also document the database design using multiple reporting options. This allows for the printing of diagrams and reports and the addition of comments to the reports and user defined attributes to the model.

Data management features allow the auditor to validate the data in the database being reviewed against the business rules and constraints defined in the model and generate detailed integrity reports. This can be extended further to access and edit the data relationally using automatic parent/child browsers and lookups and then to locate faulty data subsets using automatically generated SQL statements. These provide valuable sources of errors and help in database maintenance – making the audit all the more valuable.

Model comparison involves comparing the model of the database with the actual database on the system. This can be used to ensure change control or to ensure that no unauthorized changes have been made for other purposes. To do this, a baseline of the database structure will be taken at some point in time. At a later time the database could be reverse engineered to create another model and these two models could be compared. Any differences, variations or discrepancies between these would represent a change. Any changes should be authorized changes and if not, should be investigated. Many of the tools also have functions that provide detailed reports of all discrepancies.

Many modern databases run into the terabytes and contain tens of thousands of tables. A baseline and automated report of any differences, variations or discrepancies makes the job of auditing change on these databases much simpler. Triggers and stored procedures can be stored within the CASE tool itself. These can be used to safeguard data integrity. Selected areas within the database can be set up such as honeytoken styled fields or views that can be checked against a hash at different times to ensure that no-one has altered any of these areas of the database. Further in database tables it should not change. Tables of hashes may be maintained and validated using the offline model that has stored these hash functions already. Any variation would be reported in the discrepancy report.

Next the capability to create a complex ERD or Entity Relationship Diagram in itself adds value to the audit. Many organizations do not have a detailed structure of the database and these are grown organically over time with many of the original designers having left the organization. In this event it is not uncommon for the organization to have no idea about the various tables that they have on their own database.

Another benefit of CASE tools is their ability to migrate data. CASE tools have the ability to create detailed SQL statements and to replicate through reverse engineering the data structures. They can then migrate these data structures to a separate database. This is useful as the data can be copied to another system. That system may be used to interrogate tables without fear of damaging the data. In particular the data that has migrated to the tables does not need to be the actual data, meaning that the auditor does not have access to sensitive information but will know the defenses and protections associated with the database. This is useful as the auditor can then perform complex interrogations of the database that may result in damage to the database if it was running on the large system. This provides a capability for the auditor to validate the data in the database against the business rules and constraints that have been defined by the models and generate detailed integrity reports. This capability gives an organization advanced tools that will help them locate faulty data subsets through the use of automatically generated SQL statements.

Any database sits on top of another operating system. As such tools such as NMAP may be used to check for open ports on the database system and determine if there are other services running on the host. This is important as standards (such as PCI-DSS) call for the restriction of other services to the host allowing only those that are necessary. This means that the database has to be a bastion.

That is the system needs to be built for purpose and should not be shared with other applications. Next vulnerability and assessment tools ranging from Nessus through to commercial assessment tools such as CORE IMPACT may be used to check the database for a variety of vulnerabilities. Nessus for instance has a variety of plug-ins associated with Oracle, Microsoft SQL and My SQL databases. These plug ins allow Nessus to check for vulnerabilities associated with these particular database systems as well as also checking for application vulnerabilities and operating system vulnerabilities that may be associated with the system and may affect the database. Further, many of the database vendors also provide free tools. Microsoft SQL server comes with the SQL server analyzer. This product looks at the best practices for the SQL database and can analyze against these best practice statements.

It is essential that an auditor understand the following terms associated with databases:

The most important tool that you can have is an up-to-date checklist for your system. This checklist will help define your scope and the processes that you intend to check and validate. The first step in this process involves identifying a good source of information that can be aligned to your organization's needs. The integration of security check lists and organizational policies with a process of internal accreditation will lead to good security practices and hence effective corporate governance.

The first stage is to identify the objectives associated with the systems that you seek to audit. Once you're done this list of regulations and standards that the organization needs to adhere to may be collated. The secret is not to audit against each standard, but rather to create a series of controls that ensure you have a secure system. By creating a secure system you can virtually guarantee that you will comply with any regulatory framework.

The following sites offer a number of free checklists that are indispensable in the creation of your SQL database audit framework.

The following list is a quick introduction into some of the things you should be considering when creating a checklist for your SQL system. This is by no means comprehensive but may be used as a quick framework in association with the standards listed above. One of the first things to remember is that not all SQL is the same and what works or Oracle may not work on MySQL.