Authenticating Users Using an External Database Table

You can choose to maintain lists of users and their passwords in an external database rather than in the repository. An external database table consisting of user login information has been provided so that you can import this information into the repository and use it to authenticate users during login.

The table contains a list of users, their logins and passwords, and the group they belong to. Optionally, the table can also contain the logging level for each user, Web interface information, and the names of specific database catalogs or schema to use for each user when querying data. After this information has successfully been imported, you need to create an initialization block that retrieves this data.

Create an sql file to create the oracle database table for external authentication as shown below:

CREATE TABLE "SECURITYTABLE" 

   (

"SALESREP" VARCHAR2(24 BYTE), 

"USERNAME" VARCHAR2(32 BYTE), 

"PWD" VARCHAR2(16 BYTE), 

"GRP" VARCHAR2(24 BYTE)

   );

Insert following values for sample.

Note:
Each user listed in this table is a member of the Sales Rep group, except for Ellen Abel, who belongs to the Sales Admin group.
Using a table of users in an external database allows you to maintain a single list of users for multiple repositories. Although the groups must be entered into the Administration Tool manually, it is easier to set up 100 groups than it is to set up 50,000 separate users.

Import the SECURITYTABLE table in your repository.

Create an initialization block. This initialization block will populate session variables with data values returned by querying the database each time a user logs in.

• Select Manage > Variables.
• In the left pane, select Session > Initialization Blocks.
• In the right pane, right-click and select New Initialization Block.
• In the Name field, enter Security.

In the Default Initialization string field, create an initialization string that populates group, username, password, and log level variables. You define the variables in a later step. 

The values returned by the database are assigned to the variables you define. These variables are used to authenticate a user during login. Enter the following into the Initialization string field:

select GRP, SALESREP, USERNAME, 2 from SECURITYTABLE where USERNAME = ':USER' and PWD = ':PASSWORD';

Create the variables. Because you requested data for four variables in the string, you need to define each variable. The variables are Group, DisplayName, User, and LogLevel.

Click Edit Data Target.

• Click New.
• Enter Group in the Name field.
• Click OK.
• Click Yes to acknowledge that this variable has a special purpose.

Repeat the process for the remaining three variables. The order of the variables is important. You must list variables in the same order as the columns in the SQL statement.

Ensure that, your screen resembles the following screenshot.

Check Global Consistency for any errors.

Save the repository.

Upload it on server and restart BI Services.

Open BI Analysis and check whether you are able to log in using any of the user from your external database table "SECURITYTABLE".

E.g: User Name: EAbel

       Password: ea

Happy blogging.. :-)

Sagar Tippe.