Performing the Audit

Before actually conducting the audit, you will need a few basic tools. To start off with, you should have a checklist of the items you need to verify. You can create your own checklist, you can find checklists on the Internet, or you can even use the basic checklist we provide here.

Start off by meeting with and discussing the audit with the database administrator (DBA). Clearly, the DBA is not going to be excited about the idea of being audited. Therefore, do your best to approach the DBA in as friendly a way as possible. Make sure that the DBA understands that you are there to help, not hinder, his or her work.

Databases are very often 24/7 systems, meaning they are not allowed any down time. This will mean that you are going to get pushback on anything you want to do that could, with even the remotest possibility, affect database availability. The first time you as the auditor bring the database down, your job is going to be infinitely more difficult.

Be ready to optimize the time you will be accessing the system. Ensure that any account you are given on the system runs with only the permissions you need. Immediately after you are completed with any work, have the DBA lock the account. Don't delete the account-simply lock it until you are officially done with the audit. Then, if you do need to gather more information, the DBA simply will need to unlock the account rather than recreate it.

Perform as much work offline as possible. Ideally, you want to download the system tables, password hashes, files permissions, and all other information onto a local source. Then you can disconnect from the database and perform your audit steps offline with no risk of affecting the database. For instance, you want to ensure that you never do password strength testing on the database. The password hashes can be downloaded, and password strength testing can be done offline.

By you showing the DBA this level of caution with the database, he or she will, hopefully, give you the professional courtesy of letting you do your job. Being at odds with the DBA only results in an audit that provides little value to the organization.

Test Steps

Now that you are equipped with some background on databases, we need a plan for performing an audit. Many of the steps we will cover are almost identical to steps you would perform on an operating system or network audit, but they need to be placed in the context of the database. Some steps are simply unique to the database.

Review Database Permissions

Database permissions are slightly different from operating system privileges. Privileges are managed using the GRANT and REVOKE statements. For instance, the following SQL statement gives USER1 the permission to SELECT from the SALARY table:

GRANT SELECT ON SALARY TO USER1

The REVOKE statement is used to remove permissions that have been granted:

REVOKE SELECT ON SALARY FROM USER1

The GRANT statement can be used to selectively give permissions, such as SELECT, UPDATE, DELETE, or EXECUTE. This allows you to grant access to read the data in the table but limit the ability to modify the table. GRANT and REVOKE also can be used more selectively on a column-by-column basis.

1 Verify that database permissions are granted or revoked appropriately for the required level of authorization.

If database permissions are not restricted properly, access to critical data may occur. Database permissions also should be used to restrict people from using subsystems in the database that may be used to circumvent security. Security best practices dictate that permissions should be granted on a needs basis only. If permission is not specifically needed by an account, it should not be granted.

How

Talk with the database administrator, and determine which user accounts are required to have access to what data. There may be administrators who need access, accounts used by a web application to access the data, or even accounts used by batch jobs. Accounts that do not require permissions or access should be locked, disabled, or even removed.

2 Review database permissions granted to individuals instead of groups or roles.

Database best practices dictate that you should attempt to grant permissions to roles or groups, and those, in turn, should be granted to individuals. Use of roles or groups to allocate permissions reduces the chance of making administrative mistakes and allows for easier maintenance of security controls. When new permissions need to be granted, they can be granted to a single group rather than multiple accounts. In addition, when a user changes jobs, it is straightforward to revoke the role or group and grant new individuals the role or group.

How

Select the list of permissions from the database dictionary. Review for any permissions granted to an account or user. Check that privileges are granted to a role or group. The roles or groups then can be granted to individuals as needed.

You also will need to download the list of roles/groups and users/accounts to determine which are allowed to be granted. The lists of users and groups are stored in the data dictionary.

3 Ensure that database permissions are not implicitly granted incorrectly.

Database permissions can flow from many sources. For instance, ownership of an object grants implicit full control over that object in a database. Privileges such as SELECT ANY TABLE allow access to all data and can lead to unauthorized access to data. If you do not have a complete understanding of how database permissions are implicitly granted, permissions may be granted in a way that was not intended.

How

