Implementing data-based user access rules

Some of our customers asked how to implement access rules (for dimensions and keys) based on data stored in their database. This is useful e.g. for sales information systems where sales representives only may see customers of the region they are responsible for and if this representive/region relationship is stored in the database.

[toc]

This article describes how to implement such access rules in two steps for the customer / region example.

Step 1: Add a user dimension to your model

Before you can use the user information in access rules, you must make them available inside the model. The only ways to do this is to load the information into a dimension. Use normal SQL-KeyLoader to access the user-database and to load the user information. There are two basic rules when creating a user dimension:

• The dimension must at least contain the exact user-IDs at keys. The user keys can be loaded to the top-level or they can be grouped (e.g. to users groups), but they must be inside the dimension and contain the login-IDs.
• All IDs for keys from other dimension to which the user should have access to must be stored as attributes in the user-keys. E.g. the list of all region IDs could be loaded as attribute „regions“. Later, you can use this attribute to match its values with the region-keys.

Step 2: Add access rules to your other dimensions

Now you can add access rules to your other dimension by referencing the current user-key and its attributes. Switch to the access rule editor by selecting the „Access“ tabulator and start adding the rules to the dimension, e.g. to this „Customer“ dimension.

The first part of your access rule has to find the current user key in the „User“ dimension, because the user key is not automatically selected when the user executes a query. You could select the user in the query itself (in its „Filter“ property) or you can use the FIND function in the access rules. The last solution is the better one because in this case you does not need to change every query (and you cannot forget this in future queries). The expression to find the current user is:

FIND( User, USER() )

where „User“ is the name of your user dimension (you need to change it if your user-dimension has a different name). The function USER returns the name of the current user (his login). Because we search with the users login-name, it must exactly match the ID of the key in your dimension.

After you located the user key, you can use its attributes. E.g. if the user key has an attribute „regions“, which contains alle region id the user should have access to, the expression

FIND( User, USER() ).regions

will return the region codes. The result can contain no, one or multiple values (of course, a sales representive could be more responsible for more than one region).

Now the last thing is to match this region codes with the customer dimension. It is important to use relative expressions here and to match the REGION level (!) with the attribute, not the keys of the Customer dimension. E.g. the expression

{REGION} IN FIND( User, USER() ).regions

would convert the region name of the current customer, region (or whatever currently is selected) into a string and test its occurence in the region list of the user. Because REGION is a relative expression, this works for all levels of dimension: A customer is only visible if it belongs to a listed region, the regions itself is only visible if listed, and the root key of the Customer dimesion is only visible if one of the regions is listed.

Of course, you could also match the lowest level or match more than one level, but in most cases this is the typical expression we use for access restrictions. Sometimes it is neccesary to use dimension links – e.g. if the customer and region are stored in different dimensions, you would have to change the expression into something like:

{CUSTOMER.region} IN FIND( User, USER() ).regions

This only works, if the customer and region dimension are linked.

The last step is to add access rules for administrators or CEOs, which usually are allowed to see all keys of all dimensions. This can be done by a role-check in an additional access rule. The simple expression

HASROLE( 'iolapAdmin' )

would grant access to all keys for a user owning the role „iolapAdmin“. The expression always returns „true“ if the user own this role, independend on the keys.