13 Monitoring Database Usage of the Application

Overview of Metadata

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.

Viewing Metadata

To see where the metadata values are used, login to the AnyCo application and navigate through several pages.

On Windows, from the Start menu, select Programs (or All Programs), then Oracle Database 12c , and then Go To Database Administration Page.

On Linux, click the Application menu (on Gnome) or the K menu (on KDE), then point to Oracle Database 12c, and then Go To Database Administration Page. Navigate to the Sessions page and login as the SYSTEM user. This shows

You may see multiple entries. Apache will create several processes, any one of which might handle any of the HTTP requests. As you navigate through the application pages, different Apache processes handle the page requests.

Drill down by clicking the SID number for one of the AnyCo sessions:

Client and Application information is also available on this page.

The Oracle Database administration tool shows the SQL statement and statistics about its execution. If you see any poorly tuned or heavily used statements the end-to-end tracing metadata will let you locate the source PHP files for easy review and re-design. For statements, the metadata in effect at first execution of each unique statement is the value recorded.

The accuracy of the metadata is reliant upon the consistency of use in the applications that connect to the database.

Detailed information on how the client identifier can be used is in the technical article:

http://www.oracle.com/technetwork/articles/dsl/php-web-auditing-171451.html

More Uses of Metadata

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.

You can use the client identifier metadata 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 which 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.

Metadata and Persistent Connections

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 after 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() won't 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 won't be able to clear the values or notify the database to close a non-persistent connection.