Everything Oracle

Home

Everything Oracle

 

 

 

 

 

 

Introduction

 

As is so often the case with the OBIEE documentation, the discussion of connection pools is less than satisfactory. 

 

In the case of some categories of functionality, such as queries initiated by initialization blocks and direct database requests, a named connection pool is specified.  But for general purpose queries, naming the connection pool is not possible (there is no means for a connection pool to be specified on the “Advanced” tab).  For these general purpose queries, the connection pool that is used by a query is determined by the order in which the connection pools are listed in the “Connection Pools” tab under the “Database” node in the physical layer, and by the set of permissions that apply to each connection pool.

 

In this article we’ll first outline the rules used to allocate a connection pool to a query, and then we’ll work through a detailed example that illustrates the application of these rules in practice, using connection script calls to track the connection pool used by each query.

 

 

General Queries – Administrator

 

A user who belongs to the “Administrators group” is special.  For a general purpose query an “Administrators” group user will always use the first available pool in the connection pool list, even if permissions for that pool indicate that it is not available to any group or user (note when we select the “Show all users/groups” check box in the “Permissions” pop-up, the “Administrators” group is not shown).

 

So we need to be careful if we are using an “Administrators” group user for testing.

 

 

General Queries – Non-Administrators Group User – New Login

 

For a user who does not belong to the “Administrators” group and who is issuing the first query of a new session, the connection pool which is used is the first pool in the connection pool list for which the user has explicitly been granted permission:

 

  If the user has permission to use the first connection pool on the connection pool list, then that connection pool is used.

 

  If the user does not have permission to use the first connection pool on the list, then the connection pools are examined in turn in the order in which they are listed in the repository, and the first connection pool for which the user has explicit permission is used.

 

  If the user does not have permission to use any pool in the connection pool list, then the query will raise an error: “The user does not have sufficient privilege to access the database”.

 

  Permission must be granted explicitly.  Let’s assume that there are three connection pools, listed in the order “A”, “B”, and “C”, and that all the connection pools are disabled for the group “Everybody”.  Let’s assume that for the user the permissions are as follows:

      A – Box with red X;
      B – Blank box; and
      C – Ticked box.

Then connection pool “C” will be used.

 

 

General Queries – Dynamic Permission Changes

 

When a user executes the first general purpose query within a session, the session is bound to the connection pool selected for that query.  Changing permissions in the repository for that user will not have any effect on the choice of connection pool until the user logs out and then logs in again:

 

  If a user session has already used a connection pool and the permissions on a pool that occurs earlier in the connection pool list are changed so that this earlier pool is now available to the user, then subsequent queries within the same session will still use the original connection pool.

 

  If a user session has already used a connection pool and the permissions on that pool are changed so that the pool is no longer available to the user, then subsequent queries within the same session will still use that connection pool.

 

  If a user session has already used a connection pool and the non-permission properties of that pool (such as “username” or “password”) are changed so that the pool is no longer available to the user, then subsequent queries within the same session will fail.  The session does not try to use another pool on the connection list, even though such a pool may be available and have all the requisite permissions.

 

 

Initialization Queries

 

When a connection pool is named in an initialization query then that connection pool is always used even if the user is explicitly denied permission to use the connection pool.

 

This means that a connection pool used for initialization which does not have permissions for any user or user group can occur in any order in the connection pool list.  But if it has permissions for a specific user or user group then it must be placed after any general query connection pools for the same user or group to ensure that it is not used for general purpose queries.

 

 

Direct Database Requests

 

When a connection pool is named in a direct database request, then the SQL can be validated and the query columns retrieved even though the user does not have permissions on the connection pool, but permissions must be granted in order to execute the request.  If the grant is given while the user has a session running, then the user will have to log out and then log back in again before the Presentation Services will be aware that the grant has been given.

 

 

Connection Pool Example – Setup

 

To try and put the rules outlined in the previous sections in context, let’s consider an example.  Let’s suppose that we have three user groups, “Group A”, “Group B”, and “Group O”:

 

And let’s suppose we have three users, “User A”, “User B”, and “User O”:

 

who, respectively, belong to the corresponding groups.

 

Let’s suppose we have five connection pools.  Connection pool “GQ Group A” can only be accessed by “Group A” users:

 

Connection pool “GQ Group B” can only be accessed by “Group B” users:

 

Connection pool “GQ Others” can be accessed by all users:

 

Connection pool “DDR” (direct database requests) can also be accessed by “Group B” users:

 

Connection pool “Initialization” cannot be accessed by any users (at least for purposes other than initialization):

 

Now, let’s create an initialization block to populate a variable on session startup using the “Initialization” connection pool:

 

