Section 6.6. The Challenge of Non-Database Users


6.6. The Challenge of Non-Database Users

As I've explained throughout this chapter, FGA captures not only who did it, but what was donethe 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 information?

There are several approaches. Most of them work by conveying additional information about the real user in the ancillary data elements associated with the session. These elements are populated by the client and are read by the database while capturing the changes. Two key elements are the client identifier and the application context. I described those at some length in Chapter 5, so here we'll jump right into how they can be used in the FGA environment.

6.6.1. Client Identifier

Starting with Oracle9i Database, a variable-length character string can be assigned as an attribute of a user's session. It can then be retrieved later from another program through a query against the V$SESSION view , effectively providing distinguishing information about the real user. Suppose that the user connects to the database as a user named DBUSER and then issues a statement:

     BEGIN        DBMS_SESSION.set_identifier ('REAL_USER');     END;     /

This populates the CLIENT_IDENTIFIER column of the V$SESSION view with the value REAL_USER. So, from another session you will be able to see the value of this column.

     SELECT client_identifier       FROM v$session      WHERE SID = sid;

This returns REAL_USER.

This information is not limited to V$SESSION; it also shows up in the FGA trailsfor example:

     SELECT client_id       FROM dba_fga_audit_trail;

This also returns REAL_USER.

If this value is populated with the name of the real user, you will be able to accurately assign accountability to that user.

There are several ways that the client identifier can be set securely and reliably, as discussed in Chapter 5.

6.6.2. Application Contexts

Application contexts are sets of name-value pairs that can be defined in a session by executing a specially defined stored procedure. They are most commonly used to control access to database resources according to rules that vary depending on the current user and discussed in detail for row-level security in Chapter 5. As with the client identifier discussed in the previous section, they can be used to convey the identity of the real user. Either approach (or a combination of the two) gives you a way to identify non-database users.

See Chapter 5 for explanations and examples of using application contexts .




Oracle PL(s)SQL For DBAs
Oracle PL(s)SQL For DBAs
ISBN: N/A
EAN: N/A
Year: 2005
Pages: 122

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net