Authentication - External Database Table

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.  1^st 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.

If you have any question, Please let me know.

Thanks

Rakesh Patil