Delaying sensitive database operations until additional authentication conditions are met

Question

I have a REST API where authorised users sometimes need to make changes to data about their company. For the most important information (e.g. IBAN details might fall under this category), we need to implement additional security checks before actually allowing a change to occur. The aim here is to ensure that changes are not made accidentally or maliciously if an authorised user leaves their device unattended.

While we could just request the user enter their password as confirmation for each of these actions, that is not quite flexible enough and we would like to allow the use of 2FA methods down the line (e.g. sending an email to their existing email account with a confirmation URL, a OTP etc). In addition, we would like our 2FA challenge to be available for any resource which contains sensitive information.

What is the best approach here? Some options we have considered are:

• Use an INSERT only database table for each sensitive collection. This will be very complicated to implement since we would have to replace our existing single column primary keys with composite keys which isn't really viable and means our current REST resource ids would need to be changed. This method also adds considerable bulk to each table which may decrease indexing.
• Create a mirrored table for each sensitive collection. When an update occurs, it first goes here until an appropriate 2FA has been completed when it replaces the resource in the main table and the temporary resource can be deleted. This method means many of our tables would be duplicates.
• Create a table that stores data about sensitive requests with the table that it refers to, some info about handling the request and a JSON-encoded representation of the changed object. This seems like the best approach to me since it will be compatible with any collection easily and can support any 2FA we implement later. When the user authorises, the JSON representation is copied to the correct table and the request deleted. However, sticking arbitrary object representations into a column as JSON is not really in the spirit of relational databases though I can't see that we would ever be searching the change request data so it might be the best solution pragmatically?

Any thoughts on those options or any better/more standard solutions?

Answer 1

Since your application has more stringent security concerns, it's best to think about the security as a whole. There is no easy solution, but I think the functional requirements are pretty well defined:

• A protected transaction must remain pending until the second factor authentication is complete
• The transaction will be rolled back if the second factor authentication fails or times out

What I'm deriving from those stated requirements are auditing requirements:

• The initial request for change was made, identifying user, transaction id, resource, type of change (C,R,U,D), time, and any other information you need in your logs
• The 2nd factor authentication attempts are made, identifying the user, transaction id, time, success or failure
• The pending transaction timed out, identifying user in the initial request, etc.

What you can't do

One of the suggested options which were to update the table and have a flag on whether the information is visible. The reason this won't work is because we loose the ability to roll back if the 2nd factor authentication fails or times out. That solution can only work for creating new records, not updating existing ones. So with that out of the way...

Option 1: Elevated Permission Mode

Some tools (like Atlassian JIRA) address protecting critical changes to the configuration by requiring users to re-authenticate when they want to go in to Administration Mode. The administration mode acts as already having going past the 2nd factor authentication. For this to work you will need the following:

• The user is kicked out of that mode after a time-out (shorter than your typical session) of no activity
• The user is kicked out of that mode when they resume other activity in the app (i.e. you are only in it to perform a specific function)

The biggest pro to this option is it makes the transaction atomic, and minimizes the risk of exposure for the elevated permissions. If someone steps away from their desk, the exposure is limited to 5-10 minutes (the time you set for the elevated permissions session). This is the simplest option to implement, but you may have some security requirements that prevent the option.

Option 2: Redis or some other caching service

Redis, like many other cache servers have the concept of ephemeral keys. In short, a record is automatically deleted when the timeout passes. The only way the application knows the timeout occurred is when it asks the cache service for a value and it is no longer there. This solution is more complicated in a couple of ways, the least of which is another service to work with.

Recommendations:

• Represent the transaction as an object with the appropriate values that the user wants written
• If the payload is sensitive, encrypt it so it is never in the clear
• Use an easily calculated key like transaction:{resource}:{transaction id}
• Set a Time To Live (TTL), and register a handler when the TTL expires
• When the 2nd factor authentication succeeds explicitly remove the transaction from Redis and commit it to the database, logging the audit action
• If the 2nd factor authentication fails explicitly remove the transaction from Redis and log the audit action
• If the TTL expiration handler is invoked, log the audit action

This keeps the pending transaction completely out of the database, and uses temporary storage for the transactions. This of course does come with trade-offs. The transactions objects are not persistent, so if Redis goes completely down, you lose all pending transactions at the time. In a proper cluster, the likelihood of that happening goes down.

Option 3: Message Queues or Kafka

In this option we use persistent queues instead of temporary cache to store the transaction object. Some message queues (like RabbitMQ) allows you to set a per message TTL on a queue--allowing the queue to perform the same function as Redis did above. The primary benefit we gain here is that the queue is persistent. If the message queue goes down, then it can resume operations.

The major downside to Message Queues is that they are accessed serially. That means you have to cycle through messages and requeue what you are not ready to deal with. Either that, or you have to do some complex queue routing and set up a TTL on the queue itself.

This will be significantly more complicated to manage for this use case. I only mention this option because it is very well suited for more general asynchronous operations where you care more about the sequence of events than a specific message.

Option 4: Shadow Table

The last option is basically what you outlined in your opening question. You essentially have a table who's sole responsibility is to temporarily store pending transactions before they can be committed. This options requires your application to actively enforce transaction timeouts, but architecturally is still fairly simple.

It's not a bad solution, so I wouldn't necessarily be scared of implementing it.

Bottom Line

Adding infrastructure to your system adds to the overall complexity. However, many pieces of infrastructure can serve multiple functions. Redis can also be used to speed reading a specific record in addition to supporting your multi-factor transaction use case. Message queues are good at general asynchronous processing and sending notifications.

Evaluate your architecture as a whole:

• If you don't want to add additional infrastructure your choices are Option 1 and Option 4.
• I recommend only adding infrastructure when there is a strategic advantage--i.e. you are [eventually] solving multiple problems.