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.

OBIEE Cache is enabled, but why is the query

not cached?
Repeatedly customers pose the question – OBIEE cache is enabled, but why is the query
not cached? The reason why the queries are not cached can be of many reasons. Some of
the reasons are:

Non-cacheable SQL function: If a request contains certain SQL functions, OBIEE will
not cache the query. The functions are CURRENT_TIMESTAMP, CURRENT_DATE,
CURRENT_TIME, RAND, POPULATE. OBIEE will also not cache queries that contain
parameter markers.

Non-cacheable Table: Physical tables in the OBIEE repository can be marked ‘non-
cacheable’. If a query makes a reference to a table that has been marked as non-
cacheable, then the results are not cached even if all other tables are marked as cacheable.
Query got a cache hit: In general, if the query gets a cache hit on a previously cached
query, then the results of the current query are not added to the cache. Note: The only
exception is the query hits that are aggregate “roll-up” hits, will be added to the cache if
the nqsconfig.ini parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set
to Yes.

Caching is not configured: Caching is not enabled in NQSConfig.ini file.

Result set too big: The query result set may have too many rows, or may consume too
many bytes. The row-count limitation is controlled by the
MAX_ROWS_PER_CACHE_ENTRY nqsconfig.ini parameter. The default is 100,000
rows. The query result set max-bytes is controlled by the MAX_CACHE_ENTRY_SIZE
nqsconfig.ini parameter. The default value is 1 MB. Note: the 1MB default is fairly small.
Data typically becomes “bigger” when it enters OBIEE. This is primarily due to Unicode
expansion of strings (a 2x or 4x multiplier). In addition to Unicode expansion, rows also
get wider due to : (1) column alignment (typically double-word alignment), (2) nullable
column representation, and (3) pad bytes.
Bad cache configuration: This should be rare, but if the MAX_CACHE_ENTRY_SIZE
parameter is bigger than the DATA_STORAGE_PATHS specified capacity, then nothing
can possibly be added to the cache.

Query execution is cancelled: If the query is cancelled from the presentation server or if
a timeout has occurred, cache is not created.

OBIEE Server is clustered: Only the queries that fall under “Cache Seeding” family are
propagated throughout the cluster. Other queries are stored locally. If a query is generated
using OBIEE Server node 1, the cache is created on OBIEE Server node 1 and is not
propagated to OBIEE Server node 2

OBIEE Security Enforcement – LDAP Authentication

Authentication in OBIEE
Some authentication methods used by Oracle BI server are
 1. Database
2. LDAP
3. Oracle BI server (repository users) – I do not recommend this method for medium
to large implementations. It will be difficult to manage.

I will discuss on setting up LDAP in this article.

Setting up LDAP or Windows ADSI in OBIEE

Microsoft ADSI (Active Directory Service Interface) is Microsoft version of LDAP
server. Most of the steps to setup of either Microsoft ADSI or LDAP server are similar.
In either case, you would need help from your network security group/admin to configure
LDAP. They should provide you with the following information regarding the LDAP
server

1. LDAP server host name

2. LDAP Server port number
3. Base DN
4. Bind DN
5. Bind Password
6. LDAP version
7. Domain identifier, if any
8. User name attribute type (in most cases this is default)

Registering an LDAP server in OBIEE

In Oracle BI repository, go to manage security.
Create a new LDAP server in OBIEE Security Manager

With the help from your network security group/administration, fill out the following
information
Next in the Advanced tab, based on the kind of LDAP server you have and its
configuration, make the necessary changes.

For Microsoft ADSI (Active Directory Service Interface), choose ADSI and for all others
leave it unchecked.

Most of the times, Username attribute would be automatically generated. For Microsoft
ADSI It is sAMAccountName; for most of the LDAP servers it is uid or cn. Check with
your network security group/administrator on what is the username attribute for your
LDAP server. Make a note of the user name attribute you will need it later.

Now we need to create an Authentication initialization block. In administration tool,

under Manage go to Variables.
Under Action, go to New -> Session -> Initialization Block

Configure the session initialization block. Give it a name and click on Edit Data Source.
In the pop up window, choose LDAP from the drop down box and then click on Browse.
You can also configure a LDAP server here by clicking on “New”. In the browse pop up
window choose the LDAP server you would like to use.
Next we need to create variables. User and Email are the common variables normally in
play.
Upon clicking on OK, a warning pops up on the usage of User session variable (User
session variable has a special purpose. Are you sure you want to use this name). Click
yes.

Next enter the LDAP variable for username. sAMAccountName in the case of ADSI as
configured in the LDAP.

Next following similar steps create a variable for Email. In addition, depending on you
need, you can bring additional variables from the LDAP server.
Now bounce your services.

OBIEE Security Enforcement – External Database

Table Authorization
Authentication vs. Authorization
Commonly asked question – What is the difference between authentication and
authorization? Authentication is the process in which a user id and password is verified to
see if the user is a valid user. The process can be compared to logging on to your email or
even your laptop. Once the user logs on, authorization takes care of what components or
data a user can have access to. To read about OBIEE Authentication click here.

Setting up framework for Authorization

Authorization is most commonly handled by using an external table. The following steps
are required after setting up Authentication process:

1. Create a table in the database that would have the Authorization information. If
you already have a table from which associates the UserID/Username with
Groups, you can use that table. If not, create the following table in your database.
CREATE TABLE WC_USER_AUTH

