Fine-grained data access control across Oracle, IBM DB2 and SQL Servers

In 1668, when Thomas Hobbes wrote ‘knowledge is power’* for the first time ever, it is unlikely he was thinking of knowledge in terms of information. Nonetheless the aphorism has kept its weight in the Information Age. For us, knowledge means acquired information, and its power emanates from our ability to share or withhold information.

Information is carried by data, thus protecting who has access to data is crucial to businesses and organizations alike. At the same time, project-specific requirements, corporate rules, national laws and regulations, all contribute to a constantly changing universe of policies which demand increasingly finer controls over how data is manipulated.

While you can always implement access control policies to data stored in (relational) databases using stored procedures and views, it is a fine art to get the approach to work, scale-up and comply with the ever-varying high-level requirements dictated by management, while maintaining policy visibility and keeping change costs on a leash.

RDBMS vendors, like Oracle and IBM, address this problem with similar techniques:

  • Oracle’s Virtual Private Database (VPD) lets users associate PL/SQL functions to a table. These functions generate ‘WHERE clauses’ that are applied every time a user tries to access the table – information the user is not authorized to access is filtered out.

  • Row and Column Access Control (RCAC) was Introduced in version 10.1 of IBM DB2. This makes it possible to directly associate row permissions and column masks to specific tables. As it doesn’t force you to code SQL PL functions, RCAC is seen as ab improvement on VPD . Row permissions are basically what IBM calls ‘SQL search conditions’, and what we, at Axiomatics, call SQL filters.

VPD and RCAC represent a substantial improvement over the costly and error-prone definition of stored procedures and views. However, they suffer from a number of limitations:

  1. A change to a single column name may impact several policies in ways that can be difficult to understand and therefore require detailed analysis. Think of a column name change that forces rewriting several PL/SQL functions.

  2. Policies are spread out through PL/SQL functions (Oracle VPD) and row permission specifications (IBM DB2 RCAC).

  3. Transferring access control policies from one DBMS to another essentially implies re-implementing all of them using the proprietary solution of the target DBMS. While moving from RCAC to VPD could be more or less straightforward, migrating from say a VPD to a SQL Server, which doesn’t provide an equivalent solution, would be much more complex.´

The Axiomatics Data Access Filter (ADAF) MD addresses these issues using:

  1. XACML 3.0: a standardized, declarative language to express access control policies.

  2. A resource model that maps XACML entities to database objects (see blog post “Policy-based Data Filtering”).

  3. A SQL proxy able to enforce access control over SQL queries destined to different databases, possibly of different type/make. The SQL proxy within ADAF MD is an OEM component from a market leading vendor in this field. In the figure below, the SQL proxy knows that queries originating at application “APP” are to be rewritten and forwarded to the IBM DB2 database. Queries originating from other applications may be routed to other database servers, e.g. Oracle DB.


A single XACML policy can be applied to multiple database objects regardless of their location (e.g. server or database instance), providing visibility and enabling centralized policy management. Changes to the policy take immediate effect without the need to refactor complex PL/SQL, SQL PL or equivalent function definitions. Also, database objects can be copied or moved between databases, for instance between an Oracle database and a DB2 database. Adjustments may be required to the resource model but not to the policy.

Version 1.1 of ADAF MD, launched early February 2015, introduced support for IBM DB2, adding to the existing supported databases – Oracle DB and SQL Server. For a deeper explanation and demonstration of ADAF MD in action you may want to take a look at this Axiomatics webinar.


Originally ‘scientia potentia est’ in Thomas Hobbes (1668). Leviathan. Latin edition. 

Related Articles

The one with all the authorization vendors | Dynamically Speaking
Dynamically Speaking
Does it feel as though everyone’s suddenly talking about authorization? We think so too and asked Axiomatics President & CCO Jim Barkdoll his thoughts on...
The one about identity-first security | Dynamically Speaking
Dynamically Speaking
Few have their finger on the pulse of all things Identity and Access Management (IAM) like Jackson Shaw, Chief Strategy Officer for Clear Skye. In...
The Log4j vulnerability – What you need to know
Customer Alerts
As many of you know, on December 9, 2021 the Apache Log4j vulnerability (CVE-2021-4422) was discovered, affecting somewhere between 0 and 3 billion-plus devices currently...