I finally have a breather and can start working through my backlog of ideas. I start with some background that will make the motivation for subsequent posts clearer.
What are the threat models for the persistence layer of an application, specificially the threats against the database itself? Remember that a ‘threat’ is an adverse act, whether intentional (by an attacker) or incidental (e.g., by a distracted employee). I refer to an ‘attacker’ below but that’s for convenience – in many cases it’s more likely to be a non-malicious employee making a mistake.
Attacker with access to the application
Scenario: A casual attacker with access to the web application as a visitor or user.
Vulnerability: SQL injection.
Countermeasure: use positional parameters instead of explicitly constructing SQL statements directly. This is true even if you use sanitization, either ad hoc or in a library provided by the database vendor. Parameterization isn’t always possible but the exceptions should be carefully vetted. This countermeasure should always be used.
Attacker with access to the database as the application database user
Scenario: the attacker has full access to the database as the application database user, either via SQL Injection or another mechanism.
Vulnerability: DDL commands – the attacker can create, alter or drop tables.
Countermeasure: the application’s database user should own the data but a separate database user should own the schema. This prevents the compromised account from modifying the tables. This countermeasure should always be used.
Vulnerability: Data corruption (prevention).
Countermeasure: this is a little-used approach that is sometimes used for the most sensitive information. The application user has SELECT permissions on the table but does not have INSERT, UPDATE or DELETE permission. A second database account with INSERT, UPDATE and DELETE permission to the table creates stored procedures to update the data. These stored procedures are configured to run with the permissions of the owner, not the caller. The application can read the data as usual but must use the stored procedures to update it.
This is not as onerous as it sounds since data is read far more often than it is modified and reads are not affected. Data insertion and modification is more complex but in a well-written application it will be fully encapsulated by the peristance layer.
A variant is to use one database user to read the data (with only SELECT permission) and a second database user to modify it (with INSERT, UPDATE and DELETE permission). The application uses the first account freely but only uses carefully vetted code with the second account. The main drawback is that modern persistence frameworks make heavy use of caching and care needs to be taken to ensure that the caches are consistent.
This countermeasure is typically only used with high-value content such as user authentication information.
Vulnerability: Data corruption (detection).
Countermeasure: the database contains shadow columns that are updated on writes and verified on reads. The updates should be performed programmatically – if this is done via triggers all updates will appear to be valid.
There are several variants. The shadow column can be:
· a simple copy – easy but an attacker can easily spoof it.
· a cryptographic hash – slightly harder to spoof unless salted but the hash can cover multiple columns. Vulnerable to an attacker copying values from an existing row unless the hash incorporates the row’s primary key. This should be considered the minimum acceptable approach.
· a cryptographic message digest (HMAC) – impossible to spoof since it requires private key, but as before it should incorporate the row’s primary key to prevent an attacker from copying values from an existing row. This approach is the most computationally expensive and requires care with key management but it also provides the best assurance that the contents have not been modified.
There are also three separate locations for the shadow column:
· in the table itself – easiest but also obvious to any attacker.
· in a separate table – much less obvious to an attacker but harder to implement.
· in a separate schema – least obvious but requires the most effort.
Vulnerability: Data corruption (detection).
Countermeasure: the database table has INSERT, UPDATE and DELETE triggers added. The functions called capture the existing and modified data (if any), timestamp, primary key, database user, and anything else of potential interest. (E.g., some databases may provide the remote IP address.) This information is written to a separate log table.
This allows the organization to reconstruct a timeline of when values were modified. This could be particularly important if the attacker changed a value temporarily, performed an unauthorized action, and then restored the original value in an attempt to cover his tracks.
Vulnerability: Unauthorized disclosure (detection).
Countermeasure: this is another little-used approach that is sometimes used for the most sensitive information. The application user does not have any permissions on the table. A second database account with SELECT permission to the table creates stored procedures to access the data. These stored procedures are configured to run with the permissions of the owner, not the caller. The application must use the stored procedure to access the data.
The stored procedure records some or all access to a log table.
This approach can be used to flag access to high-value data. This could be any classified data, criminal or financial records for public figures such as politicians or celebrities, etc. The application can and should also flag this access but the application alone cannot detect direct database access.
Vulnerability: Unauthorized disclosure (detection).
Countermeasure: this is an idea that has been floating around for a long time but gotten little attention until recently. The database is seeded with sentinel values in columns with value to the underground – information useful in identity theft, credit card fraud, etc. These values will never be accessed via the application since they’re linked to nonexistent email addresses but an attacker has no way to determine this. (Few attackers will even know this is a possiblity.)
Underground forums can then be monitored for the appearance of these values. If a sentinel value appears the organization will know that the database has been compromised. A variant is to provide the sentinels with limited value, e.g., a “stolen credit card” may have a credit limit of $100 so the details of any use will be known and criminal authorities will be able to demonstrate actual use and damages.
In most cases the organization will know nothing more than the fact that a compromise occurred but that alone should be enough to trigger action.
Attacker with full access to database backups
Scenario: the attacker has full access to database backups.
Vulnerability: Unauthorized disclosure.
Countermeasures: Backups should be encrypted as a whole (e.g., with PGP/GPG) or columns could be encrypted within the database. Both approaches introduce key management issues, e.g., you may wish to rotate PGP/GPG keys on a monthly basis to limit exposure if a key is compromised.
Vulnerability: Data corruption if the attacker is able to modify the backup and force the database to be restored from that modified backup.
Countermeasures: Backups should be encrypted as a whole (e.g., with PGP/GPG) or columns could be encrypted within the database. In addition we can add HMAC checks on sensitive rows. As before all approaches introduce key management issues.
Attacker with full access to the database
Scenario: An attacker with full access to the database as the dataase user of his choice.
Vulnerability: Everything.
Countermeasures: Limited to non-existent, depending upon the sophistication of the attacker.
One noteworthy exception is database-specific extensions using Unix/Linux shared libraries or Windows DLLs. In these cases the user-defined functions can access data outside of the scope of the database, e.g., cryptographic keys stored in the filesystem or (better) retrieved from a web service during initialization, but no database user can access that information unless a mechanism is explicitly provided. An attacker can replace the user-defined function but the replacement would lose functionality.
Future Blog Entries
This list has inspired a number of future blog entries. Unfortunately I can’t estimate how long it will take to fully research and publish them since it looks like I will be very busy again next year.
PostgreSQL Extensions – how to write a PostgreSQL extension. See http://www.postgresql.org/docs/9.3/static/extend-extensions.html and PostgreSQL Extension Network.
Cryptographic PostgreSQL Extensions – this is not pgcrypto. The first phase provides a wrapper to the OpenSSL types and operations on them. These will be user-defined types and functions. The second phase will build on this to provide additional functionality based on the idea that the application should have no access to the cryptographic material in the database – it will be managed independently of the application.
Sample triggers and user-defined functions to log object modifications.
Sample AOP classes to record and verify column hashes.
Exploration of PostgreSQL Security Labels and sepgsql.
Finally I would be remiss if I did not mention my earlier entries on PL/Java:
| Reference: | Database Threat Models from our SCG partner Bear Giles at the Invariant Properties blog. |
