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
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
$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.
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.
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
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.
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.
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.
[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.
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
