+

Is It a Good Practice to Use SQL Views for Policy Information Points?

In order to better support the configuration of an Axiomatics solution (APS, ARQ, ADAF MD…) the Axiomatics Professional Services team suggest the use of a database view. To someone who is setting up an ABAC (Attribute Based Access Control) solution and who is not necessarily a database expert, this may create doubts or uncertainty: What is it and is it a good practice to use SQL views for PIPs? In this week’s Q&A we are going to explain what a database view is, how it’s used by our products, and why it’s a good thing.

What is a View?

Relating to SQL and database theory, a ‘view’ constitutes a virtual table, dynamically created as the result of a stored database query. As such it provides a “single table look and feel” – even when the underlying physical schema and operations to support it (i.e. joining multiple tables and performing multipart calculations) are complex. Through the use of views, working with data becomes easier, but also safer because the view can limit the exposure of data. There is a lot more to it than that, but this will suffice in order to support the rest of the discussion. Please see Wikipedia or your database vendors’ support or knowledge base for more general and database specific information about views.

ABAC & Policy Information Points

In ABAC as in the Axiomatics solutions, attributes can be dynamically retrieved from information systems in order to support the access decision. For example while some attributes are contained and available to the authorization service (PDP) through the access request, access control policy evaluation may need to be supported by additional attributes such as a record’s department, classification, owner, and status. If this information has been entered into an existing information system such as a database then the authorization service will retrieve it. But depending how it exists in the database, it may be beneficial and sometimes even necessary, to introduce a database view.

PIPs Inside Axiomatics

Axiomatics provides several ready-made attribute connectors (PIPs) with its Policy Server (APS and APS EE) and Data Access Filter (ADAF MD) products. Two of these connectors provide means of database attribute resolution.

When configuring the SQL Attribute Connector, it may be convenient (though not mandatory) to define views in the database in order to simplify the SQL statement that is used by the connector. Consider that there could be several attributes that need to be retrieved from the database using more or less the same methods. In that case it would be good practice to write the common logic into a view – and let each Attribute Connector configuration define the specifics for each attribute.

By contrast, when configuring the Table Attribute Connector, then the attribute data must comply with the requirements imposed by the Table connector model, namely that there must be one column for each key attribute and one column for the attribute value (e.g. resource id and resource location). If the physical database schema is not in that format, then it is necessary (mandatory) to define a view.

Note: This usually happens when you need to do a join of at least two tables in order to get from the key values to the attribute values.

Example

Imagine for an instant that we keep information about records in a single table e.g.

Record

ID: int, PRIMARY KEY

Name: VARCHAR(255)

Description: VARCHAR(255)

Content: BLOB

We also have another 2 tables that contain ownership information and status information.

Ownership

RECORD_ID: int, FOREIGN KEY(Record.ID)

USER_ID: VARCHAR(255), FOREIGN KEY(Users.ID)

And the status table.

Status

RECORD_ID: int, PRIMARY KEY, FOREIGN KEY(Record.ID)

Status: VARCHAR(255)

The resulting view would merge the three tables to give the single table look-and-feel as well as get rid of unnecessary columns e.g. the content column which is not needed by the PIP.

Record

ID: int, PRIMARY KEY

Name: VARCHAR(255)

Description: VARCHAR(255)

Owner: VARCHAR(255)

Status: VARCHAR(255)

Conclusion

In conclusion, the use of database views have general as well as Axiomatics specific benefits: by configuring attribute retrieval based on a view, a clean and consistent format or syntax is used in attribute connectors. This format is both easier to configure to (add to) for the configuration engineer, but also easier to understand from a configuration review or audit standpoint. The use of a view may also be required when configuring the Table Attribute Connector. As such Axiomatics would call it not just a good practice, but perhaps a ‘best’ practice. 

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