Thursday, March 30, 2017

Authentication in OBIEE 12c using application level database tables


Environment:

OBIEE Server Operating System: Oracle Linux 7 x64

OBIEE Server Version: Oracle Business Intelligence Enterprise Edition 12.2.1.2 x64

Oracle Business Intelligence Developer Client Tool Operating System: Windows 7 x64

Oracle Business Intelligence Developer Client Tool (12.2.1.2.0):

Oracle Business Intelligence Developer Client Tool is Installed in: C:\app\oracle\Middleware\BIClient12.2.1.2\


1. Create TNS Entry in tnsnames.ora

Create entry in tnsnames.ora file for database where we want to connect to using OBIEE repository file. File tnsnames.ora file is located in 2 locations as mentioned below. Create the entry in both the files.

ORACLE_HOME=/u01/app/oracle/product/OBIEE_12.2.1
DOMAIN_HOME=/u01/app/oracle/config/domains/OBI_Domain

Location of tnsnames.ora file in OBIEE Server is mentioned below:

$ORACLE_HOME/network/admin/tnsnames.ora
$DOMAIN_HOME/config/fmwconfig/bienv/core/tnsnames.ora

Sample tns entry:

OBIEE_TEST_PDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mydbservername)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = OBIEE_TEST_PDB)
    )
  )

NOTE: If you have deployed OBIEE Server in UNIX/Windows environment and use OBIEE Client Administration tool in Windows to build repository (rpd) file, then you have to create the tns entry in Windows OBIEE Client Administration environment as well.

2. Create database tables for OBIEE table based authentication

2 (a). Create table to store application users

CREATE TABLE obiee_test.appl_users
(
   user_id              NUMBER CONSTRAINT users_nn01 NOT NULL
  ,username             VARCHAR2 (30 BYTE) CONSTRAINT users_nn03 NOT NULL
  ,description          VARCHAR2 (240 BYTE) CONSTRAINT users_nn05 NOT NULL
  ,encrypted_password   VARCHAR2 (256 BYTE)
)
TABLESPACE users;

CREATE UNIQUE INDEX obiee_test.users_u1
   ON obiee_test.appl_users (username)
   LOGGING
   TABLESPACE users
   NOPARALLEL;

SET DEFINE OFF;

INSERT INTO obiee_test.appl_users (user_id ,username ,description ,encrypted_password)
     VALUES (1192 ,'ASHISH' ,'Ashish Man Baisyet OBIEE Author.' ,'ashish');

INSERT INTO obiee_test.appl_users (user_id ,username ,description ,encrypted_password)
     VALUES (237 ,'SHOURYA' ,'Shourya Man Baisyet OBIEE Administrator.' ,'shourya');

COMMIT;


2 (b) Create table to store application roles


CREATE TABLE obiee_test.appl_roles
(
   role_id       NUMBER CONSTRAINT roles_nn01 NOT NULL
  ,code          VARCHAR2 (30 BYTE) CONSTRAINT roles_nn02 NOT NULL
  ,description   VARCHAR2 (240 BYTE) CONSTRAINT roles_nn06 NOT NULL
)
TABLESPACE users;

CREATE INDEX obiee_test.roles_n2
   ON obiee_test.appl_roles (code)
   TABLESPACE users;

CREATE UNIQUE INDEX obiee_test.roles_u1
   ON obiee_test.appl_roles (role_id)
   TABLESPACE users;


SET DEFINE OFF;
Insert into OBIEE_TEST.APPL_ROLES
   (ROLE_ID, CODE, DESCRIPTION)
 Values
   (104, 'BIConsumer', 'Role in OBIEE to generate predefined reports');
Insert into OBIEE_TEST.APPL_ROLES
   (ROLE_ID, CODE, DESCRIPTION)
 Values
   (105, 'BIAdministrator', 'Role in OBIEE to Develop New reports');
Insert into OBIEE_TEST.APPL_ROLES
   (ROLE_ID, CODE, DESCRIPTION)
 Values
   (106, 'BIAuthor', 'BI Consumer Application Role');
COMMIT;


2 (c) Create mapping table for appl_users and appl_roles


CREATE TABLE obiee_test.user_roles
(
   user_role_id   NUMBER CONSTRAINT user_roles_nn01 NOT NULL
  ,role_id        NUMBER CONSTRAINT user_roles_nn03 NOT NULL
  ,user_id        NUMBER CONSTRAINT user_roles_nn05 NOT NULL
)
TABLESPACE users;

CREATE UNIQUE INDEX obiee_test.users_roles_u1
   ON obiee_test.user_roles (user_role_id)
   TABLESPACE users;

