Most of the times not much thought is given to defining connection pools while developing rpd. Improperly defined connection pool would affect the OBIEE performance and user experience. Here are some of the things to consider while defining connection pool.

• Change the default maximum connections. The default is 10. Based on your system usage change the value that is more realistic to your system usage
• Create a separate connection pool for execution of session variables
• Create a separate connection pool for the execution of aggregate persistence wizard. Remember that you need to give the schema user owner credentials for this connection pool as the wizard creates and drops tables
• If need be create a separate connection pool for VVIPs. You can control who gets to use the connection pool based on the connection pool permissions.

¶ 7:15 AM 0 Comments

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

   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

   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.

¶ 7:13 AM 0 Comments

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.

¶ 7:06 AM 0 Comments