Review the specifics of the permission model for your database platform, and verify that you fully understand how permissions are inherited. Also review system privileges that allow access to data, such as SELECT ANY TABLE or granting privileged role to a user. Document permissions that are implicitly granted as well as explicitly to ensure that permissions are not allowed when they are not appropriate.

4 Review dynamic SQL executed in stored procedures.

Access to an object also can be gained by running stored procedures or functions. On Microsoft SQL Server, when executing code objects, access to any other objects owned by the stored procedure owner is allowed. On Oracle, running a stored procedure allows you to access objects as the stored procedure owner. This can be dangerous if stored procedures are not constructed properly and can be manipulated.

How

Review stored procedures specifically looking for issues such as SQL injection or any form of dynamic SQL. Restrict use of dynamic SQL in procedures that run with elevated privileges. In addition, ensure that you are logging any and all access to stored procedures that run under elevated privileges.

5 Ensure that row-level access to table data is implemented properly.

Relational databases are designed to GRANT permissions on a table or column. Unfortunately, they are not well designed to restrict access to a subset of rows in a table. When you grant a user SELECT on a table, he or she will have the ability to read every row in the table.

There are technologies to help manage this problem. For instance, Oracle has its virtual private databases (VPDs) that you can use to limit access to specific rows. You also can use views to programmatically restrict rows returned based on the user's context. A very common and practical approach is to use stored procedures to access tables. Using this strategy, the DBA does not need to grant permissions on the table, preventing the user from attempting to circumvent the stored procedure.

How

Discuss with the DBA the method of row-level access controls in the database. Ensure that a user cannot access data in a table that he or she is not authorized to view if he or she circumvents the application or stored procedure providing access. Access the database through a user's account to verify that the "effective" ability of the user is as intended.

6 Revoke PUBLIC permissions where not needed.

Many of the built-in stored procedures and functions in a database are granted to the group PUBLIC by default. Each database has a slightly different implementation of a PUBLIC group-generically, it represents everyone in the database. This means that permissions granted to PUBLIC apply to everyone.

This has led to many security issues in databases. Many of the built-in procedures may not appear dangerous and have no practical use for ordinary users. Security best practices dictate that you should restrict all access unless explicitly needed. If a procedure contains functionality that is not needed, it should not be granted to any users. This is especially important for permissions granted to PUBLIC.

Understand that there is a fine line here that you must try not to cross. If you revoke permissions that are needed, you may end up breaking necessary functionality. Blindly revoking all PUBLIC permissions is a recipe for disaster.

How

Start by gathering a list of all permissions, highlighting those granted to PUBLIC. Discuss with the DBA which procedures and features of the database are being used or may be used in the future. Then determine how much risk would be introduced by revoking permissions from objects that are clearly not needed. If everyone agrees to have the DBA permissions revoked, then it makes sense to revoke them. Always take a backup and provide an undo script that can be used to roll back any changes if you find out later that you need those permissions or something unexpectedly breaks.

Operating System Security

We are not going to go into depth on operating system security here because significant other sections of this book are dedicated to this topic. Start with the premise that a database not secured can be used to break into the operating system. Conversely, an operating system not secured can be used to break into the database. You can't have one without the other. Locking down one but not the other fails to provide proper security to either. Of course, realize that the database is ultimately what you ought to focus on because the database is the most "valuable" target in your network.

7 Restrict access to the operating system.

The best situation is to have the operating system dedicated to the database only. No users other than DBAs should have access to connect to the operating system from a Telnet or SSH shell, FTP, or any other method outside the application.

How

Verify with the sysadmin that all access to the operating system is restricted to DBAs only. Verify that any shell access is done over a secure protocol, preferable SSH. Check for any accounts on the operating system that should be removed.

8 Restrict permissions on the directory to which the database is installed.

Verify that permissions on the directory to which the database is installed are as restrictive as possible. Again, in an ideal world, the permissions on Unix and Linux would allow full control to the file owner and group and would allow no permissions for everyone. Unfortunately, some database functionality was written without security in mind, and we can find ourselves in the position of breaking database functionality by making file permissions too restrictive.

On Windows, similar measures should be taken. File permissions on the directory in which the database is installed should be limited to the account the database runs as. Ensure that the "Everyone" or "Anonymous" user does not have any permissions on database files. In addition, make sure that all drives being used to store database files use NTFS.

