In a previous blog post we discussed how the Axiomatics Data Access Filter for Multiple Databases lets you define and enforce fine-grained, policy-based access control on data at the time it’s inserted into the database. This ability is fundamental if you want to provide any guarantees on data quality. Obviously, guaranteeing data quality is a complex task that involves taking care of the many ways in which data can come to reside in the database. For instance, it’s not enough to be able to control who inserts data into the database, but also who can modify it, and how.
One update, three checks
The same mechanism that ADAF MD uses to control data insertions can be applied to control data update operations. The main difference is that an update operation usually involves both reading data from the database, computing new data with it and other external information, and writing this new data to where the old data used to be. For this reason, before a user is allowed to update a row in the database table, ADAF MD ensures that the user is authorized to:
- Update the row in question (based on, among other things, the data currently stored there),
- Select the data, from the row or elsewhere, which is going to be used to compute new data for the row, and
- Insert the newly computed values back into the row.
Let’s look at our Insurance Claims example, from a previous blog post,to see how this works in practice.
The Insurance Claims example revisited
In this example, we have a database with information about claims made on insurance policies. In particular, it contains a table, SCOTT.CLAIM, that lists each insurance claim, its corresponding policy ID, the amount claimed and a bit of text with a human friendly description of the claim. For each claim, there are a couple of status bits telling us whether the claim has been approved and/or paid by the claims processor assigned to it.
We have an access control policy over the SCOTT.CLAIM table with, so far, only two rules. Here is a fragment of the ALFAsource:
Rule RC1 allows claims managers to read (i.e. SELECT) data from anywhere in the table. Though the policy places no restrictions as to which column they can read, other scenarios are possible where we would have to restrict access to specific columns, probably masking all or part of the data they contain.
The second rule, RC2, lets claims managers register (i.e. INSERT) a claim, provided that the claim region coincides with the region in which they operate.
Constraining UPDATE statements
Up to this point, our policy does not say anything about who or how data is updated in the SCOTT.CLAIM table. So, let’s consider a new business requirement, this time affecting data updates:
“A claims processor can update a claim only if it has been assigned to them.”
The corresponding rule looks like this in ALFA:
Only authorized rows get updated
Back to the Insurance Claims example. By the time we reached the end of our previous post, Bob was about to examine the list of claims assigned. He needed to decide whether to approve them or not. Let’s see how he’s doing.
First, Bob notices that Alice has made two claims on her home insurance.
Because Alice has provided enough evidence on her claims and the claimed expenses are reasonable, Bob decides to approve all her claims. Through the Claims Management Application, Bob executes the following update operation on the CLAIM table:
Bob realizes that only one row got updated. Looking at the table contents, he sees that his update operation only approved claim C0008, but not C0009.
Which rules in our policy determined that Bob could update claim C0008 but not claim C0009?
Let’s examine the UPDATE statement once more:
Notice first that this statement requires that all rows where policyid=‘alice-home’are identified. This is a read (i.e. SELECT) operation, which must naturally be subjected to access control. In this example, rule RC1 gives Bob permission to read the value of column policyid over all rows in the table. So that’s fine, rule RC1 does not restrict any rows from the set of rows he can update.
We wonder then which rows of table SCOTT.CLAIM Bob is authorized to update. This is given by RC3, which is the only rule that explicitly concerns the UPDATE action. According to this rule, Bob can only update claims that have been assigned to him, namely claims C0003, C0005, C0007 and C0008. That excludes claim C0009, which is not assigned to Bob, but to Julia.
So the update operation is not allowed to affect claim C0009, but is it really okay to update C0008? ADAF MD takes extra care to guarantee that the data inserted by the UPDATE operation could have also been inserted using an INSERT operation. This is because you can think of an update on claim C0008 as the operation that inserts a new row for claim C0008 — and deletes the old row in the process. Then we are looking at rule RC2: It is the only rule in this example that restricts INSERT actions. It turns out that RC2 allows the “insertion” of the modified row for claim C0008, because C0008 belongs to the same region (Stockholm) where Bob operates (see our previous blog). Notice that the new value for the approved column is not used by RC2, but could have been.
Consistency with INSERT constraints
In our previous post, we saw that RC2 prevented Bob from registering a claim on Elsa’s car insurance:
This was because Elsa’s car insurance was registered in the Gothenburg region, while we knew that Bob operates in Stockholm.
Would it be possible, then, for Bob to edit claim C0008 and convert it, intentionally or unintentionally, into a claim on Elsa’s car insurance? Let’s see:
By RC3, Bob is authorized to update claim C0008, because it’s assigned to him. However, rule RC2 prevents Bob from inserting a claim on a security policy outside his region (Stockholm). Thus, his attempt to circumvent the controls on INSERT, by using UPDATE statements, fails.
Because ADAF MD applies to UPDATE statements, the same filter conditions associated with INSERT statements, it guarantees that UPDATE and INSERT are treated consistently.
If you are concerned with the quality of the data stored in a database, you also need to control who updates the data and how it’s updated. UPDATE operations are actually combinations of reads and inserts, and therefore it’s important to pay special attention to the interplay between access control rules for all these actions. With ADAF MD 1.4 you can write finegrained attribute-based policies that consistently protect data flows into the database and out from it.