The purpose of this document is to explain what we consider to be a gaping hole in the security capability of Essbase Studio which makes it quite impractical for consideration in any real enterprise applications. I will explain why this is a fundamental oversight.
The Essential Problem
Oracle Hyperion Essbase, as the premier multidimensional database establishes itself as head and shoulders above relational solutions for many reasons. One of the most important reason, and why it excels in financial applications, is because its security model is so powerful. One can, if desired, restrict users to see any specifiable set of cells in the cube through the power of multidimensionally aware filters. These filter definitions have been expanded over the years to include the powerful meta_read facility that even restricts users from knowing which parts of the cube they don’t have access to. Relational database security, by comparison, is crude.
So with that high standard of security modeling in mind, I have put together a design for a significant Oracle customer which expands their user’s view into detail data by using Essbase Studio and offering several drill through reports. I presumed that the Oracle security model would somehow extend beyond Essbase through Essbase Studio’s drill through facility. It does not. In fact, there is essentially no way to secure drill through.
From a user’s perspective, all of the aggregate data in the cube is very useful, and the detail below is useful as well. For this application, which will serve on the order of 300 users nation wide, it represents the single version of the financial truth. They will be assigned security on a need-to-know basis, and we expect there to be somewhere around 30 to 50 different access categories. The users see Essbase and Drill Through as a single application, as they should. They will access it through SmartView, which they love. It’s all Hyperion branded product and it should all work together. However when the details, for example, of sensitive expenses cannot be secured, it throws into question the entire value of Essbase Studio.
The Key Requirement
For our design we have a singularly important requirement. Our drill through reports must provide auditability of detail over broad selections. Our users don’t want to just look at the journal entries for one account, but for several accounts. Our users don’t want to check the P.O.s for one vendor, but for several vendors. So the key requirement has arisen that the drill through reports should allow a user to select a dimension at a high level and bring back all of the level zero detail. We don’t expect to bring back just 10 rows for a single account or department, but hundreds and even thousands of rows.
So our key requirement is that custom SQL code for the drill through be constructed such that any arbitrary combination of parents in any of several dimensions may be selected and all detail below those parents are brought back. Here’s a two dimensional example.
The director of a sales business unit that includes several departments wants to view the detail of all Travel & Expense accounts. This includes two dimensions, Organization and Account. The secured dimension in this case is Organization and the Business Unit director has access to a total of 12 departments. He also has access to all accounts in that dimension and ‘Travel & Expense’ is also an aggregate of a dozen lower level accounts.
The sales director is given permission to see only data in his business unit and below. In this case Texas. His 12 departments correspond to 12 cities and regions in Texas. All of this security can be very easily accomplished in Essbase with the following filter.
meta_read @IDESCENDANTS("Texas")
Thus the Texas sales director cannot see any account data above Texas and all account data at and below the Texas level in the cube. But when it comes to drill through, all of that changes.
The Details
Drill though reports are defined in Essbase Studio in three ways. The first is by dimensional context, the second is by SQL code, and the third is by model association. The dimensional context tells the system when the user can drill through, the SQL code determines what data will be brought back, and the model association tells the system which Essbase cubes are enabled for the report. In dealing with our security issue, the dimensional context is key.
For our drill through we may have six, seven or more dimension contexts defined. A typical set of contexts for a drill through would be Organization, Account, Product, Project, Period, Year & Source. In the design we have specified both Fixed and Variable Contexts. The Fixed Context is one in which the specific dimension selection works like a filter. Select June, for example and you get only June data in the retrieval. For our purposes, Organization is a Variable Context. Pick Texas and you get Texas and all of the children of Texas. We have created special logic tables on the back end to facilitate this feature and we have prototyped several other solutions to the matter of Variable Contexts. All perform well.
The problem with security manifests itself in the following manner.
We have secured the Organization dimension and there are several other levels of access granted. For example the Regional Sales Director can see all of the Southwestern states including AZ, OK, NM, & CO. This requires, essentially that we allow users to drill down from any level in that dimension. But the problem exists as long as there is more than one level of access granted in any secured variable context dimension.
The Texas director navigates freely in the cube via SmartView. At organization levels higher than Texas, he can only see ‘#NoAccess’ in the displayed cells. However, Essbase Studio’s dimensional context definition for the drill through report allows him to select a cell that says ‘#NoAccess’ and execute that report!
That means he can select ‘Southwest’ and the drill through report will execute the variable context and show all the detailed information for TX, AZ, OK, NM & CO. In fact, there is nothing stopping this user from selecting even higher in the organization dimension and seeing everything.
No access means no access in Essbase. To Essbase Studio drill through it means nothing. As long as there is a legal context, the drill through report will execute regardless of what Essbase security says.
Failed Workarounds
We have tried several different ways around this problem.
So essentially we are put in the position of communicating to our customer that even though this is an Essbase application, security cannot be enforced.
It appears to us that there is a relatively simple fix somewhere in the product stack. Clearly SmartView is aware, and making judgments in real-time about the legality of contexts. We have observed that selecting any data cell after an Essbase retrieval will show the availability of none, one or more drill through reports. So we see that SmartView is plenty smart enough to block access to drill through. We’re just absolutely shocked that it doesn’t.
We hope that Oracle will find a way to overcome this gaping security hole in Essbase Studio / Essbase / APS / SmartView. It’s one application. It should work with one security model.
The Essential Problem
Oracle Hyperion Essbase, as the premier multidimensional database establishes itself as head and shoulders above relational solutions for many reasons. One of the most important reason, and why it excels in financial applications, is because its security model is so powerful. One can, if desired, restrict users to see any specifiable set of cells in the cube through the power of multidimensionally aware filters. These filter definitions have been expanded over the years to include the powerful meta_read facility that even restricts users from knowing which parts of the cube they don’t have access to. Relational database security, by comparison, is crude.
So with that high standard of security modeling in mind, I have put together a design for a significant Oracle customer which expands their user’s view into detail data by using Essbase Studio and offering several drill through reports. I presumed that the Oracle security model would somehow extend beyond Essbase through Essbase Studio’s drill through facility. It does not. In fact, there is essentially no way to secure drill through.
From a user’s perspective, all of the aggregate data in the cube is very useful, and the detail below is useful as well. For this application, which will serve on the order of 300 users nation wide, it represents the single version of the financial truth. They will be assigned security on a need-to-know basis, and we expect there to be somewhere around 30 to 50 different access categories. The users see Essbase and Drill Through as a single application, as they should. They will access it through SmartView, which they love. It’s all Hyperion branded product and it should all work together. However when the details, for example, of sensitive expenses cannot be secured, it throws into question the entire value of Essbase Studio.
The Key Requirement
For our design we have a singularly important requirement. Our drill through reports must provide auditability of detail over broad selections. Our users don’t want to just look at the journal entries for one account, but for several accounts. Our users don’t want to check the P.O.s for one vendor, but for several vendors. So the key requirement has arisen that the drill through reports should allow a user to select a dimension at a high level and bring back all of the level zero detail. We don’t expect to bring back just 10 rows for a single account or department, but hundreds and even thousands of rows.
So our key requirement is that custom SQL code for the drill through be constructed such that any arbitrary combination of parents in any of several dimensions may be selected and all detail below those parents are brought back. Here’s a two dimensional example.
The director of a sales business unit that includes several departments wants to view the detail of all Travel & Expense accounts. This includes two dimensions, Organization and Account. The secured dimension in this case is Organization and the Business Unit director has access to a total of 12 departments. He also has access to all accounts in that dimension and ‘Travel & Expense’ is also an aggregate of a dozen lower level accounts.
The sales director is given permission to see only data in his business unit and below. In this case Texas. His 12 departments correspond to 12 cities and regions in Texas. All of this security can be very easily accomplished in Essbase with the following filter.
meta_read @IDESCENDANTS("Texas")
Thus the Texas sales director cannot see any account data above Texas and all account data at and below the Texas level in the cube. But when it comes to drill through, all of that changes.
The Details
Drill though reports are defined in Essbase Studio in three ways. The first is by dimensional context, the second is by SQL code, and the third is by model association. The dimensional context tells the system when the user can drill through, the SQL code determines what data will be brought back, and the model association tells the system which Essbase cubes are enabled for the report. In dealing with our security issue, the dimensional context is key.
For our drill through we may have six, seven or more dimension contexts defined. A typical set of contexts for a drill through would be Organization, Account, Product, Project, Period, Year & Source. In the design we have specified both Fixed and Variable Contexts. The Fixed Context is one in which the specific dimension selection works like a filter. Select June, for example and you get only June data in the retrieval. For our purposes, Organization is a Variable Context. Pick Texas and you get Texas and all of the children of Texas. We have created special logic tables on the back end to facilitate this feature and we have prototyped several other solutions to the matter of Variable Contexts. All perform well.
The problem with security manifests itself in the following manner.
We have secured the Organization dimension and there are several other levels of access granted. For example the Regional Sales Director can see all of the Southwestern states including AZ, OK, NM, & CO. This requires, essentially that we allow users to drill down from any level in that dimension. But the problem exists as long as there is more than one level of access granted in any secured variable context dimension.
The Texas director navigates freely in the cube via SmartView. At organization levels higher than Texas, he can only see ‘#NoAccess’ in the displayed cells. However, Essbase Studio’s dimensional context definition for the drill through report allows him to select a cell that says ‘#NoAccess’ and execute that report!
That means he can select ‘Southwest’ and the drill through report will execute the variable context and show all the detailed information for TX, AZ, OK, NM & CO. In fact, there is nothing stopping this user from selecting even higher in the organization dimension and seeing everything.
No access means no access in Essbase. To Essbase Studio drill through it means nothing. As long as there is a legal context, the drill through report will execute regardless of what Essbase security says.
Failed Workarounds
We have tried several different ways around this problem.
- Perhaps we could create a number of different drill through reports and segregate users according to their level of access in the secured hierarchy.
- No. Drill through reports are not items that Shared Services can provision to. The lowest level of access in Essbase Studio grants access to run all drill through reports.
- Perhaps we could identify the cell contents and pass a parameter back to the SQL definition so that when we see #NoAccess, we return no rows.
- No. There is no way to communicate that parameter back to Studio.
- Perhaps we could identify the Essbase user and send pass that back as a parameter back to the SQL definition and attempt to replicate Essbase security in the relational source.
- No. There is no way to communicate that parameter back to Studio.
So essentially we are put in the position of communicating to our customer that even though this is an Essbase application, security cannot be enforced.
It appears to us that there is a relatively simple fix somewhere in the product stack. Clearly SmartView is aware, and making judgments in real-time about the legality of contexts. We have observed that selecting any data cell after an Essbase retrieval will show the availability of none, one or more drill through reports. So we see that SmartView is plenty smart enough to block access to drill through. We’re just absolutely shocked that it doesn’t.
We hope that Oracle will find a way to overcome this gaping security hole in Essbase Studio / Essbase / APS / SmartView. It’s one application. It should work with one security model.
I opened an SR on the issue and the Smartview team took ownership to not allow drill through on intersections that show as missing or no access. I'm not sure when it will be implemented
Posted by: GlennS | July 15, 2011 at 07:50 AM
Well I have seen the future and it works. Matt, Valentina, Radha and the rest have nailed this problem to the wall. It works perfectly.
Posted by: Cobb | August 23, 2011 at 12:06 PM
Fixing this issue at smartview will not help solve it, since users could still use 'old' smartview as a workaround. It should be addressed by Studio or the APS.
Posted by: Theo De Vries | August 29, 2011 at 02:48 AM