Traditionally, when a DBA works on database security, he would focus on authorization and authentication, keeping in mind mainly data access and views. This typically deals with the SELECT SQL query.
A blog article from September 30, 2015, by Pablo Giambiagi, titled “Data creation is also a matter of access control“, discusses database access control in the realm of CREATE and INSERT statements.
My post will attempt to highlight his points and discuss the implications.
Coarse controlled statements, like raw CREATE and UPDATE, any are the standard way of creating and changing values within the database tables.
However, when we take into consideration company data security policies and business rules, these basic methods will not suffice. Apart from creating rule restrictions in the front-end and the application interface, the database needs to be secured as well.
Using triggers is a powerful way to do this. But triggers are complex, “cumbersome to write”, difficult to maintain and adapt, can be “error-prone” and may be seen as too rigid and “too powerful” for enforcing policy-based security.
An easier way, and a more effective way, to integrate company security policies into the database, especially for data creation, is to work with “fine-grained” access control INSERT and UPDATE statement.
What does that mean?
For example, if an Insurance Claims company has a business rule which allows a Claims Manager to view all the claims, then the database access code would be something like the following (for a manager named “Scott”)-
policy Claim_Access
{
if target table_schema == “SCOTT” and table_name == “CLAIM”
{
rule
{
if target clause subject.role == “claims manager” and action == “SELECT”
permit
}
}
}
Now, if the business privacy policy further states that “claims processors should be allowed to register claims for policies in their own region only.”
The rule logic can be “fine-grained” to include a condition for checking the region of the authorized role. This would add extra security measures.
rule
{
if target clause subject.role == “claims manager” and action == “INSERT”
permit
condition subject.region == InsuranceClaim.region
}
So if Scott is in charge of Region A, he can only take in calls for claims from Region A and will be able to create a new row in the CLAIM table. He will not be able to do so with a claim from Region B.
Implications-
By fine-tuning queries, using specific conditions it is possible to make coarse-grained access controls into fine-grained access controls.
This helps to integrate safety and security measures into the database, without relying exclusively on the application interface, and also without being dependent on hard-coded complex database triggers.
It only helps to translate the language of written security policies and business rules into flexible code with can be translated into DML (Data Manipulation Language) statements.
This is definitely an enhancement to standard database accesses control practices.