Kumar Kambam’s OBIEE Blog

Intelligence on Oracle Business Intelligence

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.


Advertisements

June 18, 2009 Posted by | OBIEE, OBIEE Security | , , , , | 3 Comments

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.

 

 

February 3, 2009 Posted by | OBIEE Security | , , , , , , , , , | 15 Comments