[OBIEE11g] - Authentication first with LDAP then with External Database Table

Here I am going to demonstrate where on set of users present in the LDAP server as well as another set of users and passwords present in an External Oracle Database Table (here “SECURITYTABLE”) can login into thehttp://localhost:9704/analytics, the Oracle Analytics.

1. Create an sql file to create the oracle database table for external authentication as shown below:
2. You can copy the below text and save it into a file name : sectab.sql
   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’);

3. Execute the sectab.sql file in command prompt to create and populate the SECURITYTABLE as show below:
4. Open the “Oracle BI Administration Tool” and “Open Offline” the file e.g: C:OracleFMWinstancesinstance1bifoundationOracleBIServerComponentcoreapplication_obis1repositorySampleAppLite.rpd
5. Right click the “ORCL” connection in the “Physical” layer pane and select “New Object” -> “Connection Pool…”
6. Enter “SECURITY” for the “Name:” field.
7. Select “ODBC 3.5″ for the “Call interface:”
8. Select “ORCL” for the “Data source name:”
9. Enter BISAMPLE, BISAMPLE for the “User name:” and “Password:” fields respectively.
10. Then click the “OK” button.
11. Enter “BISAMPLE” in the “Confirm Password” window and click the “OK” button.
12. Right Click the “SECURITY” connection pool in the “Physical” layer pane and select “Import Metadata”
13. Select the “ORCL” entry and enter the BISAMPLE for both the “User Name:” and “Password:” fields, then click the “Next” button.
14. Click “Next” button with below checkboxes select as show in screenshot.
15. Select the SECURITYTABLE and click on the single ” > ” button to update the “Repository View:” as shown below, then click “Finish” button.
16. Now you should be able to see “SECURITYTABLE” in the “Physical” layer pane as shown below:
17. Right Click on the “SECURITYTABLE” and select “View Data…”.
18. On the “Select Connection Pool you want to use for database ORCL” window, select “SECURITY” then click on “Select” button.
19. You should now able to view the data table show below:
20. Click on the “Close” button.
21. On the Administration Tool Menu select “Manage” -> “Variables…”
22. In the “Variable Manager” window, right click on the right pane and select “New Initialization Block…”
23. On the “Session Variable Initialization Block” window click the “Edit Data Source…” button.
24. In the “Session Variable Initialization Block Data Source” window select “Database” for the “Data Source Type:” field
25. Select the “Default initialization string” button and enter the below SQL:

    SELECT GRP, SALESREP, USERNAME, 2 FROM SECURITYTABLE WHERE USERNAME = ':USER' AND PWD = ':PASSWORD'

26. Click the “Browse…” button and in the “Select Connection Pool” window select “SECURITY” the click the “Select” button as show below:
27. Then click “OK” button to close the “Session Variable Initialization Block Data Source” window.
28. In the “Variable Target” section click on the “Edit Data Target…”
29. In the “Session Variable Initialization Block Variable Target” window click on “New…” button.
30. In the “Session Variable” window enter “GROUP” for “Name:” field and click on the “OK” button
31. Click “Yes” on the Warning pop-up window.
32. Similarly create all 4 Session Variables: GROUP, DISPLAYNAME, USER and LOGLEVEL as show below.
33. Click on the “OK” button to close the window.
34. Finally on the “Session Variable Initialization Block” window enter “Security” for the “Name:” field.
35. Make sure “Required for authentication” check box is NOT Selected.
36. Verify entries as in below screenshot and Click “OK” button.
37. On the “Variable Manager” select “Action” -> “Close”.
38. On the “Administration Tool” window menu select “Save”.
39. Select “Yes” to the “Do you wish to check global consistency?” and make sure no Warnings or Errors are shown.
40. Now go to the URL: http://locahost:7001/em and login with the admin user “weblogic” .
41. Goto “Administration” on the top menu and under “Security” section select “Manage Catalog Groups”
42. Click the “+” icon with “Create a new catalog group” tooltip.
43. On the “Add Group” window enter “SalesAdmin” for the “Catalog Group Name *” and click on the “OK” button.
44. Create another catalog group with the same procedure above: “SalesRep”.
45. Now goto the URL: http://localhost:7001/em to deploy the latest SampleAppLite.rpd that we modified above.
46. Goto the “Farm_bifoundation_domain” -> “Business Intelligence” -> “coreapplication” on the left pane.
47. On the right pane select “Deployment” -> “Repository” tabs.
48. The click on the “Lock and Edit Configuration” link above the “Deployment” tab.
49. Click the “Close” button once the pop-up window appears.
50. Select the “Browse…” button and goto the location of the SampleAppLite.rpd file and click on “Open”.
51. Enter the “Repository Password” and “Confirm Password” entries as “Admin123″.
52. Then click the “Apply” button on the top right.
53. Then click on the “Activate Changes” link.
54. Click “Close” button one “Activate Changes – Completed Successfully” show up.
55. Then click on the “Restart to apply recent changes” link.
56. After the “Overview” screen show up click on the blue “Restart” button.
57. Click the “Yes” button when “Are you sure you want to restart all BI components?” shows up
58. Click on the “Close” button one the “Restarted Successfully” shows up.
59. Now we are ready to test the LDAP and External Table authentication.
60. Goto the URL: http://localhost:9704/analytics and login as adminstrator user “weblogic”
61. Select “New” -> “Analysis” -> “Sample Sales” in the “Home” section.
62. Select two columns from the “Subject Areas” section as show below: i.e “Per Name Year” and “Revenue” columns.
63. Click on the “Save Analysis” icon and browse to “Shared Folders” -> “11g Shared” (if not there you can create one”
64. Enter “Revenue Sales” for the “Name” Field and click the “OK” button.
65. Now goto the “Catalog” tab and select “More” -> “Permissions” for the “Revenue Sales” .
66. On the “Permission” window select the “BI Consumer Role” and click the ” X ” icon to delete that permission.
67. Click on the ” + ” icon to add a new permission.
68. On the “Add Application Roles, Catalog Groups and Users” window select “Catalog Groups” for the “List” field and click the “Search” button.
69. Select the “SalesRep” on the left side and click the blue ” > ” icon to move it to “Selected Members” on the right side as show below:
70. Click the “OK” button.
71. Verify the below entries are as shown below and click “OK” button on the “Permission” window.
72. Now “Sign Out” as the “weblogic” user and login giving “User ID” and “Password” as “AJOHNSON” and “aj” respectively.
73. Click the “Catalog” tab browse to “Shared Folders” -> “11g Shared” folder.
74. On the Right click “Open” on the “Revenue Sales”.
75. You able to see this since “AJOHNSON” user is under the “SalesRep” Group.
76. Now try a user under the “SalesAdmin” Group and see if you can open the same “Revenue Sales”
77. Now “Sign Out” as “AJOHNSON” and login as “ATAYLOR” and password as “at”.
78. Click the “Catalog” tab browse to “Shared Folders” -> “11g Shared” folder.
79. Here since your not in the “BI Administrator Role” or “SalesRep” group, the “Revenue Sales” report itself is invisible to you.

Note:
In order to override the LDAP authentication and use only External Database Table authentication, check the below check box in one of the previous steps.

Summary:
You would have observed we were able to login with both the administrator user “weblogic” whom is a part of the LDAP system and “AJOHNSON” whom exists in the external SECURITYTABLE oracle database table.
Playing around with the authentication section on the weblogic’s console you can configure a variety of authentication combinations.