The case for using application userids is strong. Thus, rather than working around that requirement, the ideal solution is to use application userids in the FGA framework.

Client Identifier

Oracle9i introduced the concept of client identifier, which is a value that can be placed as an attribute of a session. Anything can be placed in this attribute and in our situation, we can use it to represent the actual user's name, such as JANE. Even though the database username is recorded as APPUSER or something else, the client identifier can hold the value JANE.

You can set the session's client identifier to a value by calling an Oracle-supplied API, dbms_session.set_identifier. The following statement sets the identifier SCOTT in the session:

EXECUTE DBMS_SESSION.SET_IDENTIFIER ('SCOTT')

When set, this identifier is visible in V$SESSION view for that session:

SELECT CLIENT_IDENTIFIER
FROM V$SESSION
WHERE AUDSID = USERENV('SESSIONID');

This will show the value SCOTT, as set earlier. So, what is the significance of this value? In addition to being on the V$SESSION view, this information also shows up in the FGA table FGA_LOG$ and subsequently in the view DBA_FGA_AUDIT_TRAIL, in the column CLIENT_ID as shown below:

SELECT DB_USER, CLIENT_ID FROM DBA_FGA_AUDIT_TRAIL;

Even though the column DB_USER shows the database user, the real application user, if placed in the identifier, can be captured in FGA trails. This is a very important concept to understand and apply when auditing application users.

The client identifier also shows up in the regular audit trails, not just the fine-grained audit trails. So, in all types of cases of auditing, from the regular type to the advanced fine-grained one, this value provides the missing link to represent the actual user. In case of regular audit trail, the table AUD$ records the client identifier, if set in the session, in the column CLIENTID. This value shows up in the dependent views such as DBA_AUDIT_TRAIL in the column CLIENT_ID.

The dilemma is how to set this value properly. Remember, in a web-enabled application with connection pooling, the database sessions service many user sessions—setting it only once in the beginning of the session will not help. Rather, the application has to set the client identifier before each call to the database so that FGA can see the actual application user id. When the database session is reused by another thread of the application, the identifier is set to that userid, identifying that user in the FGA trail.

Another approach is to set a cookie and use that value to set the client identifier. This technique is more secure, as the cookie can contain other authentication information that will not be possible to set through a regular session. The value in the identifier can then be decoded properly to get the real username, but the authentication information adds a data integrity component to the value.

Application Contexts

Using client identifier has its benefits, but also presents a serious security threat: This setup assumes that the user will set the value to the real userid, but that can never be guaranteed. A malicious user can connect and set the value to a different userid, seriously undermining the authenticity of the audit trail. In a web application, the use of cookies to store the client identifier makes that task difficult if not impossible; but in the regular applications, the use of just the client identifiers may not be secured satisfactorily. We need a more secure means of capturing the application user in the audit trails.

Enter the solution: application contexts. Application contexts are akin to session variables; once set, they can be accessed in a session any time. A different value can be set in another session and the values will not be visible across the sessions. A context has attributes, similar to the columns of a table; but unlike tables, contexts are not segment objects and the attributes can be defined at runtime, not during design.

The application context can be created with the following SQL:

create context my_app_ctx using set_my_app_ctx;

Note the clause, using set_my_app_ctx, which means that the attributes inside the context can be manipulated only through a procedure named set_my_app_ctx, defined as follows:

create or replace procedure set_my_app_ctx
(
   p_app_user in varchar2 := USER
)
is
begin
   dbms_session.set_context('MY_APP_CTX','APP_USERID', p_app_user);
end;

This procedure simply sets the attribute APP_USERID to the value passed by the input parameter by calling dbms_session.set_context API. So what happens if the user directly calls this API?

SQL> exec dbms_session.set_context('MY_APP_CTX','APP_USERID', 'JUNE')
BEGIN dbms_session.set_context('MY_APP_CTX','APP_USERID', 'JUNE'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 78
ORA-06512: at line 1

Note the error, ORA-01031:insufficient privileges, which is actually a little misleading. The user does have the execute privilege on SYS.DBMS_SESSION, but the setting of a context attribute by calling it is illegal and hence the error occurs. However, when the user calls the trusted procedure to set the context attribute:

SQL> execute set_my_app_ctx ('AAAA')

PL/SQL procedure successfully completed.

it is successful. Because the context attributes can be set only through its procedure, it is aptly called the trusted procedure. This is an important property of the application context and will be exploited in FGA.

The context attribute, once set, can be retrieved by calling the function SYS_CONTEXT. After the context is set in the code above, it can be seen by

select sys_context('MY_APP_CTX','APP_USERID') from dual;

which returns the value of the attribute. If the context can be set in a secure manner, it can be used to set the client identifier.

Based on what we know, a possible solution could look like this:

• The application executes the procedure code that sets the application context automatically to the correct value. In the above example, the context attribute for userid has been used, but another one — such as the role of the user — could have been used. You can define more than one attribute inside a context. The attribute can be used as the enabled role. The trusted procedure can include all types of security checks to make it secure and authentic. If these security checks fail, the desired role is not set. So even if the user can successfully log in using APPUSER account, he will not be able to manipulate data because the appropriate role will not be enabled. Note that the roles must be procedure-authenticated roles, not regular ones. Such roles are created by the command CREATE ROLE <role_name> USING <procedure_name>; and the user enables the role by calling the <procedure_name>, not SET ROLE command.
• This procedure also sets the client identifier; so there is no need to grant execute privileges on dbms_session to public, not even this user. Because the user does not have the privileges to call the API, they cannot set the client identifier directly—rather, it will be set automatically and carried forward to the fine-grained audit trails.

This method can be refined further using user-defined audit trails where the client identifiers can be set to a value retrieved from the application context. We'll discuss that method in third (and final) part of this series.

What Did the User See?

Fine-grained audit trails record the actual statement the user entered along with the values of bind variables, if any. Here's a typical statement that could be captured in the trail:

select balance from accounts 
where acct_no = 10034;

Say this transaction occurred at 10:00AM and now it is 11:00AM, in which case the account balance may have been updated. If the auditor (or the DBA) decides to see exactly what the user really saw at that time, the present value of the column may not reveal accurate information. In industrial espionage cases, or to establish a motive or pattern, the exact data the user viewed is necessary. Even though FGA does not capture the exact data at that point, we can see that using another piece of information captured in the trail.