Let’s arrange the connection pools in the following order:

 

General queries for groups A and B will go to their respective pools, and all other general queries will go to connection pool “GQ Others”.

 

The “Initialization” pool could be placed anywhere in the list since no users or groups have permission to use it, but putting it after the general purpose query pools acts as an additional safety measure.

 

Connection pool “DDR” could go anywhere after connection pool “GQ Group B”, but, again, it is better placed after the connection pool “GQ Others”, which acts as a catch all (if connection pool “DDR” were placed before connection pool “GQ Group B”, then it would be used for general purpose group B queries, which is not intended).

 

Connection pool “GQ Others” must go after connection pools “GQ Group A” and “GQ Group B”; otherwise, it would be used instead of connection pools “GQ Group A” and “GQ Group B” for general purpose queries associated with these groups.

 

Connection pool “GQ Group A” could occur after connection pool “GQ Group B” as the permissions on these connection pools are specific to the corresponding user groups.

 

 

Connection Pool Example – Queries

 

In order to determine which connection pools are being used by which queries let’s create a logging table:

 

To populate the table, let’s create a PL/SQL script:

 

We’ll call this script from each connection pool, using a suitable message to identify the context pool and the call type (whether the BI Server is establishing a database connection, or whether a query is being issued using the connection pool).  For example, for connection pool “GQ Group A”, we can record when a database connection through the pool is established, and when each query is processed by the pool as follows:

 

Now, let’s shut down and then start up the BI Server and the Presentation Services.  Initially, the logging table has no rows:

 

Let’s log into Answers as “User A” and then examine the log:

 

Logging in as “User A” has created a new session.  This will fire the initialization block and send the initialization query directly to the “Initialization” connection pool (corresponding to the second line in the log).  But since the BI Server has just been brought up, none of the connection pools has been used to date.  So before the initialization query is fired the pool must first establish a connection to the database (corresponding to the first line in the log).

 

If we create and run a request in Answers, and then examine the log we find:

 

As this is a general purpose query, the BI Server starts at the beginning of the connection pool list and finds that “User A”, belonging to user group “Group A”, has permission to use the first connection pool.  So the search stops and this connection pool is used for the query.  Since this connection pool has not yet been used we can see that a connection to the database is established before the query is processed.

 

Now, let’s login as “User B”, and then examine the log:

 

Once again the “Initialization” block fires a query which is directed straight to the “Initialization” connection pool.  However, this time a connection to the database already exists since a connection has been established using this connection pool since the BI Server was last brought up.  So we only see a record of the query and not a record of the creation of a new database connection.

 

If we create and run a request in Answers, and then examine the log we find:

 

As this is a general purpose query, the BI Server starts at the beginning of the connection pool list and works its way down until it finds a valid connection pool.  “User B”, belonging to user group “Group B”, does not have permission to use the first connection pool, “GQ Group A”, so this connection pool is ignored, and the second connection pool on the list is examined.  “User B” does have permission to use the second connection pool, so the search stops here and this connection pool is used for the query.  Since this connection pool has not yet been used we can see that a connection to the database is established first before the query is executed.

 

If, as “User B” we create a direct database request in Answers, press “Validate SQL and Retrieve Columns”, and then examine the log we find:

 

For a direct database request we have specified the connection pool so the BI Server goes directly to this connection pool.  “User B” has permissions on this connection pool, so the pool is used (note “User B” or “Group B” must also be explicitly allowed to issue direct database requests by granting both DDR Presentation Services privileges and by granting the DDR privilege within the Administration Tool).

 

Here again we can see that a database connection is established before the query is issued.  When we run the direct database request by moving to the “Request” tab, and then examine the log we see that a second query has been issued:

 

The purpose of the first query was to retrieve the metadata needed to format the request; the purpose of the second query was to retrieve the actual data.

 

Finally let’s login as “User O”.  If we examine the log we see the initialization query as before:

 

If we create and run a request in Answers, and then examine the log we find:

 

As this is a general purpose query, the BI Server starts at the beginning of the connection pool list and works its way down until it finds a valid connection pool.  “User O”, belonging to user group “Group Others”, does not have permission to use the first or second connection pools, “GQ Group A” and “GQ group B”, so these connection pools are ignored, and the third connection pool on the list is examined.  “User O” does have permission to use this connection pool, so the search stops here and the connection pool is used for the query.  Since this connection pool has not yet been used a connection to the database is established first before the query is executed.

 

From this example we see that the relevant rules that we outlined in the earlier sections have being applied.

 

 

 

Everything Oracle

Home

Everything Oracle

 

Copyright © 2009 PWG Consulting, All Rights Reserved