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.
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.
- 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.
- Select Manage > Variables to open the Variable Manager.
- Select Initialization Blocks on the left tree pane.
- Right-click on white space in the right pane, and then click on New Initialization Block from the right-click menu.
- In the Initialization Block dialog box, type the name for the initialization block.
- Select Database from the Data Source Connection drop-down list.
- Click Browse to search for the name of the connection pool this block will use.
- 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'
- 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.
- Click OK to save the initialization block.