OracleBIBlog Search

Thursday, January 15, 2009

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”

clip_image002

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

clip_image004

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.

clip_image006

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

clip_image008

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

clip_image009

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

clip_image010

Now log on as Basic User

clip_image011

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

clip_image013

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)

clip_image014

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.

clip_image016

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

clip_image018

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.

0 comments: