Oracle BI EE 10.1.3.4.1 – Puzzlers – Puzzle 4 – Bypassing Security

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

in