Oracle BI EE 10.1.3.4.1 – Puzzlers – Puzzle 4 – Bypassing Security
February 28th, 2010 by Venkatakrishnan J
On to the 4th puzzle in this Puzzle series. This is a very interesting Puzzle at least from the perspective of BI EE Security. We all know that BI EE provides comprehensive security within the repository. For example, the screenshot below shows that the column CHANNEL_DESC can be accessed only by the exec user.
Now when we log in as any user(users who do not belong to Administrators group) and open the report containing this secured column, we will either be getting an error or will be shown as NULL in reports depending on the PROJECT_INACCESSIBLE_COLUMN_AS_NULL property in the NQSConfig.ini. The question is how do we bypass the security and still show the CHANNEL_DESC column(with their values of course) in reports. I know this sounds a bit malicious and can even be read as a security hack, but this is currently possible in BI EE(in the solution i will let you know how to use a workaround to disable this though). The question or the Puzzle today is how do we achieve this. There are some potential use cases of this
1. If you do not have access to the repository but still want to look at certain security protected columns to validate certain reports
2. If you do not have a column in the repository at all (but exists in the database) and you still want to access it.
Remember this is not about enabling Direct Database Requests as in most cases that will always be disabled. For example, if you look at the report below, CHANNEL_DESC has become null for the user i have logged in as this user does not have access to this column(PROJECT_INACCESSIBLE_COLUMN_AS_NULLÂ is set to YES in my case).
If you look at the SQL, you will notice that the CHANNEL_DESC column is not even pushed back to the database.
WITH
SAWITH0 AS (select sum(1) as c1,
T4167.CHANNEL_ID as c2
from
CHANNELS T4167
group by T4167.CHANNEL_ID)
select distinct SAWITH0.c2 as c1,
cast(NULL as VARCHAR ( 1 ) ) as c2,
SAWITH0.c1 as c3
from
SAWITH0
order by c1, c2
which is good and as expected. Now, the puzzle is to somehow bypass this security and display the CHANNEL_DESC column as shown below.
Remember, there are 2 pre-conditions to this
1. No Direct Database Requests
2. No changing the repository to add a new column in the presentation layer
March 1st, 2010 at 12:10 pm
Hi ,
4th puzzle in this Puzzle series. Bypassing Security
My guess is we can bypass security by running iBto for mentioned report on behalf of (Run As)’exec’.
Thanks,
Harsh
March 1st, 2010 at 7:06 pm
I believe that this can be done using the ‘Evaluate’ function and using a user defined database function. Assuming that the user has access to the Channel ID column, this ID can be passed to the database function to retrieve the description column.
March 1st, 2010 at 8:25 pm
Does “evaluate” count as a Direct Database Request? If not, you can “evaluate” that column:
evaluate(’channel_desc /* %1 */’, Channels.CHANNEL_ID)
This is documented well at http://everythingoracle.com/obieeeslc.htm
March 2nd, 2010 at 4:41 am
Craig/Anu – Thanks for participating. Evaluate is one of the Answers, yes. But there are 2 other solutions as well. Also Evaluate cannot be applied in all situations & one can negate it by BI Server Modeling. The link above shows a very simple example containing only dimension attributes. What if a fact attribute is thrown in the mix (like my screenshot above)? Evaluate will fail in that case. How will you negate it then? But your solution approach is correct and can you try to answer the other 2 possible solutions as well.
March 5th, 2010 at 5:24 pm
Well I am stumped.. I look forward to you posting the other 2 solutions and the next puzzle!