Throughout the implementation of the AnyCo application, metadata values were used for Oracle's end-to-end application tracing features. Values set were the:

• client identifier

• client information

• module

• action

The client identifier held a value uniquely associated with an end user. The other three values were effectively a descending hierarchy of data about the application's tasks.

The metadata values are semi-arbitrary text strings. Oracle records the metadata values and makes them available in certain database features, such as in the list of currently open connections. How your DBA uses those features and how your application is designed will determine what values an application should set.

In the AnyCo application the client identifier was set to simon or admin, depending on which web user was logged into the application. The client information was always set to 'AnyCo Corp.' Refer to CLIENT_INFO in ac_db.inc.php. The module was set when each page created an instance of the Db class. By choice, the module name used was always Equipment, indicating that this set of files was related to manipulating employees' equipment. The action varied with each SQL statement being run.

The Oracle Database administration pane is a simplified view of all the information Oracle database records about connections and statement execution. Various standard database administration views such as V$SESSION and V$SQLAREA will also contain the tracing metadata. You can write your own queries or use other tools to present the information.

The client identifier metadata can be used to restrict data access. In Oracle Database you could manually augment each SQL statement to restrict access by testing the client identifier:

select * from equipment
where sys_context('userenv', 'client_identifier') = 'admin';

If a web user with another client identifier was logged in, the WHERE condition would evaluate false and no rows would be returned. Only the Administrator would be able to see data.

In the Enterprise Edition of Oracle Database, the Virtual Private Database feature supports creation of policy rules that will automatically restrict access to data. This removes the need for every SQL statement to be modified. The client identifier is also recorded in the audit log when auditing is enabled.

When using the client identifier for enforcing security, is a very important to have application code integrity. Is it imperative to ensure that there is no omission or impersonation of the client identifier.

The AnyCo application uses PHP OCI8 persistent connections that are kept open even when the PHP processes are not processing scripts. The metadata values are not reset at the end of a user script and they are visible in the Session screen when the PHP process and the database connection are idle. Also the current values will remain in effect if any subsequent oci_pconnect() handled by the PHP process does not explicitly set them again.

Using non-persistent connections, calls to oci_connect() or oci_new_connect() will not have this behavior, since those connections are always closed after each PHP script completes.

In a busy system with little idle time, the left-over metadata for persistent connections is generally not an issue. The problem areas for DBAs are busy connections, not idle ones. Not un-setting the metadata gives maximum performance because it avoids a round trip between PHP and the database. This would slow down the whole system so it is not recommended. However, you could forcefully clear the metadata by adding this to the Db destructor:

        $this->stid = oci_parse($this->conn,
                      "begin
                           dbms_session.clear_identifier;
                           dbms_application_info.set_client_info('');
                           dbms_application_info.set_module('', '');
                       end;");
        oci_execute($this->stid);

If you try this, first restart the web server to close all existing PHP persistent database connections.

No solution is perfect. If the PHP process crashes it will not be able to clear the values or notify the database to close a non-persistent connection.