CREATE INDEX obiee_test.user_roles_n1
   ON obiee_test.user_roles (role_id)
   TABLESPACE users;


SET DEFINE OFF;
Insert into OBIEE_TEST.USER_ROLES
   (USER_ROLE_ID, ROLE_ID, USER_ID)
 Values
   (12450, 106, 237);
Insert into OBIEE_TEST.USER_ROLES
   (USER_ROLE_ID, ROLE_ID, USER_ID)
 Values
   (14404, 104, 237);
Insert into OBIEE_TEST.USER_ROLES
   (USER_ROLE_ID, ROLE_ID, USER_ID)
 Values
   (14405, 105, 1192);
COMMIT;


3. Create Repository file

Open Oracle BI Administration Tool. It is located in C:\app\oracle\Middleware\BIClient12.2.1.2\bi\bitools\bin\admintool.cmd or you can open it from Windows Start menu.

To create new repository file Click on File=> New Repository.


OBIEE_DB_Table_Image_01.jpg

Click on Browse button to locate the folder where you want to create your repository file and enter the filename. For now do not import any metadata so select “No” option for “Import Metadata”. Then provide “Repository Password:” and click on “Finish” button.


OBIEE_DB_Table_Image_02.jpg

4. Create new Database in Physical Layer

In the Physical layer right click and click on New Database.


OBIEE_DB_Table_Image_03.jpg

Provide a name for database to appear in the Physical Layer. It need not be same as the database server database name. Add new database as desired. Then select “Database type:” and click on “Ok” button. In my case I am using Oracle Database 12c.


OBIEE_DB_Table_Image_04.jpg

5. Create Connection Pool for RPD Modelling


After the database is added then create connection pool. This connection pool is required to execute queries behind the scenes. Select the database that we just added and then select “New Object => Connection Pool”.


OBIEE_DB_Table_Image_05.jpg

Provide connection pool details. Point to note here is if you are using OCI 10g/1g Call Interface then you have to define a tns entry in OBIEE Client Tools home as mentioned above. This tnsentry has to be entered in “Data Source Name:”. In my case Data Source Name is “OBIEE_TEST_PDB. Connection Pool “OBIEE_TEST_CP” is for modelling repository and we will not be using this connection pool in this post. Connection Pool “OBIEE_Security_CP” is to execute the SELECT statement for External Table Authentication in the Initialization Block of RPD file.

Remember for OCI Call interface Data Source Name should be same as that defined in tnsnames.ora file and the tns-entry name should be same as to match Data Source Name which is OBIEE_TEST_PDB, NOT TO BE CONFUSED WITH OUR PLUGGABLE DATABASE NAME.


OBIEE_DB_Table_Image_06.jpg

Administration Client till will prompt you for confirmation password, re-enter the password.


OBIEE_DB_Table_Image_07.jpg

6. Create Connection Pool for Seurity Modelling

Create another Connection Pool to execute the "SELECT" statement defined in the Session level Initialization Block in RPD file. We may be able to use the same Connection Pool to model repositories and for external table authentication, I have never tested this though.


OBIEE_DB_Table_Image_08.jpg

Provide details for another connection pool "OBIEE_Security_CP".


OBIEE_DB_Table_Image_09.jpg

Administration Client till will prompt you for confirmation password, re-enter the password.


OBIEE_DB_Table_Image_10.jpg



7. Create Initialization Block For Security


From the main menu click on “Manage => Variables …” to go to the Variable Manager.



OBIEE_DB_Table_Image_11.jpg

In the Variable Manager screen, click on “Initialization Blocks” at the “Session” level.  Right click on the right pane and click on “New Initialization Block” to create new Session level initialization Block.


OBIEE_DB_Table_Image_12.jpg

8. Provide Initialization Block Details


Enter Initialization Block Name and click on Edit Data Source. In this pane enter the query which is used to authenticate users based on application table. Click on “Browse” button to select the Connection Pool to execute the query we just defined. 


OBIEE_DB_Table_Image_13.jpg

Select the Connection Pool in the right pane and click on “Select” button.


OBIEE_DB_Table_Image_14.jpg

Selected Connection Pool is displayed in Connection Pool text box.


OBIEE_DB_Table_Image_14-B.jpg

9. Set Target Variables

Provide variable targets. Click on “Edit Data Target …” to provide the name of variables where the row returned from the query above should be stored.

OBIEE_DB_Table_Image_15.jpg

Click on “New” button to add a variable. Our query returns following columns so we need to define equal number of variables here.


user_id ,username ,user_description ,role_id ,role_code ,role_description, loglevel