In an ideal situation, even the DBA would not need permissions on the underlying operating system files. However, given the need for the DBA to work with database files and backups, patch the database, and accomplish other chores, the DBA will need some access to the operating system files. Privileged users who do not need access to the operating system should not been granted permissions to it.

How

Connect to the operating system, and retrieve a list of file permissions on all database files. Review the listing to find any excessive privileges. On Unix, check that permissions are set to 770. Be careful in that if you revoke all permissions for "Everyone," many programs may break. Setting tight security is a good goal, but you may have to set exceptions to this policy. Document the reasons for exceptions. For Windows, make sure that permissions are not given to "Everyone." The best practice is to grant permissions to the DBAs who require access only.

9 Restrict permissions on the registry keys used by the database.

For database platforms running on Windows, you need to properly secure the registry keys being used by the database. The registry keys are used to store configuration values that are important to the secure functioning of the database. Make sure that only the account the database runs has permission to edit, create, delete, or even view these registry keys.

How

Review the security permissions through the Registry Editor or through a command-line utility such as GetDACL. After retrieving a complete list of the permissions, review to ensure that no excessive permissions exist.

Password Strength and Management Features

Many database platforms maintain their own authentication settings. You should ensure that passwords and the authentication mechanism do not become the weak link in the chain.

Other database platforms integrate with the operating system or some other security subsystem to provide authentication. For instance, DB2 UDB does not maintain its own usernames and passwords instead using the operating system or RACF for authentication. Microsoft SQL Server in Windows mode uses Windows authentication. This does not mean that users are not maintained in the database. Usernames continue to be maintained in the database because there needs to be a mapping of the users to groups as well as permissions and other database settings. However, the authentication happens at the operating system level instead of in the database.

Using integrated operating security for any of the database platforms has many pros and cons. Pros include

• Operating system authentication typically is more robust than database authentication.

• Operating system authentication typically includes more password management features.

• Password management features are more likely to be implemented already at the operating system level.

Negatives include

• Authentication is out of the DBA's hands.

• A user with an operating system account can access the operating system of the database if it is not configured properly.

10 Check for default usernames and passwords.

The first basic item to audit for is default usernames and passwords. This continues to be an issue for databases. At least five database worms have been based on propagating through databases using default usernames and passwords. Table 9-3 classifies these default usernames and passwords into a few categories. There are collections of literally thousands of these default passwords that can be found on various security websites on the Internet.

Table 9-3: Default Passwords

Category	Description
Default database password	Created in a standard database install. Can be dependent on the components of the database that are installed. Most of the latest versions of databases have eliminated default database passwords. However, default passwords continue to be a serious concern in older versions of database software.
Sample or example passwords	Many samples, examples, and demonstrations of new or existing features are shown in SQL scripts that include creation of a test or sample account.
Default application password	When installing third-party products on top of a database, these products often will install and run using a default user-name and password to access the database. These are known to hackers and serve as a common route to break in.
User-defined default password	When a new account is created, often the password is set to an initial value and then is reset on first use. Problems arise when an account is created but never accessed. Ensure that passwords set on new accounts are random, strong passwords.

How

Verify that all default usernames and passwords have been removed or locked, or the passwords have been changed. There are utilities and tools both free and commercial that you can use to verify this.

11 Check for easily guessed passwords.

Users often use passwords that can be easily guessed by automated programs or clever hackers. The most common passwords used to be password and secret. People are more clever these days and select more secure passwords, but it is still important to ensure that passwords cannot be found in a dictionary or guessed by knowing your date of birth or dog's name.

How

Run a password strength test on password hashes to determine if any passwords are easily guessed. If you detect passwords that are found in a dictionary or can be guessed, talk to the users and help to educate them on the use of strong passwords.

12 Check that password management capabilities are enabled.

Many of the database platforms provide support for rich passwords management features. Oracle leads this area by including capabilities for all the following features:

• Password strength validation functions

• Password expiration

• Password reuse limits

• Password expiration grace time

• Password lockout

• Password lockout reset

One problem is that if you do not configure these settings, they do not provide any additional security. By default, these features are not enabled.

How

