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.
Posts