OBIEE_DB_Table_Image_16.jpg

Enter the variable name and click on “Ok” button. Similarly, create remaining of the variables.

Create variable for USER_ID column.


OBIEE_DB_Table_Image_17.jpg

Create variable for USERNAME column and name it USER.

Note: Variables USER, ROLES and LOGLEVEL are OBIEE built-in variables, you will receive a confirmation message before these two variables are created. Click on “Yes” button in the confirmation dialog box.


OBIEE_DB_Table_Image_18.jpg
Click on Yes to confirm.


OBIEE_DB_Table_Image_19.jpg

Create variable for USER_DESCRIPTION column.


OBIEE_DB_Table_Image_20.jpg

Create variable for ROLE_ID column.


OBIEE_DB_Table_Image_21.jpg

Create variable for ROLE_CODE column and name it ROLES.


OBIEE_DB_Table_Image_22.jpg

Click on Yes to confirm.


OBIEE_DB_Table_Image_23.jpg

Create variable for ROLE_DESCRIPTION column.

 

OBIEE_DB_Table_Image_24.jpg

Create variable for LOGLEVEL column.


OBIEE_DB_Table_Image_25.jpg

Click on Yes to confirm.


OBIEE_DB_Table_Image_26.jpg

Crosscheck total number, name and order of variables. Out of these variables USER, ROLES and LOGLEVEL are Oracle OBIEE built in variables and based on these variables External Database Table level authentication takes place.


OBIEE_DB_Table_Image_27.jpg

10. Test Configuration

After all the variables are added we need to test if the configuration and query is working or not. First we test for ASHISH user and then we test for SHOURYA user. Click on “Test” button.

10.1 Test for ASHISH user



OBIEE_DB_Table_Image_28.jpg

In Set value for the variables dialog box, in :USER variable enter ASHISH and for :PASSWORD enter ashish and click “Ok” button to test. You may have some predefined value in USER field, in that case override the value with your USERNAME.


OBIEE_DB_Table_Image_29.jpg

NOTE: Make sure to keep strong password in production environment.


OBIEE_DB_Table_Image_30.jpg

If all the steps above are good then we receive values for each variable else “Empty Result set.” message is displayed.


OBIEE_DB_Table_Image_31.jpg

10.2 Test for SHOURYA user



OBIEE_DB_Table_Image_32.jpg


OBIEE_DB_Table_Image_33.jpg

11. Transfer repository file (RPD) to OBIEE Server


Close the repository file in Administration Tool and upload to OBIEE server from Windows to UNIX environment, then upload the repository file in OBIEE Server using following command.


If the OBIEE Administration Tool and OBIEE Server are in different machines then FTP the repository file to OBIEE server, then upload the repository file in OBIEE Server using following command.

[oracle@obiee-server BI1221_Cat]$ $DOMAIN_HOME/bitools/bin/datamodel.sh uploadrpd -I OBIEE_12c_DB_Auth.rpd -SI ssi -U weblogic -P Oracle_PWD2
RPD Password:
Service Instance: ssi

Operation successful.
RPD upload completed successfully.
[oracle@obiee-server BI1221_Cat]$

12. Test the configuration via OBIEE web analytics

After the RPD file is uploaded go to OBIEE web analytics login page and try to login with the users defined in appl_users table. OBIEE Web Analytics is available via following url:

http://obiee-server-name:port_no/analytics


OBIEE_DB_Table_Image_34.jpg

Bingo, we have successfully logged into OBIEE using application tables for authentication.


OBIEE_DB_Table_Image_35.jpg

13. Check the permission of logged in user

Click on the username along "Signed In As". Then click on "My Account". Then Click on "Application Roles" tab to see the roles.





14. Location of Important Log files

The log files which are useful for this configuration are located at:

/u01/app/oracle/config/domains/OBI_Domain/servers/bi_server1/logs/bi_server1-diagnostic.log
/u01/app/oracle/config/domains/OBI_Domain/servers/obips1/logs/sawlog0.log
/u01/app/oracle/config/domains/OBI_Domain/servers/obis1/logs/obis1-diagnostic.log

I found logfile "obis1-diagnostic.log" very useful as it gives exact error message.


15. Query used for the initialization block

SELECT DISTINCT u.user_id ,u.username ,u.description AS user_description ,ur.role_id AS role_id ,r.code AS role_code
               ,r.description AS role_description,2 AS loglevel
  FROM user_roles ur, appl_users u, appl_roles r
 WHERE     ur.user_id = u.user_id
       AND ur.role_id = r.role_id
       AND r.code IN ('BIAdministrator', 'BIConsumer')
       AND u.username = ':USER'
       AND u.encrypted_password = ':PASSWORD'

