Wednesday, 17 August 2011

More on Databases

Data access auditing is a surveillance control. By monitoring access to all sensitive information contained within the database, suspicious activity can be brought to the auditor’s awareness. Databases commonly structure data as tables containing columns (think of a spreadsheet, only more complex). Data access auditing should address six questions:

1. Who accessed the data?
2. When was the data accessed?
3. How was the data accessed? (This is what computer program or client software was used?)
4. Where was the data accessed from (this is the location on the network or Internet)
5. Which SQL query was used to access the data?
6. Was it the attempt to access data successful? (And if yes, how much data was retrieved?)
The evidence available to the auditor is provided:

  • Within the client system (this may be infeasible – such as in web based commerce systems),
  • Within the database (including the logs produced by the database that are sent to a remote system), or
  • Between the client and the database (such as firewall logs, IDS/IPS devices and host based events and logs). 

Auditing within the client entails using the evidence available on the client itself. Client systems can hold a wealth of database access tools and the logs that these create. These logs may contain lists of end-user activity that a user has performed on the database. In respect of web based systems, the web server itself may be treated as a client of sorts.

To obtain an adequate audit trail from client systems alone, all data access must have occurred using client tools under the control of the organization conducting the audit. In the event that data access can transpire using other means, it is rare that sufficient evidence will be available. This option by itself is the entirely worst option available to the auditor, but it can provide additional evidence in support of the other methods. This is chiefly used in the event of a forensic investigation.

Auditing within the database is often problematic due to:
  • A limited audit functionality of many database management systems (DBMS),
  • Inconsistent DBMS configurations and types being deployed throughout an organization, and
  • Performance losses due to enabling the audit mechanisms
Auditing within the database is without doubt better than auditing within the client, however, the best approach is a combination of auditing the client, network and the database.

Auditing between the client and the database entails monitoring the communication between the client and the database. This involves capturing and interpreting the traffic between the client and the database. Software is available for this and it may be used to provide data access auditing. The biggest issues with this type of data access auditing are:

  • Encryption between the client and the database server,
  • Privacy considerations and rights to view data, and
  • Correlating large volumes of data that also need to be parsed and processed to be useful. 

SQL Injection
SQL injection is covered in more detail in the chapter on web exploits. SQL Injection has three primary goals:
1. Accessing information,
2. Destroying data, and
3. Modifying data.

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:
“SELECT * FROM users WHERE username = ‘administrator’ and password = ‘password’
An attacker could attempt to add ‘OR ‘’ = ‘ changing the SQL statement to:
“SELECT * FROM users WHERE username = ‘administrator’ and password = ‘password‘OR ‘’ = ‘
This could potentially allow the attacker to bypass the database authentication.

The tools used to audit databases range from CASE (Computer Aided Software Engineering) Tools through to the more familiar network and system test tools covered throughout the book. In addition to the database itself, it is important to test:
1 File system controls and permission,
2 Service initialization files,
3 The connection to the database (such as access rights and encryption

Specialized Audit software
Three popular database auditing solutions include:
  •  DB Audit (SoftTree Technologies),
  •  Audit DB (Lumigent Technologies), and
  •  DbProtect (Application Security).
DB Audit ( 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 ( 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 ( 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 (Computer Aided Software Engineering) Tools

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 (

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:
  • · The indexes,
  • · Fields,
  • · Relationships,
  • · Sub-categories,
  • · Views,
  • · Connections,
  • · Primary keys and alternate keys,
  • · Triggers,
  • · Constraints,
  • · Procedures and functions,
  • · Rules,
  • · Table space and storage details associated with the database,
  • · Sequences used and finally the entities within the database.
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. 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.
Fig 1 Display database schema.
When 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:

1. Schemas restrict the views of the database for users,
2. Domains, assertions, checks and other integrity controls defined as database objects which may be enforced using the DBMS in the process of database queries and updates,
3. Authorization rules. These are rules which identify the users and roles associated with the database and may be used to restrict the actions that a user can take against any of the database features such as tables or individual fields,
4. Authentication schemes. These are schemes which can be used to identify users attempting to gain access to the database or individual features within the database.
5. User defined procedures which may define constraints or limitations on the use of the database,
6. Encryption processes. Many compliance regimes call for the encryption of selected data on the database. Most modern databases include encryption processes that can be used to ensure that the data is protected.
7. Other features such as backup, check point capabilities and journaling help to ensure recovery processes for the database. These controls aid in database availability and integrity, two of the three legs of security.
CASE tools also contain other functions that are useful when auditing a database. One function that is extremely useful is model comparison.

Fig 2 Reverse Engineer existing databases into presentation quality diagrams in minutes.

Case tools allow the auditor to:
· Present clear data models at various levels of detail using visual objects, colors and embedded diagrams to organize database schemas,
· Synchronize models with the database,
· Compare a baseline model to the actual database (or to another model),

Case tools can generate code automatically and also store this for review and baselining. This includes:
· DDL Code to build and change the database structure
· Triggers and Stored Procedures to safeguard data integrity
· Views and Queries to extract data

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.

No comments: