Oracle Application Users and Auditing Security Oracle Security Tips by Burleson Consulting

This is an excerpt from the bestselling book "Oracle Privacy Security Auditing", a complete Oracle security reference with working Oracle security scripts.
 

---

Application Users and Auditing

So far we have discussed auditing user actions assuming that the user connected to the database is an actual user. In some applications, particularly third party applications, the users are authenticated by the application that then connects to the database as a generic user. For instance an application user may be called CLARA, and the application can authenticate her in a variety of ways – against a table containing the user names and their passwords or using domain authentication as in a Windows environment. After authentication the application connects to the database as a generic user, e.g. APPUSER. As far as the application is concerned, the user is CLARA. For the database, the user is APPUSER. Therefore all auditing records show the user as APPUSER, not CLARA, and all audit trails will be recorded as being done by APPUSER. This completely defeats the purpose of auditing and accountability.

The best option is to have separate user accounts and have them authenticated by the database. However, that is not a choice many DBAs have when they inherit applications written long ago.

Another option is to have Oracle Single Sign-On Option (SSO) where a secured application model authenticates the application user once and his identity is passed from tier to tier. This is perhaps the best practical option overall and Oracle is investing heavily in the identity management process – an area that is supposed to mushroom into big business in the next few years. However, the biggest obstacle to this is the infrastructure requirement and complexity it brings to the table. This setup requires Oracle Advance Networking Option (ANO, also known as Advanced Security Option) and may need Oracle Application Server as a middle tier. The ANO is a must have requirement and with it being an add-on option with the increased complexity of operating it, proves it to be quite a challenge.

So, we are left with the task of unearthing a middle ground to identify those application users without using the complicated and expensive ANO. In Chapter 5, we explained a detailed model of securely managing application users using a combination of application authentication functions, roles authenticated by procedures, and application contexts. Please revisit that Chapter for a refresher. Next we will cover how to extend the functionality of auditing.

Client Identifier

In a previous chapter, we introduced the concept of Client Identifiers, which appeared in Oracle 9i. A user can set the value of a predefined application context attribute called client_identifier as shown below.

exec dbms_session.set_identifier (‘ARUP’);

This sets an application context attribute named client_identifier to the value ‘ARUP’. This is unique to a session and stays in the memory attached to that session as long as the session is active. This has nothing to do with any other attribute of the session – the username, the client IP address, the terminal, or any other details. The same user from another session can set another value of client_identifier to identify the session.

The session can check what the current value of the client_identifier is by issuing:

select sys_context(‘USERENV’,’CLIENT_IDENTIFIER’)
from dual
/

The result comes back as:

SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')
--------------------------------------------
ARUP

From another session, if we query the data dictionary view v$session for this session, we can see the client_identifier of that session as following:

select client_identifier
from v$session
where sid  = <the SID of the session>
/

We get:

CLIENT_IDENTIFIER
-----------------------------------------------
ARUP

The usefulness of client_identifier does not stop here. Its importance extends into auditing as well. In the aud$ table, there is a column labeled CLIENTID, which stores this context attribute. In order to understand how it works, let’s see an example.

First, we enable auditing on SELECT on table CLAIMS using the following query:

audit select on claim_schema.claims by access;

Then connecting as user APPUSER, we will select from the table CLAIMS.

select * from claim_schema.claims;

The result of the query is not shown here, as it’s not relevant. This action has caused auditing to be triggered. We will see the auditing information from the trail generated.

select client_id
from dba_audit_object
where username = 'APPUSER'
and OBJ_NAME = 'CLAIMS'
/

CLIENT_ID
------------------

The CLIENT_ID column came back as NULL, because by default nothing was set in the client identifier. Next, we will issue the same query, but we will set the client identifier as follows:

