External table authorization and row level security in OBIEE11g.

Step1: Create a table to store user login information.

  CREATE TABLE "UI_LOGIN"

   (     

    "UNAME" VARCHAR2(100 BYTE),

    "PASSWORDS" VARCHAR2(100 BYTE)

   );

Insert into UI_LOGIN (UNAME,PASSWORDS) values ('Testuser1','Testuser1');

Insert into UI_LOGIN (UNAME,PASSWORDS) values ('Testuser2','Testuser2');

Step2: Create a session system variable called “USER” and corresponding initialization block.

SQL for initialization block:

Select uname from SUPPLIER2.UI_LOGIN where  uname=':USER'

After this step you will able to login to the Analytics url using the username available in UI_LOGIN table.

Step3: Create application roles in enterprise manager (em), in this example we will create application role called “PM”.

Stpe4:  Now you go to the Manage->Identity manager in rpd and sync the application roles. Now you able to see PM role .

Step5:  Create a table for user role maping.

  CREATE TABLE  ROLE_AUTH"

   (      "UNAME" VARCHAR2(100 BYTE),

          "ROLE_NAME" VARCHAR2(100 BYTE)

   );

Insert into ROLE_AUTH (UNAME,ROLE_NAME) values ('Testuser1','PM');

Insert into ROLE_AUTH (UNAME,ROLE_NAME) values ('Testuser2','PM');

Step6: Create a session system variable called “GROUP” and corresponding initialization block.

SQL for initialization block:

Select 'GROUP', role_name from supplier2.role_auth where uname=':USER'

Note: Here we will mark the variable as row-wise initialization, as we may get multiple groups for one user.

Step7: Create a table for user to data mapping.

  CREATE TABLE      DATA_AUTH"

   (      "UNAME" VARCHAR2(100 BYTE),

          "TYPECODE" VARCHAR2(100 BYTE),

          "USER_YEAR" NUMBER(*,0)

   )

Insert into DATA_AUTH (UNAME,TYPECODE,USER_YEAR) values ('Testuser1','100',1998);

Insert into DATA_AUTH (UNAME,TYPECODE,USER_YEAR) values ('Testuser1','101',1998);

Insert into DATA_AUTH (UNAME,TYPECODE,USER_YEAR) values ('Testuser2','110',1999);

Insert into DATA_AUTH (UNAME,TYPECODE,USER_YEAR) values ('Testuser2','111',1999);

Intention above to restrict dataset, In this case

         Testuser1:  will be able access Typecode 100,101 and for year 1998

         Testuser2:  will be able access Typecode 110,111 and for year 1999

Note: in this example Typecode, user_year is the business columns; this will differ based on your business requirement.

Step8: Create a session Non-system variable called “User_Year”,” Typecode  ” and corresponding initialization block. As we will use this two variables for data restriction.

SQL for initialization block 'User_Year':

Select 'User_Year',user_YEAR from SUPPLIER2.data_auth where uname=':USER'

SQL for initialization block ‘Typecode‘:

Select 'Typecode', Typecode from SUPPLIER2.data_auth where uname=':USER'

Step9: Set the data filter.

a.    Go to Manage->Identity

b.    Now select the appropriate role in this example “PM”

c.    Click on permission

d.    Now in data filter section add the filter condition.

e.    In this example you need to add below to filter condition

"Sales"."Dim_Time"."YEAR" =VALUEOF(NQ_SESSION."User_Year")

"Sales"."Dim_Product"."Type Code" =VALUEOF(NQ_SESSION."Typecode")

Step10: Testing

  If you have any report having  "Sales"."Dim_Time"."YEAR","Sales"."Dim_Product"."Type Code" (In this example) and report accessed by two different users (Testuser1, Testuser2) the result will vary based on the data restriction.

Testuser1 Result:                                                       Testuser2 Result:

In other users where we don’t have data restriction like Webogic   will able to see all data .