Note: I had few issues and so I applied patch p25312652_122120_Generic.zip in OBIEE server

16. Possible errors

 If tnsentry is not defined properly in OBIEE server and OBIEE Administration Client Tools installation location then it is likely that you may encounter following exceptions in “obis1-diagnostic.log” logfile:

[2017-03-30T11:20:04.381+11:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: 3b3e50d4-bd15-4766-88b8-be3c49cbc8d6-00000301,0:1:1:6:2] [sik: ssi] [tid: 777f7700] New connection created for dsn OBIEE_TEST_PDB, DB connection name is OBIEE_Test_DB:OBIEE_Security_CP, DB name is OBIEE_Test_DB, Idle Timeout is Ĭ [[
Properties: description=SessionVar Exchange; producerID=0x75c6cff8; requestID=0xfffe0005; sessionID=0xfffe0000; userName=ASHISH;
]]
[2017-03-30T11:20:04.381+11:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: 3b3e50d4-bd15-4766-88b8-be3c49cbc8d6-00000301,0:1:1:6:2] [sik: ssi] [tid: 777f7700] Connection Pool entry for DSN OBIEE_TEST_PDB, DB name is , connection pool name is  [[
Properties: description=SessionVar Exchange; producerID=0x75c6cff8; requestID=0xfffe0005; sessionID=0xfffe0000; userName=ASHISH;
]]
[2017-03-30T11:20:04.403+11:00] [OBIS] [ERROR:1] [] [] [ecid: 3b3e50d4-bd15-4766-88b8-be3c49cbc8d6-00000301,0:1:1:6:2] [sik: ssi] [tid: 777f7700]  [nQSError: 17014] Could not connect to Oracle database. [[
file: server/Query/Execution/DbGateway/Oci10g/../Oci8/Src/SQXDGOci8.cpp; line: 411
Properties: description=SessionVar Exchange; producerID=0x75c6cff8; requestID=0xfffe0005; sessionID=0xfffe0000; userName=ASHISH;
[nQSError: 17001] Oracle Error code: 12154, message: ORA-12154: TNS:could not resolve the connect identifier specified
 at OCI call OCIServerAttach.

**********  Task: 1. Running for (mls): 22 **********
Description: DB Connect
DSN: OBIEE_TEST_PDB; userName=obiee_test

**********  Task: 2. Running for (mls): 22 **********
Description: SessionVar GatewayDbGateway Prepare
DSN:OBIEE_Security_CP
User Name:obiee_test
SQL:SELECT DISTINCT u.username ,r.code AS role_code ,2 AS loglevel
  FROM user_roles ur, appl_users u, appl_roles r
 WHERE     ur.user_id = u.user_id
       AND ur.role_id = r.role_id
       AND r.code IN ('BIAdministrator', 'BIConsumer')
       AND u.username = 'ASHISH'
       AND u.encrypted_password = 'ashish'

**********  Task: 3. Running for (mls): 22 **********
Description: Producer Executing Query
Repository Name:ssi;Subject Area Name:;User Name:ASHISH

]]
[2017-03-30T11:20:04.403+11:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: 3b3e50d4-bd15-4766-88b8-be3c49cbc8d6-00000301,0:1:1:6:2] [sik: ssi] [tid: 777f7700] Connection Pool exit for DSN OBIEE_TEST_PDB, DB name is OBIEE_Test_DB, connection pool name is OBIEE_Security_CP [[
Properties: description=SessionVar Exchange; producerID=0x75c6cff8; requestID=0xfffe0005; sessionID=0xfffe0000; userName=ASHISH;
]]
[2017-03-30T11:20:04.403+11:00] [OBIS] [NOTIFICATION:1] [] [] [ecid: 3b3e50d4-bd15-4766-88b8-be3c49cbc8d6-00000301,0:1:1:6:2] [sik: ssi] [tid: 777f7700] Connection destroyed due to timeout. dsn is OBIEE_TEST_PDB, DB name is OBIEE_Test_DB [[
Properties: description=SessionVar Exchange; producerID=0x75c6cff8; requestID=0xfffe0005; sessionID=0xfffe0000; userName=ASHISH;
]]
[2017-03-30T11:20:04.404+11:00] [OBIS] [ERROR:1] [] [] [ecid: 3b3e50d4-bd15-4766-88b8-be3c49cbc8d6-00000301,0:1:1:6] [sik: ssi] [tid: 6eeee700]  [13011] Query for Initialization Block 'SecurityInitBlock' has failed. [[


Hope this helps