Select the configuration values from the database. Ensure that each password management feature is enabled and configured for an appropriate value for the environment. You will need to review the documentation for the database platform to determine the exact commands required.

Activity Monitoring

In this section we are talking about the type of auditing that involves recording the activity in the database. Proper auditing of access to sensitive data has become a standard requirement because of increased regulatory requirements. Regulations such as the Sar-banes Oxley Act have had a significant and positive impact on companies recording access to sensitive data. Until it became a regulatory requirement, it was simply not something very many organizations considered. There are many forms of auditing:

• Access and authentication auditing. Determine who accessed which systems, when, and how.

• User and administrator auditing. Determine what activities were performed in the database by both users and administrators.

• Suspicious activity auditing. Identify and flag any suspicious, unusual, or abnormal access to sensitive data or critical systems.

• Vulnerability and threat auditing. Detect vulnerabilities in the database, and then monitor for users attempting to exploit them.

• Change auditing. Establish a baseline policy for database, configuration, schema, users, privileges, and structure, and then find and track deviations from that baseline.

13 Check that auditing is enabled.

Auditing provides you with a method of knowing when authorized activity is occurring. There are a number of methods that can be used to monitor activity. These include

• Enabling native auditing in the database

• Monitoring network traffic to audit database activity

• Reviewing transaction logs to build an audit trail from the database

Different methods have different strengths and weaknesses. For instance, native auditing is relatively inexpensive because it is typically included in the database. Other solutions are more expensive but meet requirements that native auditing fails to provide.

How

Verify that auditing is enabled for the database. Ensure that the audit configuration meets the requirements of your organization. Review the list of sensitive data in the database, and verify that auditing is properly enabled for sensitive data. Ask the DBA for a list of people who accessed sensitive data for some time period, and see if he or she is able to generate a correct list.

Database Encryption

Encryption is a very broad term used to describe several forms of the subject. Encryption of network traffic has been available for many years-some people use network encryption well, as in SSH and HTTPS. There does remain a large amount of network traffic that is not encrypted, particularly in the database area. Encryption of files has been available even longer and is implemented more widely. Encryption of data in a database is a new topic that has been pioneered only recently. The challenges of encrypting databases are entirely different from those of encrypting files or network traffic. Here we will explore the challenges associated with the various uses of encryption.

14 Verify that network encryption is implemented.

There are two main purposes for network encryption. The first reason is to protect authentication credentials as they move across the network. The second reason is to protect the actual data in the database as they move over the network. The network is not a secure environment-IP addresses can be spoofed, and network traffic can be redirected and sniffed. It is critical that network traffic be encrypted not just over the external network but also on your intranet.

How

Verify that the network and client drivers have been configured to support encrypting network traffic using protocols such as SSL. Verify settings at both the client and the database. If able to, you may even want to take a sampling of network traffic to verify that no cleartext traffic is present.

15 Verify that encryption of data at rest is implemented where appropriate.

Encryption of data-at-rest involves encrypting data as they are stored in the database. While the different forms of encryption use similar algorithms, the format and use of the algorithms are entirely different. Arguably, encryption of data-at-rest is more important than other forms of encryption because the lifetime of data on disk or in the database is much longer than the lifetime of data on the network. If you look at where data are most likely to be stolen, it is directly from the database while at rest, not while traversing the network. This is strong reasoning to support increased use of encryption of data-at-rest.

Encryption of data-at-rest can be problematic, though. Specifically, if encryption is used incorrectly, it can create more problems than it solves. Encryption is not a replacement for the other layers in the security stack-it is a complement to the existing pieces.

The performance bottleneck for encryption in the database is very different from that for file or network encryption. In file/network encryption systems, encryption accelerators can be used to increase the throughput of data because data look substantially more like a stream. In database encryption, the encryption itself is not the bottleneck. The performance impact is almost entirely a product of the mechanisms needed to call out of the database. This is so because databases are working with many, many small pieces of data, so the ability to pull as many pieces of data back and call them out to do the encryption is the limiting factor in performance. Working with database encryption is very different from working with a stream of data, so improving that bottleneck doesn't significantly improve performance.

How

