Security filters can be difficult beasts at the best of times, especially when it comes to the interaction of multiple filter rows / multiple filters (I glaze over on reading the DBAG statement that “a filter that defines a more detailed dimension combination list takes precedence over a filter with less detail”). In this post I’m going to discuss a particularly confusing behavior involving the interaction of calculation privileges with filter access. I’m not the first to discover or comment on the following phenomenon, but I don’t think it’s been written up comprehensively. The DBAG entry for filters certainly doesn’t make any mention of it, which seems like an oversight.

Read-Access Filters

First, I’ll create a simple read-access filter on Sample.Basic, and assign the filter to user ‘Test1’.

create filter Sample.Basic.ReadEast read on '@DESCENDANTS("East")';
grant filter Sample.Basic.ReadEast to user Test1;

Although I generally prefer to use MDX to query Essbase for this type of exercise, I do appreciate Smart View’s cell styles feature which visually identifies intersections with different levels of access. In these examples, I’ll use the orange color to show read-access cells, and green to show write-access cells.  Here’s a retrieve performed by user Test1:

As expected – read-access to @DESCENDANTS(“East”), and #No Access everywhere else. To put it another way, the ‘background’ access level for a user granted a read-access filter  is #No Access.

Write-Access Filters

Now I’ll try the exercise again, with a write-access filter¹. I’ll revoke the originally granted filter first – not strictly necessary, but makes absolutely clear that the ReadEast filter no longer applies.

alter user Test1 revoke filter Sample.Basic.ReadEast;
create filter Sample.Basic.WriteEast write on '@DESCENDANTS("East")';
grant filter Sample.Basic.WriteEast to user Test1;

Perform the retrieve:

Notice that Test1 now has write-access (indicated by the green cells) to @DESCENDANTS(“East”), but the background access level to the remainder of the cube is still #No Access.

Add a Calculation

When a user has write-access to a database, it’s very common to require calculation access too. I’ll create a very simple calculation script, and grant execute privileges to Test1:

create calculation Sample.Basic.Test1 'CALC ALL;';
grant execute Sample.Basic.Test1 to user Test1;

Now I’ll re-run my read-access and write-access filter tests:

alter user Test1 revoke filter Sample.Basic.WriteEast;
grant filter Sample.Basic.ReadEast to user Test1;

As soon as the user is granted execute privileges on a calc in the database, the background access level is changed to write, and the region specified by the read filter is now more restrictive (read-only)! For completeness, I’ll test again with the ‘write’ filter:

alter user Test1 revoke filter Sample.Basic.ReadEast;
grant filter Sample.Basic.WriteEast to user Test1;

The write filter achieves nothing in this scenario, since Test1’s background access level already permits writing to every cell in the database.

What I find especially nasty about this behavior is that calculation execute privileges might be granted to a user long after the filters have been defined and tested successfully. Granting execute privileges on a specific calculation doesn’t remove filter associations, and there’s no intuitive link between granting access to one or more calculations scripts and modifying a user’s cell-level access.

What’s the Solution?

Fortunately, there is a simple solution; explicitly make the background access level for the entire cube #No Access, by adding an additional row to each filter. It doesn’t actually matter which dimension is selected for the NONE row; the slice defined covers the entire database.  I’d suggest choosing the smallest sparse dimension, if only because my instinct is that this will have the smallest performance impact:

alter user Test1 revoke filter Sample.Basic.WriteEast;
create filter Sample.Basic.ReadEast none on '@IDESCENDANTS("Product")', read on '@DESCENDANTS("East")';
grant filter Sample.Basic.ReadEast to user Test1;

My recommendation is that the NONE line be considered mandatory when filters are created, because it removes the filter’s vulnerability to unexpectedly changing behavior with the addition (or removal) of execute calculation privileges. This could easily be done by an unsuspecting administrator, years down the line. I would be less bothered by this possibility if it didn’t have the potential to grant write access.

There’s a good earlier discussion of the problem here on Network54. When I ran into it recently, I remembered the thread, but (worryingly) didn’t remember that it was me that had the problem the first time around…