SQL> connect appuser/*****
SQL> exec dbms_Session.set_identifier ('ARUP')

PL/SQL procedure successfully completed.

SQL> select * from claim_schema.claims;

When we query the audit trail, we find:

select client_id
from dba_audit_object
where username = 'APPUSER'
and OBJ_NAME = 'CLAIMS'
/

CLIENT_ID
---------------------------
ARUP

The audit trail captured the client identifier application context. If the user APPUSER logs in from two different places and sets the client identifier unique to each session, the actions go into the audit trail along with the client identifiers.

This concept can be applied in the application user concept. The client identifier can be set to some meaningful value such as the application user. So, if the application user CLARA issues the select query, the client identifier can be set to CLARA, which then goes into the audit trail. At the beginning of every session, the application must call

begin
   dbms_session.set_identifier (p_app_user);
end;

The application knows the name of the user; so it can assign the value of p_user_id variable. After that, every time the audit trail is generated, the client identifier CLARA also tags along.

Security

So far, it has been simple; but here comes a monkey wrench into the wheel. The whole mechanism is based on two very important assumptions:

* That the user will call the line dbms_session.set_identifier as the first task in a session.

* That the user will supply the correct name of the application user to the packaged procedure.

These two assumptions are fundamental to the tracking of application users; but unfortunately, these assumptions are easily circumvented. The user may not call the procedure to set the client identifier, either deliberately or inadvertently. Nothing will then go into the CLIENTID column of the aud$ table, nor will it cause an error. The application will continue to run fine without the vital auditing information. Because of this, the omission may not be even detected.

Similarly the user may not supply the correct value for the application user id. The human user Clara can login as APPUSER and then issue dbms_session.set_identifier (‘JOE’). This will record all the auditing information as being done by Joe, not Clara.

Therefore the above setup in its present form is not adequate. We need some extensive work to make it more secure.

Making the application call the procedures directly can solve the problem. After the application authenticates the user using some mechanism such as Domain Authentication, the username is known. After it connects to the database, the application can execute the procedure dbms_session.set_identifier with the proper username. The user has no chance of entering anything in there.

However, what if the user connects to the database outside the application, e.g. using SQL*Plus or some other tools like TOAD? Then the client_identifier is not set.

This is where the elaborate application user management functionality described in Chapter 5 comes handy. Instead of repeating those steps here, we will just highlight the important points. All the procedures and functions mentioned here are described in Chapter 5 and are available from the online code depot.

* All the privileges are granted to several roles corresponding to several types of users. All these roles are authenticated by procedures.

* The user APPUSER is granted all these roles, but none as default. So when a user logins in APPUSER, none of the roles are enabled, meaning the user can’t do anything at that point.

* After the user logs in as APPUSER, he has to execute a special function called is_password_correct which accepts two parameters – the application userid and the password, and returns YES, if correct and NO, otherwise.

* Inside this function, all the roles allocated to that application user are enabled by the set_role procedure.

* The user APPUSER is not given execute privileges on the package dbms_session. This will prevent the user from calling the procedure set_identifier directly.

* The is_password_correct function also sets the client identifier.

* Since the user APPUSER must know the application user’s password, the chance that someone else would logs in as APPUSER and make changes is eliminated.

* Also, since the client identifier is set automatically and the user can’t set it specifically, the authenticity of the client identifier is also maintained.

As you can see, client identifiers provide the missing link between the actual user and the database. This functionality can be extended not just to an application user management, but to auditing as well.

Other Types of Audit

You might have noticed the central theme of the auditing actions mentioned in the previous sections is auditing access to the object. However, creating a trail of activity is not just about accessing the specific objects; but other things as well – such as data. The Oracle tool AUDIT can't capture changes to the data inside objects like tables.

Similarly, if the source code of a procedural object like a function or procedure is changed, the audit command captures the fact that it was changed, but not what was changed. Typically, the changes to data find their way into the redo logs and then to the archived logs, which can be mined later to identify the changes. The changes can also be captured at the source through triggers. All these options that are beyond the capabilities of the regular audit command have been discussed in subsequent chapters in this book.

Another part of the problem in auditing is when the user only selects, not changes, the data. This does not cause any triggers to fire and this information does not go into the redo log files, and therefore can't be mined later. Unfortunately, prior to Oracle 9i, it was not possible to capture the user's select statements. In some cases, this may not seem important, but in a scenario where you are dealing with data containing PHI, auditing is mandated by HIPAA. Oracle 9i introduced a new feature called Fine Grained Auditing (FGA) that allows not merely recording of select statements, but even records selectively based on user defined filtering criteria. In Chapter 11, we will discuss details the FGA and how to use it in complicated situations such as the application user management area.

 

This is an excerpt from the book "Oracle Privacy Security Auditing". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle security and auditing scripts.