Database Auditing
Database Auditing
Databases are the proverbial keys to the kingdom for the majority of organizations. The hold most of the Intellectual Property that has value and are the end goal of many attacks. In the past, the database was commonly protected inside the organizational firewall. Today in a web 2.0 world, the database is there for all to access any time they wish. This includes attackers and others who should not have access.
Database systems are both the most overlooked and the most crucial areas in need of securing. Most of the reason for both security and compliance comes down to information stored on databases and in many instances all the critical information held by a company will be found on its database. This is not to state that other systems are not important, but that databases though often overlooked form they keystone of our information systems. A basic knowledge of SQL is assumed throughout this section. In any event it is important to have someone involved with a security review who understands and knows how the database system is configured.
At the same time as we are opening database access, we are doing less to protect them than ever. There is light at the end of the tunnel. NIST, DASA and the Centre for Internet Security have detailed guidelines for securing these database systems.
An Introduction to SQL
Most modern databases provide access to the data using a language called Structured Query Language or SQL. A reviewer involved with the review of a database should be familiar with SQL.
Structured Query Language (SQL) is an ANSI standard that permits users to access and manipulate databases. SQL statements can retrieve and update data in a database, as well as modify the structure of a database. The basics of SQL include a Data Manipulation Language (DML) and Data Definition Language (DDL).
DML includes SELECT, UPDATE, DELETE and INSERT INTO statements.
DDL includes CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX and DROP INDEX statements.
These statements are semantically simple.
· CREATE TABLE creates a database table
· ALTER TABLE alters a database table
· DROP TABLE deletes a database table.
Indexes may be created or dropped using the CREATE INDEX and DROP INDEX statements.
UNION ALL SELECT
This SQL Statement can return data from different tables. An attacker will use this to access information contained within tables that they should not be able to access.
INSERT INTO
Adding this clause to the end adds additional data to a table of your choice. For example, you might add a record to the table that controls authentication therefore adding another username and password that you now have knowledge of to access the database.
JOIN
JOIN allows provides the ability to select data from more than one table. Usually data is related to between tables through the use of a primary or unique key. The tables are joined through a WHERE clause condition.
UNION
The UNION command adds the ability to extract data from two tables; unlike JOIN, it provides the ability to simply “stack” the two result sets on top of each other. The fields do usually need to be of the same data type.
Key Database terms
It is essential that an reviewer understand the following terms associated with databases:
Database
A database is a grouping of files where actual data and database parameters are stored. Each Database can be connected to by one or more independent instances.
Data type
Every field has a data type. There are diverse numeric data types such as integer, double integer, decimal, as well as character and block data types.
Field
A field is a data structure for a single piece of data. (e.g. “first_name”, “last_name” and “phone_number” are all fields). Each database column is a field.
Instance
An instance is a set of memory and processes that make up an active part of a functional database. An Instance includes the memory buffers (working storage) and background processes. (Oracle uniquely identifies each instance using a SID [System Identification]).
Joins
Tables can be connected to other tables that have additional information. Usually tables are connected through some kind of key such as a primary or foreign key.
Primary Key
The primary key is a field that uniquely identifies each record.
Record
A record is a group of fields that are relevant to a particular topic.
Stored Procedures
A set of SQL statements can be grouped together in one file (program) with an assigned name. This set of statements is then stored in the database in a compiled form so that it can be shared by a multiple programs.
Table
Organized group of fields used to store information.
View
A view is a way of presenting the data in a database.
Database Security
Database security is about both the specifics of the database itself and also the system and network it is run on, there are some general areas that you may also want to address when testing a database. These areas include:
· Policies and procedures
· Patches
· Operating system security
· Setup files
· Service privileges
· Physical security
· Change Control
· Disaster Recovery
· Separation and restriction of production, test and development environments
· Scripts, jobs or batch files
· The storage of usernames and passwords in an unencrypted format
· Application Patch level
· User and Role Rights
· Configuration Parameters
It is also necessary to check that processes and control are in place to restrict the use of default and simple passwords.
Principles for Developing a Database Review Strategy
When testing databases in order to ensure that they are secure; test generally, then specifically. In any security reviewing involving a database, start with gathering the system configuration. Some of the key checks include:
· Protect the Audit Trail – Has the organization protected the audit trail so that audit information cannot be added, changed, or deleted without being recorded and logged?
· Audit Normal Database Activity - The process of gathering historical information about particular database activities that may be reviewed as a baseline. Knowing the baseline provides a starting point to find changes that are out of the ordinary.
· Audit only pertinent actions – In order to avoid cluttering the meaningful information with useless audit information, audit only the targeted database activities. It is all too easy to let the scope of an engagement grow. Due to time and cost limitations, this will only hurt the process in the long run and leave the organization less secure.
· 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. Maintaining a historical trail is useful and will help future reviews.
Check Triggers
Database triggers are procedural code that is automatically executed in reaction to selected events on a particular table, row or field in a database. A security test of a database should check that these are used and where. Triggers need to be set to fire when events that are defined in policy occur.
System triggers
System triggers allow the activation of controls that start when system events take place. These events can include:
· The start- up and shutdown of the database,
· Logon and logoff from users,
· Privileged access, and
· The creation, altering and dropping of schema objects.
Using autonomous transactions also allows a log to be written for the above system events. Any comprehensive review of a database security should check what (if any) systems triggers exist and ensure that these are aligned with the policy of the organization. An example trigger would be sending an alert if a user with administrative access has been added to the database.
Update, delete, and insert triggers
Defense in depth requires an understanding of the users' actions at multiple levels. This is not just access to the database, but access at the detailed row level for selected events and where there is sensitive data. Database triggers need to be written to capture changes at the column and row level.
Where data is extremely sensitive and any and all changes must be recorded, the database can be configured to write entire rows of data detailing a change to the data (who, what, where and why). This can be done both ahead of and subsequent to the modification of data being made with a write of information to a log table in the database and to an alternate location. This class of logging is extremely resource intensive. It requires that at least as many extra records are written and stored as the planned change (and at times more).
The one flaw in this technique is an inability to capture read access to a file using normal database triggers.
Fine-grained audit and review
Fine-grained audit is also commonly based on internal triggers that react when selected SQL code is parsed. This approach allows the reviewer to perform access reviews to the row and column level – not only for changes –but as well for read statements.
System logs
Databases generate numerous log files. Many of them providing useful information that can assist in an audit or review of the database. The alert log (for instance) can be used to provide evidence of database start-up and shutdown events. More crucially it will provide details of structural changes (such as adding a data file to the database or changes to the schema).
Validate database access
Check to find out who has access the database (and even what tables, rows and fields that they have access to). Checking access requires that the audit verify access location and time (where and when). Logon failures should also be checked with seemingly legitimate access at out of the ordinary or anomalous times (such as access to a local payroll system at 3am on a Sunday morning).
Auditing changes to the database structure
Production databases should NEVER allow ANY user to alter the schema structure. Changes should only be done (such as for upgrades) at definite times (that are logged and approved through change control). All other changes should be regarded as suspicious. Any privileges allowing this must be reviewed carefully. An examination of the database logs for evidence of structural changes can uncover evidence of invalid or unauthorized use of the database.
Monitor any use of system privileges
It is one thing to check the configuration of a database; it is another all together to validate that access has been the same as a configuration file over time, or indeed if the database is reacting as it should. Logging to a separate system is critical for this reason. If the DBA and system administration function lie with the same person, it is possible to remove evidence of changes to the system.
Separate logs provide the capacity to check if either an attacker or a rogue DBA has made any authorized changes to the database.
Log and record data changes to objects
These requirements are very application and installation specific. This is where the security tester needs to know what they are doing and why. This type of review needs to be purposeful and objective. It is easy to exceed the scope of an object access audit and in this event it is also possible for the tester to breach the law themselves (for instance in gaining an unauthorized view of health information).
Failed log-on attempts
Check for attempts to gain unauthorized access the database (and ensure the logs are available).
Attempts to access the database with non-existent users
This could be an attempt to bypass the controls in place over the system.
Attempts to access the database at unusual hours
Check for any attempts to access the database outside of working hours in environments where this is feasible. Otherwise, validation of access patterns over time may be completed using a baseline.
Check for users sharing database accounts
Non-repudiation hinges on not sharing accounts and access. Shared accounts are the anathema of a secure system and there is no compliance regime that allows this practice.
Multiple access attempts for different users from the same terminal
Check if multiple database accounts have been used from the same terminal. This can indicate compromised access or shared access.
Views
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.
Remember, a view may be bypassed by some users or if the database is accessed in an unexpected manner. Always consider how appropriate the use of a view is and never trust this as being the sole source of a database security.
Integrity Controls
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
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:
· Select. This gives the user the capability to query the object.
· Insert. This gives the user the capability to insert records into the table or view.
· Update. This updates records in the table or view.
· Delete. Delete enables the user to delete records from a table or view.
· Alter. This allows the user to alter the table.
· Index. This allows the user to create indexes on a table.
· References. This enables the user to create foreign keys that reference the table.
· Execute. The execute privilege allows a user to execute a procedure, package or function.
User defined procedures
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.
Encryption
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.
Client Service Security and Databases
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:
1. Backup facilities. Backup facilities provide periodic backups or images of either the entire database or selected portions thereof,
2. Journaling facilities. Journaling facilities maintain an order trail or the transactions and database changes.
3. Checkpoint facilities. These provide the DBMS with a point in time control, designed to stop processing periodically, suspending and synchronizing all its files and journals and establishing a recovery point.
4. Recovery manager. A recovery manager allows the DBMS to restore the database to the correct functioning condition and restart processing transactions.
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:
· Atomic,
· Consistent,
· Isolated, and
· Durable.
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.
Automated database security solutions
To make certain that unauthorized access to the database is not occurring, the security reviewer has to audit user activity. User activity monitoring provides a level of assurance over the performance of the policies, procedures, and controls and helps the organization to discover any contravention of the controls that may have transpired.
Auditing and monitoring user activity is best achieved using continuous data auditing and reporting controls. 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 the reviewer 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 test. 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:
1. Creating an inventory of all database systems and usage classifications. This should include production and test data. It needs to be maintained and be up-to-date.
2. Classifying data risk within the database systems. Monitoring should be prioritized for low, medium, and high risk information.
3. Implementing access request processes that require data owners to authorize the "roles" (through Role Based Access) granted to accounts in the database.
4. Conducting an analysis of access authority. User accounts that have a higher degree of access or permissions should be under higher scrutiny. Any account for which access has been suspended should be monitored to ensure access is denied and attempts are identified.
5. Assessing 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.
6. Validating technical safeguards to ensure that they are in place and enforced with access controls having been set appropriately.
7. Auditing activity and access. It is necessary to monitor data changes and modifications to the database structure, permission and user changes, and data viewing activities. Where possible, use network based database activity monitoring appliances instead of native database audit trails.
8. Ensuring that processes are in place to archive, analyze, review, and report audit information. Reports to reviewers and IT managers must communicate relevant audit information, which can be analyzed 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.
Steps one to five above are most effectively performed by the reviewer 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:
1. Gathering information from the database transaction log. Databases maintain transaction logs in the course of normal operation. Non-trigger audit agents gather data modifications and other activity from threes sources directly.
2. Databases have inbuilt event notification systems. Non-trigger audit agents acquire supplementary records, including permission changes and data access that are used to record the events occurring within the database.
Data Access Auditing
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 reviewer’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 reviewer 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 reviewer, 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.
Tools
The tools used to audit databases range from CASE (Computer Aided Software Engineering) Tools through to the more familiar network and system test tools that are used in network and host security audits. 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 and Security Review software
Three popular database security review and audit solutions include:
· DB Audit (SoftTree Technologies),
· Audit DB (Lumigent Technologies), and
· DbProtect (Application Security).
DB Audit (http://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 (http://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 (http://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 (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 (http://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 reviewer’s capacity to analyze the structure. Reverse engineering a database will enable the reviewer 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 reviewer 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 reviewer 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 reviewer 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 reviewer does not have access to sensitive information but will know the defenses and protections associated with the database. This is useful as the reviewer 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 reviewer 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.
Vulnerability Assessment Tools
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.
Local security
The security of the database overall is only ever as good as the security of the system it resides on. Anyone with physical access to the host or administrative access to a system can compromise a database. At the least copying g the data is possible – stories of people who have purchased hard drives from organization that have not wiped the data on the drives and that have sold them using eBay only to have recovered data are a near daily occurrence.
No database can be considered compliant with any standard if the system it is running on is also not adequately secured. As such, always ensure that the host the database is running on is secured. Disable any unnecessary services and patch the system on a regular basis. By regular – this also means often.
Creating a Checklist
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.
CIS (The Center for Internet Security)
CIS provides a large number of Benchmarks for both the Operating Systems and also applications. CIS offers both Benchmarks and also a number of tools that may be used to validate a system. The site is: http://www.cisecurity.org. CIS currently has configuration benchmarks for the following database applications:
· MySQL
· Microsoft SQL Server 2005
· Microsoft SQL Server 2000
SANS
The SANS Institute has a wealth of information available that will aid in the creation of a checklist as well as many documents that detail how to run the various tools.
The SANS reading room (http://www.sans.org/reading_room/) has a number of papers that have been made freely available:
· GSNA Audit Gold Papers
· GSOC Oracle Gold Papers
· General Tools papers (http://www.sans.org/reading_room/whitepapers/tools/)
SANS Score (Security Consensus Operational Readiness Evaluation) is directly associated with CIS.
NSA, NIST and DISA
The US Government (through the NSA, DISA and NIST) have a large number of security configuration guidance papers and benchmarks.
NIST runs the US “National Vulnerability Database” with the Microsoft SQL Security Checklist from DISA (http://iase.disa.mil/stigs/checklist) and a generic database checklist.
Summary
Database security consists of a number of key categories, all of which need to be tested. These include:
· Server Security (the process of limiting the access to the database server).
· Database Connections (such as local access and remote network connectivity to the database using authentication and authorization).
· Table Access Control (Table access control is related to an access control list restricting access to the database tables).
0 Response to "Database Auditing"
Post a Comment