Oracle PL/SQL for DBAs

The Challenge of Non-Database Users

As I’ve explained throughout this chapter, FGA captures not only who did it, but what was done—the row that was changed, the SCN number at the time of the action, the originating terminal, and more. The most important information is, of course, the user who performed the audited action, which is captured in the DB_USER column in the view DBA_FGA_AUDIT_TRAIL.

In some cases, however, the database username does not identify a real user. In Chapter 5, in the discussion of row-level security, you saw how in some types of architecture (e.g., three-tier web systems) a single userid is used to create a pool of connections in the database. A web user connects to the application server, which, in turn, connects to the connection pool , and she is assigned one connection from that pool. After the user becomes idle, that connection may be assigned to another user. In this way, a relatively small number of database sessions can service a larger number of real users.

This scenario, however, creates a problem: how can the database identify actual users? From the database perspective, the username is the shared id used by the connection pool, not the real user behind the connection pool. Hence, the DB_USER column in the FGA trail records the shared userid, which cannot be used to assign true accountability. For accurate recording of the user performing this action, I must identify the actual, distinct user from within the pool. How can I capture that ...