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.
- 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
- 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.
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.