Security!... where do I start?

This post is going to discuss things to consider when setting up a basic security strategy in OBIEE, focusing mainly on User Authentication.

Security in OBIEE can be thought of as a 2 step process as a user logs into the system:
1. Authentication – Do you have access?
2. Authorization – How much access do you have?

Authentication
Think of this as just getting your foot in the door. During Authentication, OBIEE is simply determining if a user has access to the tool. Once someone logs into the system from the Logon screen, the Oracle BI Presentation Server (OBIEE web front end), creates two System Variables. One for the Username that was entered (:USER) and one for the Password that was entered (:PASSOWORD). These System Variables are then sent to the Oracle BI Server to check if a User has been set up with that combination of Username and Password. A User can be set up a few different ways…

Security Manager:

The easiest and most basic way to set someone up with a Username and Password is via the Security Manager. Once someone is set up in this manner, their Username (:USER) and Password (:PASSWORD) are stored on the Oracle BI Server. If the Username and Password entered in the Logon Screen match what’s stored on the Oracle BI Server, they are granted access to the tool. This approach should only be taken if just a few people need access to the tool. Adding an entire organization of people in this manner is tedious, time consuming, and difficult to maintain.

External Database:

Setting up a table with a complete set of User Information, including Username and Password, will allow OBIEE to verify security credentials using what’s called an Initialization Block. An Initialization Block is basically a SELECT statement sent to the User Information table to pull back Username and Password. It is then compared to the System Variables sent from the Logon screen. If there is a match in the User Information table, they are given access. Now the User doesn’t need to be entered through the Security Manager, and the User Information table can be maintained from the database. Additionally, a number of other Variables can be populated from your User Information table to be used during Authorization.

Care must be taken when creating the Initialization Block, as the order of the SELECT statement determines what information is applied to each Variable. For example, in the image below, the value of “full_name” will be applied to the Variable “DISPLAYNAME”.

Single Sign-On:

SSO allows Users a single point of access to all of their organization’s systems. This typically requires the use of an SSO product. View this blog entry for more information on SSO. http://oraclebiblog.blogspot.com/search/label/SSO

Once you iron out your approach with this first step in the process… You’re ready to tackle the task of setting up Authorization. I’ll cover this in a future post…

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 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 and Virtual Private Database (VPD)

What is VPD?

Virtual Private Database is Oracle’s fine grain access control (FGAC) feature that was introduced in Oracle 8i. It helps control data level security on the database side by applying policies, thus data level security in the applications that read from the database is not necessary. The advantage is that if there are multiple applications accessing data from a database, it is not necessary to implement data level security in all those applications.

How does VPD work?

Policies are created in the database that would append a predicate (a WHERE clause) to the query in runtime. Consider a simple example – there exists a policy which would return only the rows attached to a particular user id on the table Orders. If a user “Kumar” were to query data from Orders table, Kumar would enter the following command:

Select * from Orders;

The policy that dictates what information a user can see would append a predicate to the query as follows:

Select * from Orders

where user_name = ‘KUMAR’;

This mechanism of appending the predicate is entirely transparent to the user.

Click here to read more about VPD on Oracle’s OTN.

Configuring VPD in OBIEE

To use the VPD feature in the Database and OBIEE along with its caching capabilities it is important to configure VPD in OBIEE. Failing to configure VPD in OBIEE while caching is enabled (in OBIEE), the request would bypass VPD policies by accessing data from cache and data level security will not be effectively handled by the database’s VPD. Thus, the users will see incorrect results.

To configure VPD in OBIEE, first enable the VPD option in the database’s general tab as shown:

Then enable the “Security Sensitive” option in the security variable:

Normal OBIEE Cache Behavior

To be simple and brief, if caching is enabled, a query that is being run for the first time would create a cache. Subsequent requests that is similar to the query or its subset would hit the cache to retrieve the results. This is true even if the users are different.

Example:

Logged on as Kumar Kambam

Running a request…

… generates the following Query log

The cache is created….

Now any user that issues a similar request or a subset of the request will hit the cache.

Logged on as Power User1

Running a similar request, generates the following log. Notice that OBIEE server found a matching query in the cache that is created by Kumar.Kambam for the query issued by Poweruser1.

OBIEE Cache Behavior with VPD configured

When VPD option is configured in OBIEE, cache is created for each user even though a matching query exists in the cache. This ensures that the data retrieved for a user is not retrieved from the cache created by a different user, thereby ensuring the enforcement of VPD policies. In other words, if Kumar.Kambam were to run a query, the cache is created by the data visibility rules enforced by the VPD for Kumar.Kambam. If Poweruser1 runs a similar request it should bypass the query cache and hit the database to retrieve the data along the policies of the VPD for Poweruser1; if it were to hit the cache created by Kumar.Kambam, the results for Kumar Kambam will be presented to Poweruser1.

After configuring VPD, logged on as Kumar Kambam

Running a query for the first time…

…the following log is generated

The cache is created

Running the same query again, the following log is generated…

OBIEE found a matching query in the cache and uses it.

Now log on as Power User1

By running the same request, the following log is generated…

A new cache entry is created even though a similar request has been issued by a different user and a cache has been created for it

The subsequent requests by Poweruser1 that is similar to the query will hit its own cache. This ensures that a user will only see his/her data.

"Bullet Proof" SSO

The following pieces are a subset of info presented at Oracle OpenWorld's OBI SSO Unconference by Kevin McGinley and myself. The content framework was provided by John Andrzejek of BI Consulting Group.

The full presentation can be found via http://www.biconsultinggroup.com/OOW

Many organizations have enabled SSO (Single Sign-On) with OBI EE; however, not all have taken the appropriate steps to make it "Bullet Proof", aka next to impossible to hack. Until now!

Below is a typical OBI EE login path when SSO is not enabled. This method works well in an intranet environment where personal data is not available to be queried by OBI EE.



Many organizations have taken the step to enable SSO for OBI EE so their users do not have to enter/remember yet another username/password combination. SSO is more secure than the basic OBI EE login but is still susceptable to hackers tricking OBI EE into authorization.

In order to ensure a more robust/secure method of SSO with OBI EE, the following approach should be taken so that a hacker cannot trick OBI EE into thinking the user has been authenticated. This is handle by Step 4 (see pic below) whereby the user clicks on a link in their portal to go to OBIEE and the portal writes a Record to a database table and then the BI Server checks this record to make sure the user has been authenticated.

If the user was not properly authenticated by the Portal then OBI will deny the user access to OBI EE.