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. The table could also contain the names of specific database catalogs or schemas to use for each user when querying data.

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.

Refer to Setting Up Database Authentication, and Order of Authentication for additional details.

External table authentication uses Oracle BI session variables that you define using the Variable Manager of the Administration Tool. For more information about the Variable Manager, refer to Using the Variable Manager.

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. For more information about session variables, refer to Understanding and Creating Session Variables.

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 provide other forms of authentication, associate the USER system variable with an initialization block associated with a LDAP server or XML source. For more information, refer to Setting Up LDAP Authentication.

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. Select Database from the Data Source Connection drop-down list.
7. Click 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 New in the dialog's Variables tab.
   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.