Oracle PL/SQL for DBAs

Identifying Non-Database Users

Application contexts are useful well beyond the situations we’ve described so far. A key use of application contexts is to distinguish between different users who cannot be identified through unique sessions. This is quite common in web applications that typically use a connection pool —a pool of connections to the database using a single user, named, for example, CONNPOOL. Web users connect to the application server, which, in turn, uses one of the connections from the pool to get to the database. This is shown in Figure 5-2.

Figure 5-2. Application users and RLS

Here the users Martin and King are not database users, they are web users, and the database has no specific knowledge of them. The connection pool connects to the database using the userid CONNPOOL, which is a database user. When Martin requests something from the database, the pool might decide to use the connection labeled 1 to get it from the database. After the request is complete, the connection becomes idle. If at this point King requests something, the pool might decide to use the same connection (labeled 1). Hence, from the database perspective, a session (which is actually the connection from the pool) is from the user CONNPOOL. As a consequence, the examples we showed earlier (where the USER function identifies the actual schema name) will not work to uniquely identify the user making ...