Everyone who has ever travelled on a train, metro or underground, particularly in London, will be familiar with the famous “Mind the Gap” announcement. It is made because the curved design of some platforms leaves an unsafe gap when the straight train carriages stop at the station. The design of some IT systems can also leave gaps, particularly between the application and the database. Understanding and pointing out these gaps should be the concern of all IT auditors.
Examples of these gaps include:
1) Users Have Direct Access to the Database.
In many legacy applications there will be a one-to-one correspondence between application and database users. That is, one user will exist in the database for every user that exists in the application. Further, the authentication may be controlled by the database. In these scenarios, it may be possible for the users to access the database via open database connectivity (ODBC) or something similar.
This means that the users could use common tools such as Microsoft Access or Microsoft Excel to access the database directly. These users are now connected to the database with the privileges defined for them, but, critically, any controls defined by the application can be bypassed. For example, if the application is written to ensure that a particular field has particular limits, these may be overridden by updating the database table directly.
Solution: Where users have individual database users configured, application roles or a similar mechanism should be employed to ensure that they cannot directly access the underlying database.
2) A Proxy User Is Employed, but the Password Is Stored in Plaintext.
To overcome the design weakness above, a proxy user may be employed. A proxy can be defined as a person authorized to act for another. A proxy user therefore connects to the database on behalf of the application users. One proxy user usually connects to the database on behalf of all application users. However, with the proxy user solution, the database username and password must be stored somewhere.
It is common to store these in the Windows registry, initialization files, configuration files, etc. Unfortunately, the password is often stored in plaintext on the client server (in a two-tier solution) but more commonly on the application server (in a multi-tier solution). Access to this username and password will allow access to the database.
Solution: The database password should always be stored using strong encryption.
3) A Proxy User Is Employed With Encryption, but User Has Elevated Privileges.
In this scenario, the database password has been encrypted, but the proxy user has been defined as a database administrator, schema owner or database owner. These roles afford the proxy user elevated privileges. This means that the proxy user may have access to Data Definition Language (DDL) as opposed to Data Manipulation Language (DML) privileges.
This may allow the user to drop and/or create tables or access other database schema outside those for the given application. Where the application is susceptible to SQL injection, there is an increased risk to the underlying databases.
Solution: Proxy users should always be defined under the principle of least privilege.
Author’s note: If you have additional database gaps to add to the above list, I would be very much interested to hear about them.
(About the author: Ian Cooke is a group IT audit manager at An Post, and a volunteer and subject matter expert with the ISACA. This post originally appeared on his ISACA blog, which can be viewed here)