Setting Up External Table Authentication

Instead of storing user IDs and passwords in an Oracle BI repository, you can maintain lists of users and their passwords in an external database table and use this table for authentication purposes.

The external database table contains user IDs and passwords, and could contain other information, including group membership and display names used for Oracle BI Presentation Services users.

NOTE: If a user belongs to multiple groups, the group names should be included in the same column separated by semicolons.

External table authentication can be used in conjunction with database authentication. If external

table authentication succeeds, then database authentication is not performed. If external table

authentication fails, then database authentication is performed.

External table authentication uses Oracle BI session variables that you define using the Variable

Manager of the Administration Tool.

Session variables get their values when a user begins a session by logging on. Certain session

variables, called system variables, have special uses. The variable USER is a system variable that is used with external table authentication.

To set up external table authentication, you define a system variable called USER and associate it with an initialization block that is associated with an external database table.

Whenever a user logs in, the user ID and password will be authenticated using SQL that queries this database table for authentication.

 After the user is authenticated successfully, other session variables for the user could

also be populated from the results of this SQL query

The presence of a defined system variable USER determines that external authentication is done.

Associating USER with an external database table initialization block determines that the user will be authenticated using the information in this table.

To set up external table authentication

1. Import information about the external table into the Physical layer. In this illustration, the

database sql_nqsecurity contains a table named securitylogons and has a connection pool named

External Table Security.

2. Select Manage > Variables to open the Variable Manager.

3. Select Initialization Blocks on the left tree pane.

4. Right-click on white space in the right pane, and then click on New Initialization Block from the right-click menu.

5. In the Initialization Block dialog box, type the name for the initialization block.

6. Click on the Select Database from the Data Source Connection drop-down list.

7. Click on Browse to search for the name of the connection pool this block will use.

8. In the Initialization String area, type the SQL statement that will be issued at authentication

time.

The values returned by the database in the columns in your SQL will be assigned to variables.

The order of the variables and the order of the columns will determine which columns are

assigned to which variables.

Consider the SQL in the following example:

select username, grp_name, SalesRep, 2 from securitylogons where username =

':USER' and pwd = ':PASSWORD'

This SQL contains two constraints in the WHERE clause:

■ :USER (note the colon) equals the ID the user entered when logging on.

■ :PASSWORD (note the colon again) equals the password the user typed.

The query will return data only if the user ID and password match values found in the specified

table.

You should test the SQL statement outside of the Oracle BI Server, substituting valid values for

:USER and :PASSWORD to verify that a row of data returns.

9 If this query returns data, the user is authenticated and session variables will be populated.

Because this query returns four columns, four session variables will be populated. Create these

variables (USER, GROUP, DISPLAYNAME, and LOGLEVEL) by clicking Edit Data Target button.

If a variable is not in the desired order, click on the variable you want to reorder and use the Up

and Down buttons to move it.

10 Click OK to save the initialization block.