Oracle BI EE 10.1.3.4.1 – Solutions – Puzzle 4

Venkatakrishnan J -

The 4th puzzle in this series was a reasonably simple one which basically demonstrates a capability that allows end users to bypass the security applied in the BI Server layer. There are 3 possible solutions for this

Solution 1: Using Evaluate

EVALUATE was introduced in the 10.1.3.3.1 version of BI EE, that allows end users to call database functions directly. This feature has a lot of uses and actually is widely used. One such use case (others might term this as a bug) is its ability to call table columns that are not in the repository, by using native database functions. For example, the report shown below is a very simple one where only dimensional attributes CHANNEL_CLASS and CHANNEL_TOTAL exist in the report

The SQL for this report is give below

select distinct T4167.CHANNEL_TOTAL as c1,
     T4167.CHANNEL_CLASS as c2
from
     CHANNELS T4167
order by c1, c2

In our case CHANNEL_DESC column is completely not available for reporting as that is blocked by Security in the presentation layer as shown below

To bypass this, in one of the columns of the above report, use an EVALUATE function shown below

EVALUATE('DECODE(%1,''Dummy'',NULL,CHANNEL_DESC)' AS CHAR(10),Dim.CHANNEL_CLASS)

This will basically bypass the security of the BI Server and will provide a means of looking at the CHANNEL_DESC column directly. The report and the SQL are given below

select distinct T4167.CHANNEL_TOTAL as c1,
     T4167.CHANNEL_CLASS as c2,
     DECODE(T4167.CHANNEL_CLASS,'Dummy',NULL,CHANNEL_DESC) as c3
from
     CHANNELS T4167
order by c1, c2, c3

So far so good. Now, lets try adding a measure to this report. You will notice that this will start producing an OCI error.

The reason is since CHANNEL_DESC was not part of the report directly, BI Server did not include that as part of the Select or the Group By clause. The wrong SQL is given below

select T4167.CHANNEL_TOTAL as c1,
     T4167.CHANNEL_CLASS as c2,
     DECODE(T4167.CHANNEL_CLASS,'Dummy',NULL,CHANNEL_DESC) as c3,
     sum(1) as c4
from
     CHANNELS T4167
group by T4167.CHANNEL_CLASS, T4167.CHANNEL_TOTAL
order by c1, c2, c3

Now, to make this work even when a fact attribute is included, the only option is to somehow push this column inside an Aggregate function like SUM, MAX etc since we cannot make the BI Server to generate the Group By (for the secured column). This requires a non-secure dimensional attribute at the same grain as the secured column. In our case, both CHANNEL_CLASS and CHANNEL_DESC have one to one relation and hence i will use that itself to demonstrate. The idea is to encapsulate the EVALUATE expression inside a string aggregate function like MAX etc as shown below

MAX(EVALUATE('DECODE(%1,''Dummy'',NULL,CHANNEL_DESC)' AS CHAR(10),Dim.CHANNEL_CLASS))

select T4167.CHANNEL_TOTAL as c1,
     T4167.CHANNEL_CLASS as c2,
     max(DECODE(T4167.CHANNEL_CLASS,'Dummy',NULL,CHANNEL_DESC)) as c3,
     sum(1) as c4
from
     CHANNELS T4167
group by T4167.CHANNEL_CLASS, T4167.CHANNEL_TOTAL
order by c1, c2

How do we disable this ability completely as this might be considered as a security bug? Ideally i would like to have a privilege in Answers, that can basically stop the use of EVALUATE functions thereby providing us with the capability of controlling who has access to this. But since this is not currently available, the easiest approach is to make sure that all your query columns are pushed into a sub-query. There are a multiple ways we can push all the columns to a sub-query. I will list them below

  1. Using Logical Columns

  2. Using Level Based Measures

  3. Using SELECT based tables

There are other techniques as well. But for the sake of keeping this brief, i will show how all columns can be pushed into Sub-Queries using SELECT tables (instead of the normal tables obtained through Import). The idea is to use a simple table based on SELECT as shown below

instead of the normal imported table. Then we cannot use EVALUATE as the EVALUATE function can be pushed only to the sub-query. For example, the SQL for the same report above, using SELECT table instead of normal table is given below

select distinct T5419.CHANNEL_TOTAL as c1,
     T5419.CHANNEL_CLASS as c2
from
     (SELECT
CHANNEL_CLASS,
CHANNEL_CLASS_ID,
CHANNEL_ID,
CHANNEL_TOTAL,
CHANNEL_TOTAL_ID
FROM
CHANNELS) T5419
order by c1, c2

As you see, whatever function we apply in the front-end will always be pushed only to the outer query (inner sub-query will always remain the same which is for the SELECT table). Hence other than the columns in the Select sub-Query, EVALUATE cannot get external columns residing in the actual table (CHANNEL_DESC for example). If we try using the same EVALUATE function we will get an error. But this approach has potential performance issues since for every query, depending on the database, predicates might not get pushed from outer query to inner sub-query(within the optimizer) thereby causing performance issues.

People who answered this correctly: Craig, Anu

Solution 2: Using BYPASS_NQS authentication

This is not something that is normally used. But this is another important example where when the authentication model gets changed, the entire BI EE security can be bypassed. This security model allows any user to login to BI EE. But only database users will be allowed to report out of the databases(login to BI EE with the same username/password as the database). When this is done, any security that is applied at the column level will be bypassed and hence everyone can see the secured column data as well

Solution 3: Impersonation & Proxy Authentication

For this solution to work, one needs to have the privilege to do proxy authentication. Also, one cannot call this exactly as a security bypass since the user requires the capability to proxy in as another user. But there are cases when logged in as a single user, to look at the actual report values (during report development) we might want to login as Administrator which will essentially bypass all the security that is applied at the column level in BI Administrator. For more details on Proxy Authentication refer my blog post here.

The 5th Puzzle in this series to follow later this week.