How Database Auth Can Improve

I have been looking into a couple database auth edge cases for my work on Cisco’s Advanced Malware Protection for Endpoints product. Behaviors surrounding cred rotation, user deleting and session revocation were my primary concern. Basically, I was looking at what it would take to completely kick a user out of our databases. During this process, I found a few things I don’t like about the current state of database auth.

This article isn’t about specific databases though. If you’re interested in how specific databases (MySQL, PostgreSQL, Cassandra and MongoDB) behave, check out my presentation from BSides Las Vegas 2019 (~25 min). This article is focused on a couple widely seen database auth behaviors and possibilities for fixing them.

Authentication and Authorization

This image is a screen capture from an episode of the Spider Man cartoon that aired in 1967.

This recap of authentication and authorization is mainly to convince you that I know the difference between the two.

Authentication is when a user establishes an identity with a database (or a service more generally). The database is asking the question “Who are you?”. The user presents the identity they want to establish along with some sort of proof (e.g. password, kerberos ticket or cert) that the identity belongs to them.

Authorization is when a database decides what a user with an established identity can do. There are usually rules configured by an admin that dictate which actions a user can perform.

Some Bad Behaviors

These behaviors are easy to test with database command line clients, so I recommend you try them out on your database of choice. You can also just watch my talk if you are running MySQL, PostgreSQL, Cassandra or MongoDB. That’s the last time I’ll mention the talk… I promise.

Sessions Persist After Dropping Users

I’ve seen this behavior in all the databases I looked at. The situation unfolds like this:

1. The user mitch is authenticated and a session is established
2. An admin deletes the user mitch
3. Sessions associated with the user mitch remain active

Here is an example with PostgreSQL 11.4:

When a user is dropped, it no longer has any standing with the database and should not own an active session. You can’t establish new sessions with a user that was dropped. I don’t think you should be able to maintain a session with a dropped user either.

Next, the session can still perform unprivileged actions. In the above example, the session can extract metadata on tables present in the database. The unprivileged actions that can be performed with these sessions varies from database to database.

Even if none of the submitted commands worked, you can still submit commands (e.g. SQL) to be evaluated. I don’t like this because it provides a rich attack surface for an adversary using a dropped user’s session.

Sessions Regain Privileges After Recreating Users

This behavior builds on the last one. A session persists after user deletion. If a user with the same name is recreated, the session gains the abilities of the newly created user. I did not see this in all the databases I tested, but I did see it in more than one. It looks like this:

1. The user mitch is authenticated and a session is established
2. An admin deletes the user mitch
3. Sessions associated with the user mitch remain active
4. An admin recreates the user mitch
5. The old session can be used with the permissions granted in step 4

Here is an example with MySQL 8.0.17:

In this example, the MySQL shell is able to write and read data after an admin deletes and recreates the user mitch. It does not matter if the password for the recreated user is different. Also note that if the user is recreated with different permissions, the previously established session inherits those permissions. An extreme instance of this would be if the user mitch was recreated with admin capabilities. Then the old session would become a usable admin session.

Bad Behavior Causes

User Aliasing

I have seen sessions regain privileges on user recreation when there is nothing in the database to distinguish two user instances with the same name. Since there is nothing to distinguish two user instances, they become aliased. Then, when an old session tries to perform an action, its permissions are looked up by the name associated with that session. This grants the old session permissions associated with a newly created user that has the same name. Queue the Spidey meme.

Here is a solution from PostgreSQL which does not suffer from this problem. When the user table is listed, there is a column called usesysid. This is a unique identifier that PostgreSQL assigns to a user on creation. You can see below that two instances of the user mitch have different usesysid values. Using this unique identifier prevents user aliasing.

Authorization for Access Revocation

I’ll try to explain here why sessions persist after users are dropped. In essence, I think it is because databases can mostly get away with relying on authorization.

Database auth information is stored in tables just like any other data. Therefore, authorization rules benefit from any replication mechanism already built into the database. Additionally, authorization information is checked frequently to ensure newly permitted commands work and revoked commands don’t.

With this setup, when a user is dropped (along with its permissions), sessions can only perform unprivileged operations. If unprivileged operations don’t exist in the database, then database access is effectively revoked. This isn’t entirely true since commands can still be submitted for parsing and evaluation, but it’s pretty close.

I don’t think performing session revocation on user deletion would add much complexity though. User tables also benefit from built in replication mechanisms. Additionally, session dropping could be driven by user action, just like authorization is.

If a database has unique user ids to avoid user aliasing, then session revocation could occur whenever an action is attempted. When a command is submitted, the database should first check if the session’s user id is still in the user table. If it is, pass the command along to evaluation and authorization. If not, kill the session.

Aside: Since auth information uses normal replication mechanisms, any data consistency bug is potentially a security bug.

Some Mitigations

There are a couple general purpose mitigations that can help with these behaviors.

The first is downtime. If you restart your database service, all sessions on that server will be closed. Therefore, if you wanted to fully revoke a user’s access, you can drop their account and restart the database.

The second workaround only applies to the case when users are recreated. If you are using a database that aliases different user instances with the same name (e.g. MySQL), you should never create the same user twice. Even if you want to recreate the same user, make the names slightly different (e.g. mitch_20190830 and mitch_20190831). This effectively implements the unique user id method yourself.