Verify that data that should be encrypted are encrypted properly. Most important, review the location where the encryption keys are stored because the strength of encryption relies on the strength of protection of the encryption keys. If the encryption keys are stored with the encrypted data, an attacker can subvert the security simply by extracting the encryption keys.

Check the disaster-recovery plan to ensure that encryption key management is included as a component. A mistake you do not want your DBA to make is to implement encryption features but fail to include key management in the backup procedures. Failing to back up encryption keys properly results in being unable to recover a database backup.

Database Vulnerabilities, Integrity, and the Patching Process

Databases pose an interesting dilemma with regard to patching. Many databases run on a 24/7 schedule, meaning that they have no allowance for down time. This means that no time is available to bring down the database to apply the patches.

The other major complication for database patching is that testing new patches is typically a 3- to 6-month process. Databases typically are so critical that patches cannot be installed without thorough testing. Given a quarterly patch cycle, the DBAs full-time job easily can become testing and applying new patches. This likely will become a fulltime job for DBAs moving forward, just as we have teams of people dedicated to patching our Windows and Unix systems.

One solution to the downtime problem has been the use of clustering. In a clustered environment, a single node in the cluster can be taken offline, patched, and brought back online. This can work but introduces complexity to the process.

16 Verify that the latest patches for the database have been installed.

One of the most important tasks as an auditor is to ensure that the latest patches have been applied to the database. It is not always easy to keep up with the latest patches because they are changing constantly, and each platform and version may have different patches to apply. In addition, you may have to analyze the patch to determine if the security vulnerabilities addressed in the patch apply to your installation.

Many DBAs attempt to mitigate the need to patch by removing components of the system they determine to have vulnerabilities. While this is a great practice because it does reduce the security risk, it should not be accepted as a long-term replacement for patching. First off, many vulnerabilities fixed in the patches are not documented by the database vendors. Believing that you eliminated the vulnerabilities the database vendor mentioned is a mistake–instead, you need to mitigate the risk using techniques you determine useful, but ultimately you need to apply the patches.

How

Attain as much information as possible about the latest patches, and determine the scope of the vulnerabilities addressed by the patches. Compare the patches with the patch level applied to the database. Talk to the DBA about steps taken to mitigate the risk if the patches are not applied in a timely manner. Even taking mitigating steps should not be accepted as a replacement for applying the latest patches.

17 Verify that the database is running a version the vendor continues to support.

Many legacy databases run versions of the database software that are no longer supported by the database vendor. This can become a problem when a security vulnerability is released, and the database cannot be patched because no patches are available from the vendor.

How

Verify with the database vendor which versions and platforms are supported and whether patches for security issues will be provided. Inventory the versions of the database that are being run, and check for any databases that fall under the unsupported versions. Ideally, you want to keep the databases upgraded to supported versions.

18 Verify that policies and procedures are in place to identify when a patch is available and to apply the patch.

Several of the database vendors, including Oracle and Microsoft, have regularly scheduled patch releases. It is important that you are prepared for the scheduled releases so that you can plan appropriately for patching and testing of the patching. There should be people responsible for reviewing the patches and analyzing them. Installing a patch often can be delayed safely if it is determined that there are ways to mitigate vulnerabilities through simpler steps.

How

Interview the DBA to determine who reviews advisories from vendors, what steps are taken to prepare for the patches, and how long the patches are tested before applying to the production databases, and even ask for notes from the previous patching cycle.

19 Check the integrity of the database by looking for root kits, viruses, backdoors, and Trojan horses.

As the hacker tools designed to break into databases grow in sophistication, we expect to see more robust ways to control the database by hackers. Currently, there are a handful of these types of tools, and their numbers are growing quickly. These attack tools require that an attacker gain some access to the database to install these tools. Many different delivery mechanisms are being used, including installation by a disgruntled employee, being hidden in an e-mail, or leveraging a vulnerability.

How

These malicious tools are difficult to find because they are often designed to hide themselves from detection. There are counter-tools designed specifically to detect and disinfect your database, which are much more accurate than manually attempting to find a backdoor or other infections. You can, of course, check for these types of infections manually, although doing so manually is technically difficult. Doing so requires looking very closely at the data dictionary. Root kits are implemented by modifying the commonly used system-stored procedures and system views, so you will need to look even closer at the dynamic tables that exist underneath the system views.