Oracle PL/SQL for DBAs

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 trails—for 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.