Kumar Kambam’s OBIEE Blog

Intelligence on Oracle Business Intelligence

OBIEE Data Security – Column Level Security

Oracle BI aka OBIEE offers a wide variety of data security, of which column level security is a flavor. Consider a column that has sensitive information like Social Security Number. This information should not be presented to all in the organization except the select few who need to have this info. You can actually hide the column in the presentation layer from others. You can use this column in reports on dashboards and people with access to this column will see it in report for others the report will not show this column. To achieve this functionality we need to make changes in two places – Metadata and one time change in NQSConfig.ini.

 

Let us first see what needs to be done in Metadata. For this example, let us consider that EmployeeID is a scared piece of information that a select few MegaUsers can see and access.

 

In Metadata on the presentation column, we need to make changes to permission settings. Right click on the column and select “Properties”

 

By default, the columns will have read access to everyone.

 

Choose the groups that should have read access rights on the column. The check box here works like a toggle button. Click on it to get a check mark or a red cross mark. A red cross marks explicitly restricts access. While an unchecked/black check box implicitly restricts access to the column.

Now let us log in as a Mega User (Kumar.Kambam, in this case) and create a report using the EmployeeID Column.

 

In answers, Kumar.Kambam can see the EmployeeID Column. Let us create a simple report using the column with column level security enforced.

 

 

Save it and put it on a dashboard to test OBIEE column level security.

 

 

Now log on as Basic User

In answers check for EmployeeID column. The column is not visible. This is due to the column level security restriction.

 

Go to OBIEE Security Dashboard to see the report, and we get an error message. State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 27005] Unresolved column: “Employees”.”EmployeeID”. (HY000)

The error is expected. The report contains a column on which the current user does not have access to. So for all practical purposes as far as OBIEE is concerned, the column does not even exist. The default setting in NQSConfig.ini file drives this behavior.

In, NQSConfig.ini change the parameter PROJECT_INACCESSIBLE_COLUMN_AS_NULL which is under security section. By default it is set to No. Set it to yes. And restart the services.

 

Now logged in as BasicUser1 and access OBIEE Security Dashboard to test OBIEE column level security

The report is presented without the EmployeeID column on which OBIEE column level security was enforced.

So for the same dashboard report, depending on the data level security access permissions in OBIEE for a user, a column visibility can be controlled using column level security feature.

 

 

 

Advertisements

January 15, 2009 - Posted by | OBIEE Security | , , , , ,

20 Comments »

  1. Hi Kumar,
    I was aware of using the CHOOSE function to avoid the error message (http://obiee101.blogspot.com/2008/09/obiee-choose-statement.html), but this is a more elegant solution in my opinion.

    Regards,
    Stijn

    Comment by Stijn Gabriels | January 15, 2009

  2. Hi Kumar,

    I have recently started working with OBIEE. Found this article very useful. Keep posting.

    Comment by Nachammai | January 16, 2009

  3. Awesome Kumar…..Thanks alot

    Comment by imran | February 1, 2009

  4. Hello Kumar,

    Thanks for blog on column security. I find it very usefull. It works as you outlined.

    I took a step further and created a prompt on the secured column. The prompt works for user who has access to column.
    However it fails with “Error retrieving choices.”

    How this can be addressed? Any thought?

    Thank you
    Shivaji

    Comment by Shivaji | February 10, 2009

  5. I have started working on OBIEE since a week.This information is very much helpful.

    Thanks

    Comment by Jyothi | February 11, 2009

  6. Hi,
    Perfect material for column level security.

    Comment by lankish | March 1, 2009

  7. Hey Kumar

    I have a concern here.. I tried to implement this. But it is displaying the hided attribute with null values. It is actually not hiding the attribute from the report. May I kno why is it so.?

    Comment by Akash | May 6, 2009

  8. Kumar, Thanks for the useful and well-illustrated article.

    Comment by BI Enthusiast | May 11, 2009

  9. Its a wonderfull content KUmar am thankfull to you for this

    I implemented this in my project.

    Thanks
    ThePrashaant 🙂

    Comment by Prashaant Pandey | June 26, 2009

  10. Kumar, do you know how to provide similar security on the dashboard pages so only certain users are allowed access to certain pages on a dashboard?

    Comment by Bill C | July 6, 2009

  11. hi kumar,
    its really a very nice blog!!!
    thanks!!!

    i need a favour from u…
    actually i have a situation…how can a user can change the log level value dynamically in the answers or in dashboards?

    my personal mail id is: shankar0503@gmail.com.
    looking forward to your response.
    thanks in advance…

    Regards,
    shankar

    Comment by shankar | August 3, 2009

  12. Bill,

    Yes you can give a group of users or a user access or deny access in catalog manager.

    Kumar

    Comment by Kumar Kambam | August 4, 2009

  13. Hi Kumar,

    I Read your all blogs,Each topic is very much impressing topics…and i am fan of your blogs,I have a small request to you can you please send me the major difference between Physical Layer and BMM Layer,What kind of joins we will use in Physical layer and BMM layer and why those joins?

    Thanks in Advance
    Chi(Hong Kong)

    Comment by Chi | August 9, 2009

  14. Physical Layer –> Foreign Key JOin.
    Business Layer –> Complex Join.

    Hari KV
    http://nerdsofobiee.wordpress.com

    Comment by Hari KV | October 7, 2009

  15. Hi Bill

    You can use the Presentation Layer Administrator for disabling/enabling certain tabs of the dashboard to certain users. Pretty soon, I will create a nice step by step guide for users like you on my blog regarding this.

    Hari KV
    http://nerdsofobiee.wordpress.com

    Comment by Hari KV | October 7, 2009

  16. Hi Shankar
    What we have done in my recent implementation is have an admin page where there is write back functionality and any user who wants their log level changed will call them and ask for this. I will post the step by step guide on my blog pretty soon.

    Reason for not dynamically changing is because we dont want every user to raise their log levels unless needed. I can see this being useful in development phase but going to a dashboard page with writeback will be good enough.

    Hari KV
    http://nerdsofabiee.wordpress.com

    Comment by Hari KV | October 7, 2009

  17. good articles ..keep on posting,….

    Comment by nagendra | November 16, 2009

  18. Kumar,

    Thanks for the tip – just what I was looking for. What do you use for screen capture and manipulation for this post ? looks good!

    Thanks,

    Dan

    Comment by Dan | February 15, 2010

  19. Thanks. I use SnagIt.

    Comment by Kumar Kambam | March 4, 2010

  20. Really excellent. Got a right solution

    Comment by praveen | September 9, 2010


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: