Data creation is also a matter of access control

Database access control is not only about controlling who can read the data, but also about making sure that the right people get to generate it. You may say that they are two sides of the same coin. It’s not only important to restrict how data is extracted from the database. You need to make sure that the data stored in the database comes from the right sources.

The standard way to enforce controls upon data creation in the relational database world is through the use of INSERT and UPDATE privileges on whole tables. There are situations, however, when this is too coarse. Typically this happens whenever we want to restrict what data a user is allowed to insert into a table. In these cases, the traditional solution is to define insert and update triggers, i.e. pieces of code, written in the usual, imperative programming languages offered by the DBMS vendor, that are executed every time an INSERT or UPDATE statement is executed on the table in question. Triggers are very powerful. Sometimes even too powerful. The main problem with triggers is that they are cumbersome to write and maintain, making them quite error prone. They also get spread all over the place. Thus they have essentially no central point of control, are database dependent, non-declarative and not policy-based.

We have already discussed, in a previous post, how to do policy-based, attribute-based, centralized and fine-grained access control for SELECT statements using Axiomatics Data Access Filter (ADAF). Since version 1.4, ADAF MD provides similar controls for both INSERT and UPDATE statements. This allows you to define policies that control how and when a user may insert a new row into a table, based on attributes of both the user and of the data to be inserted. For update operations it lets you control which rows can be updated and how.

Fine-grained access control for INSERT statements

All this sounds very good, but what does it really mean? Well, let me guide you through a concrete example. It’ll hopefully give you a better idea of how ADAF MD 1.4 lets you control which data is inserted into the database and by whom.

In the example we shall only discuss how to control INSERT operations. Though there is a component of data insertion implicit in UPDATE operations, we shall leave that for a later blog post.

The Insurance Claims example

In the following sections, we will be looking at a database that contains information about insurance policies as well as claims over those policies.

Let’s assume there is a table in the database that holds information about all claims made by insurance holders. This table is accessible to all claims processors, as stated by the following rule:

The rule above is written in ALFA, a DSL that makes it easy to author and edit XACML policies. Our rule is placed within a policy, Claims_Access, whose target tells us that the policy pertains to table CLAIM in schema SCOTT.

So far, our policy allows claims processors, i.e. users with the “claims manager” role, to perform SELECT operations on the SCOTT.CLAIM table. Of course, this is a rather minimal policy to control who can read insurance claims data, but we shall keep it simple for the sake of presentation. After all, our goal today is to discuss how we can apply fine-grained access control to INSERT statements.

Claims processors can examine any insurance claim in the database. However, not every claims processor should be allowed to register a claim on behalf of a policyholder. In our example, every policy is issued within a region. Likewise, every claims processor is assigned to a region. Our ‘business manager’ has therefore decided that:

“claims processors should be allowed to register claims for policies in their own region only.”


It’s easy to write this business requirement in ALFA. We just need a new rule which applies to INSERT actions and checks that the claims processor’s region (subject.region) coincides with the claim region (InsuranceClaim.region).

Notice that a claim is made in relation to a certain insurance policy and that the claim region is actually the region where the policy has been issued.

What Claims Processors Do

Let’s see our rule in action now. We need just a few claims in table SCOTT.CLAIM to start with:


A water leak

Bob, who is a claims processor (meaning he’s been assigned the “claims manager” role), gets a call from Alice. She’s discovered a water leak at home and wants to place a claim on her home insurance. Through a Claims Management Application, Bob executes the following insert operation on the CLAIM table:


The operation succeeds, because Bob is a claims processor assigned to the Stockholm region, and Alice’s home insurance is registered in Stockholm. Bob then asks Alice to mail him some extra details about the incident, which she promises to do.

A new windshield

A few minutes later, Bob receives a call from Elsa who wants to file a claim on her car insurance. She hopes the insurance company will cover the costs of a new windshield she had to buy after the old one broke. Bob attempts to register the claim. This time, however, the database informs back that no rows were inserted:

It turns out that Elsa’s car insurance policy is registered in the Gothenburg region, which is not the region where Bob operates (Stockholm). Bob apologizes to Elsa for his clumsiness. He should have forwarded her call to a claims processor in Gothenburg right away. He does it and hangs up.

You may wonder at this point why not let the application check that Bob is not allowed to register this claim before ever trying to push it to the CLAIM table. This is a fair point and the recommendation is to enforce access control at the application level whenever possible (and you should definitely try the ABAC approach supported by a tool like the Axiomatics Policy Server). There are situations, however, when this is either impossible or undesirable. For example, it may be impossible because you’re not allowed or don’t dare change the application. It may be undesirable if there are too many applications that access the CLAIM table and you don’t want to touch all their code-bases. Or perhaps you let some of your users access the database directly and not through an application. There may be other reasons too. At the end of the day, in all these cases, you may have to enforce access control at a layer outside the application – the database layer, and that’s where a solution like ADAF MD comes to your assistance.

A pipe gets fixed

Our security policy to control inserts on table CLAIM is rather simple. In fact, it’s easy to imagine that it should have to be extended with many other rules before it really covers all high-level security requirements. For instance, nothing in the policy prevents a claims processor from assigning a new claim to another claims processor. Is this acceptable? Let’s suppose it is. In the example below, Bob registers a claim on Alice’s home insurance, and assigns it to Julia, a colleague of his.


The operation succeeds simply because Bob is allowed to register claims on Alice’s home insurance (being both registered in Stockholm, as we saw before).

Time to process old claims

Bob gets no calls for a while, so he takes a look at the CLAIM table to pick a claim to process. He notices that the table contains two new claims on Alice’s home insurance policy, and no new ones on Elsa’s car insurance (remember Bob was not allowed to register Elsa’s insurance claim), as expected:


With ADAF MD you can write ABAC policies to protect databases at the level of whole tables, single rows and even isolated cells. ADAF MD uses a rich and expressive policy language, so that your access rules can combine properties of the data and of its consumers in rather flexible ways. For instance, the insurance claims example made use of the user’s role and region, both user attributes, and of the claim region, which is an attribute of the actual data passed in the INSERT operation.

In short, ADAF MD 1.4 lets you write declarative, attribute-based, fine-grained access control policies which, besides controlling how data may flow out of the database, can restrict who can write what data into the database. Knowing that only authorized people can write to your database is essential if you want to build up confidence on the integrity and quality of your data.


Related Articles

Meeting today’s dynamic authorization and access challenges: The Axiomatics story | Dynamically Speaking
Dynamically Speaking
For more than 15 years, Axiomatics has worked with companies worldwide to define and deliver solutions to the most complex authorization and access challenge. In...
Getting started with Zero Trust using dynamic authorization | Dynamically Speaking
Dynamically Speaking
Zero Trust. It’s everywhere. It’s a methodology that’s been around for years, and we are now seeing a significant uptick in the number of enterprises...
The case for dynamic authorization in banking and finance
Attribute Based Access Control (ABAC)
More than other organizations, banks, and financial institutions face the highest levels of scrutiny when it comes to how they protect critical assets and sensitive...