Delivers and Database Authentication

In Oracle BI Applications, users are always created in the operational application database, never in the Oracle BI repository. The Oracle BI repository is preconfigured for database authentication.

Oracle BI Scheduler Server runs Delivers jobs for users without accessing or storing their passwords. Using a process called impersonation, the Scheduler uses one user ID and password with Oracle BI Administrator privileges that can act on behalf of other users. The Scheduler initiates an iBot by logging on to Oracle BI Presentation Services with that Oracle BI Administrator ID and password.

For Delivers to work, all database authentication must be performed in only one connection pool, and that connection pool can only be selected in an initialization block for the USER system session variable. This is typically called the Authentication Initialization Block. When impersonation is used, this initialization block is skipped. All other initialization blocks must use connection pools that do not use database authentication.

CAUTION:  Using an authentication initialization block is the only initialization block in which it is acceptable to use a connection pool in which :USER and :PASSWORD are passed to a physical database.

For other initialization blocks, SQL statements can use :USER AND :PASSWORD. However, because Oracle BI Scheduler Server does not store user passwords, the WHERE clause must be constructed as shown in the following example:

select username, groupname, dbname, schemaname from users
where username=':USER'
NQS_PASSWORD_CLAUSE(and pwd=':PASSWORD')NQS_PASSWORD_CLAUSE

NOTE:  When impersonation is used, everything in the parentheses is extracted from the SQL statement at runtime.

For more information, refer to the Oracle BI Delivers examples in Selecting and Testing the Data Source and Connection Pool.