Hello All,
This info I got from the
wordspress blog. I tried “External table Authentication” on my own
machine and it was working fine for me. 1st
of all we should know what is authentication.
What is mean by Authentication?
Authentication is the
process in which a user id and password is verified to see if the user is a
valid user.
Instead of creating user
IDs and passwords manually in an Oracle BI repository (10g) and Console (11g),
you can maintain lists of users and their passwords in an external database
table and use this table for authentication purposes.
Type of Authentication:
External table
authentication, Database Authentication, LDAP authentication, Oracle BI Server
User Authentication
External table Authentication Steps:
Please copy below code and
create the table. In case if you’re not able to copy, Please drop me massage.
CREATE TABLE
“SECURITYTABLE”
( ”ID” NUMBER,
“GRP” VARCHAR2(20),
“PWD” VARCHAR2(20),
“SALESREP” VARCHAR2(20),
“USERNAME” VARCHAR2(20)
) ;
Insert into
SECURITYTABLE
(ID,GRP,PWD,SALESREP,USERNAME) values (1,’SalesAdmin’,'az’,'ALAN
ZIFF’,'AZIFF’);
Insert into SECURITYTABLE
(ID,GRP,PWD,SALESREP,USERNAME) values (2,’SalesAdmin’,'at’,'ANDREW
TAYLOR’,'ATAYLOR’);
Insert into SECURITYTABLE
(ID,GRP,PWD,SALESREP,USERNAME) values (3,’SalesRep’,'aj’,'ANN
JOHNSON’,'AJOHNSON’);
Insert into SECURITYTABLE
(ID,GRP,PWD,SALESREP,USERNAME) values (4,’SalesRep’,'bj’,'ANNE
WILLIAMS’,'AWILLIAMS’);
Insert into SECURITYTABLE
(ID,GRP,PWD,SALESREP,USERNAME) values (5,’SalesRep’,'bn’,'BETTY
NEWER’,'BNEWER’);
Insert into SECURITYTABLE
(ID,GRP,PWD,SALESREP,USERNAME) values (6,’SalesRep’,'cd’,'CHRIS DREW’,'CDREW’);
Insert into SECURITYTABLE
(ID,GRP,PWD,SALESREP,USERNAME) values (7,’SalesRep’,'cm’,'CHRIS MUIR’,'CMUIR’);
Insert into SECURITYTABLE
(ID,GRP,PWD,SALESREP,USERNAME) values (8,’SalesRep’,'da’,'DALE
AREND’,'DAREND’);
Insert into SECURITYTABLE
(ID,GRP,PWD,SALESREP,USERNAME) values (9,’SalesRep’,'df’,'DALE
FAIRWEATHER’,'DFAIRWEATHER’);
Insert into SECURITYTABLE
(ID,GRP,PWD,SALESREP,USERNAME) values (10,’SalesRep’,'ds’,'DICK
SCHMIDT’,'DSCHMIDT’);
Confirm table is created properly.
Open the “Oracle BI
Administration Tool” and “Open Current live RPD in offline mode”
Right click the “Rakesh
ORCL” connection in the “Physical” layer pane and select “New Object” ->
“Connection Pool…”
Enter “SECURITY” for the
“Name:” field. And Select “OCI 10g/11g″ for the “Call interface:”
Select “ORCL” for the “Data
source name:’’ and Enter shared logon user name and password. Check the below
details. Then click the “OK” button.
Right
Click the “SECURITY” connection pool in the “Physical” layer pane and select
“Import Metadata”. Check your data source name “ORCL” and enter the SH for both
the “User Name:” and “Password:” fields, then click the “Next” button.
Click
“Next” button with below checkboxes select as show in screenshot
Select
the SECURITYTABLE and click on > as shown below, then click “Finish” button
Now you should be able to
see “SECURITYTABLE” in the “Physical” layer pane as shown below:
Right Click on the
“SECURITYTABLE” and select “View Data…”.
Select Connection Pool you want to use for database ORCL” window, select “SECURITY” then click on “Select” button.
Go to Administration Tool
Menu select “Manage” -> “Variables…”
On the “Session Variable Initialization Block” window click the “Edit Data Source…” button.
In the “Session Variable
Initialization Block Data Source” window select “Database” for the “Data Source
Type:” field
Select the “Default
initialization string” button and enter the below SQL:
SELECT GRP, SALESREP,
USERNAME, 2 FROM SECURITYTABLE WHERE USERNAME = ':USER' AND PWD = ':PASSWORD'
Click the “Browse…” button
and in the “Select Connection Pool” window select “SECURITY” the click the
“Select” button as show below:
Then click “OK” button to
close the “Session Variable Initialization Block Data Source” window.
In the “Variable Target”
section click on the Edit Data Target
In the “Session Variable
Initialization Block Variable Target” window click on “New…” button.
In the “Session Variable” window
enter “GROUP” for “Name:” field and click on the “OK” button
Click “Yes” on the Warning pop-up window.
Similarly create all 4
Session Variables: GROUP, DISPLAYNAME, USER and LOGLEVEL as show below and
Click on the “OK” button to close the window.
Finally on the “Session
Variable Initialization Block” window enter “Security” for the “Name:” field.
Make sure “Required for
authentication” check box is NOT Selected.
Verify entries as in below
screenshot and Click “OK” button.
Now go to the URL: http://rakesh:7001/analytics and login with the admin
user “weblogic” .
Go to “Administration” on the top menu and under “Security” section select “Manage Catalog Groups”
Click the “+” icon with
“Create a new catalog group” tooltip.
On the “Add Group” window
enter “SalesAdmin” for the “Catalog Group Name *” and click on the “OK” button.
Create another catalog
group with the same procedure above: “SalesRep”.
IMP Notes – Catalog Group name should be same which available in SECURITYTABLE
After Creating catalog
group (Sales Admin and Salesrep) create the sample report and give it access to
as SalesAdmin and SalesRep.
Login with SalesAdmin and SalesRep Group User.
After logging you will see they access only for report
Example –
Here I have l logged in
with user ANNJOHNSON, Here you will see he has access only QuickStart - Overview Dashbored. He don’t have access for OBIEE Analysis.