LOGON VARCHAR2(120 BYTE) NOT NULL,

GROUP_NAME VARCHAR2(120 BYTE) NOT NULL,

CREATED_DT DATE DEFAULT SYSDATE

TABLESPACE <your tablespace>

PCTUSED 0

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 64K

MINEXTENTS 1

MAXEXTENTS 2147483645

PCTINCREASE 0

BUFFER_POOL DEFAULT

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;
 CREATE UNIQUE INDEX NDX_LOGON_GROUP ON WC_USER_AUTH

(LOGON, GROUP_NAME)

NOLOGGING

TABLESPACE <your tablespace>

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

INITIAL 64K

MINEXTENTS 1

MAXEXTENTS 2147483645

PCTINCREASE 0

BUFFER_POOL DEFAULT

NOPARALLEL;

2. Now you will have to populate the table with the relevant information. Note that
one user can belong to more than one group.
3. Next, you need to create the groups in the repository. The name of the groups
should be as they are in the table, if you want these groups to drive web and data
security as well. If you have a group called “Power Users” in the table, you would
have to create a group with the exact same name.
1. As a best practice, it is recommended that a separate connection pool is created
for the execution of Authentication and Authorization Initialization blocks.
1. Now create a session initialization block that would read from the table to assign
groups to the user.
1. Configure the session initialization block. Give it a name and click on Edit Data
Source. In the pop up window, choose Database from the drop down box. Write a
SQL statement that would get all the group names of the user that is populated in
the USER variable as part of Authentication. The SQL statement used in this
example is SELECT ‘GROUP’, R.GROUP_NAME FROM WC_USER_AUTH R
WHERE UPPER(R.LOGON)=UPPER(‘:USER’). Choose a connection pool.
1. Now edit the variable target and set it to row wise initialization. What it really
means is, it would assign multiple values to a variable, GROUP variable in this
case. If a user belongs o multiple groups, multiple rows will be returned by the
SQL and this setting would enable GROUP to contain all the values.
1. Now set execution precedence. The authorization process takes place after
authentication process. We are using a variable (USER) that authentication
process is populating.
2. Now create the Catalog Groups in the presentation services. The group names
should match the group names from the table and the repository as in Step 3, if
you want them to drive the web and data security.

Go to Settings -> Manage Presentation Catalog Groups and Users

1. Click on Create a new Catalog Group. In the new window give the name of the
group and as a best practice give it a password.
1. Now when logged I will log in as Kumar Kambam and click on My Account, in
here we can see the Kumar.Kambam ‘s group membership. You can join a
Catalog Group from here.
1. Now that we have established that Power Users group has at least one user as
demonstrated in the Step 12, let us log in as Administrator and go to Power Users
Group properties. Don’t panic if you see the message saying “There are currently
no members in this Group”. Group assignment to a user is done at session level.
When a user logs on and authorization process assigns groups to users. This
assignment of users to a group is valid for that session only. Thus no group
membership information is stored in the presentation services.
 1. One can also create catalog groups in the presentation services and assign users
manually, however it is not recommended to do so.
2. One frequently asked question is – Why cannot I see the comprehensive list of
users and their group memberships in the presentation services?

In this set up, presentation services cannot be used to maintain or see the
comprehensive list of users. A user will appear only after he/she logs on for the
firstime. As far as group assigment goes, it is done on the session level and is
valid for that session only. So we cannot see the group membership information.
Though you can create a catalog group on the presentation services and assign
users manually, it not recommended to do so.

Points to ponder
 1. Authentication and Authorization are two different processes accomplishing
different tasks.
1. Authentication checks valid user and password
2. Authorization assigns security group membership
2. Authorization process is executed after authentication process
3. If you want to control data and web security with the groups defined in the table,
the name of the group should be the same in all the three places – table,
repository, and presentation services
4. The assignment of a user to a group in this case is done at session level and that
information is not stored in the presentation services. Though you can create a
catalog group on the presentation services and assign users manually, it not
recommended doing so.

Simplifying Migration Process – Changing

Environment Specific Variables in RPD

When it comes to migrating repository file between environments (Dev – TEST-Prod),

one of the common questions from OBIEE environment administrators is if there is a way
to change the connection information without having to change them manually in all the
places.

Creating repository variables for DSN and DSN Username solves the issue to an extent.
However, imagine having to deal with changing multiple DSNs and their respective
usernames. Moreover, there is the setting password to the DSN username. One way to
automate the setting of the variables is to store the values in a file and set them via admin
tool command line mode.

First, define repository variables that can be used in the connection pool.
Reference the DSN information in the connection pool.
Now create a control file SetVariables.txt with the environment specific values.

_______________

‘To Open rpd – Open <rpdname> <Administrator User> <administrator password>

Open YourRpd.rpd Administrator SADMIN

‘Setting OLAP DSN variable

SetProperty ”Variable” ”OLAP_DSN” Initializer ” ‘QA_DSN’ ”

‘ Setting OLAP DSN Username variable

SetProperty ”Variable” ”OLAP_DSN_USER” Initializer ” ‘QA_User’ ”

‘Setting OLAP DSN User QA database password

SetProperty “Connection Pool” “AppDW”.”Connection Pool” “Password”

“QAPassword”

Save

Close

Exit

______________

Create one per environment.

In the command line run the following command:

AdminTool.exe /command SetVariables_QA.txt

And the repository is now prepped for the other environment (QA in this case).