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

Joffry Ferrater   ·   Wednesday, June 8th, 2016

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.

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

And the status table.

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.

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.