PHP Web Auditing, Authorization and Monitoring with Oracle Database

by Christopher Jones

Learn to use Oracle Database's "client identifier" feature in your PHP applications.

Published September 2010

The OCI8 extension for the PHP language lets applications set a small string identifier token on each database connection. This "client identifier" can be used by Oracle Database to distinguish between individual web application users who all connect to the database using one common set of database credentials. For example, every page in a web site might physically connect to the database as the same database user PHPUSER. If two different people 'Chris' and 'Alison' are using the site, these two user names can be set as their respective client identifiers and be passed into the database.

By associating a unique client identifier with each web user Oracle Database can:

  • Provide an audit trail on individual web users
  • Automatically apply rules to individual web users to restrict data access
  • Monitor and trace applications per web user


Setting Client Identifiers


Each PHP file in a typical Oracle PHP application calls oci_connect() with an identical database user name. Once the application's own authentication system decides a particular web user is OK, then a unique token is passed back and forth in HTTP responses and requests so that the web user doesn't have to re-authenticate each time a new web page is loaded.

Implementing application level authentication and passing PHP session information need careful design to ensure security. Setting client identifiers in the application also requires care to ensure consistency of use. The overall nature of stateless web applications that utilize shared database connections means application code integrity is a very important part of ensuring data security. This article gives advice on when to set client identifiers, and on how using them helps development and management of web sites. It does not cover PHP authentication or session handling best practices.

Client identifiers should be set with oci_set_client_identifier() after connecting but before executing any statements or OCI8 calls on behalf of the web user. At its most basic, the client identifier could be the web user's name stored in PHP's session data by a previous authentication request:

session_start();
$c = oci_pconnect('phpuser', 'welcome', 'localhost/orcl');
oci_set_client_identifier($c, $_SESSION['app_user_name']);
. . .


If the identity of the end user alters during the run time of the script (perhaps if PHP is executing a long running command-line process, or perhaps in an administrative web page that runs different components representing different end users) then oci_set_client_identifier() can be called at each point the end-user identity changes:

$c = oci_pconnect('phpuser', 'welcome', 'localhost/orcl');

$myuser = 'Chris';
oci_set_client_identifier($c, $myuser);
. . .
$myuser = 'Alison';
oci_set_client_identifier($c, $myuser);
. . .


In practice, consider using more secure values for identifiers.

Client identifiers can be set when using oci_connect(), oci_new_connect(), or oci_pconnect() connection calls. Identifiers can be used when the database is configured to use any of the three types of server processes: "Dedicated" servers, "Shared" servers, and when using Database Resident Connection Pooling (DRCP) servers.

The oci_set_client_identifier() function was added in PHP OCI8 1.4 (first included in PHP 5.3.1). With older versions of OCI8 you can use the PL/SQL DBMS_SESSION package instead:

session_start();
$c = oci_pconnect('phpuser', 'welcome', 'localhost/orcl');
$s = oci_parse($c, "begin dbms_session.set_identifier(:id); end;");
oci_bind_by_name($s, ":id", $_SESSION['app_user_name']);
oci_execute($s);


The oci_set_client_identifier() function is preferred because unlike DBMS_SESSION.SET_IDENTIFIER it doesn't force a database round-trip request-and-response. With oci_set_client_identifier the identifier is piggy-backed on any subsequent OCI8 call that actually does reach the database from PHP. Unnecessary round-trips slow down each PHP page and impact application scalability.

To set the identifier in PHP frameworks, you will currently need to call OCI8 directly. In Zend Framework this could conceptually be like:

$auth = Zend_Auth::getInstance();
if ($auth->hasIdentity()) {
  $db = $this->getAdapter();
  $conn = $db->getConnection();
  if ($conn) {
    oci_set_client_identifier($conn, $auth->getIdentity()->USERNAME);
  }
}


In practice a separation of responsibilities would be preferred.

PHP OCI8 does not clear the client identifier at the end of an HTTP request since the overhead of a round-trip to clear the value would impact scalability of every application. This is not detrimental for standard OCI8 connections since the database connection is destroyed at the end of the HTTP request and the identifier value is cleared as a result. However identifiers may remain in effect across web requests that use persistent connections. To avoid an incorrect or no identifier being recorded by the database, all PHP files that connect to the database should set the identifier so it is correct for the duration of the request's execution. If this cannot be guaranteed, then every script that sets the client identifier should forcefully clear it at the script end with:

$s = oci_parse($c, "begin dbms_session.clear_identifier; end;");
oci_execute($s);


This causes a round-trip to the database.

PHP's oci_set_client_identifier() function corresponds to setting Oracle's C level OCI_ATTR_CLIENT_IDENTIFIER attribute. Oracle literature on this, and on PL/SQL's equivalent DBMS_SESSION.SET_IDENTIFIER, provide good references about client identifiers.

A Sample Application


A sample PHP "Parts" application illustrates how client identifiers can be used in the OCI8 extension. Overall, the application shows an inventory of electrical and plumbing parts. An application-level authentication system handles web user logins. For successful logins, an identifier unique for each web user is passed between HTTP requests in the PHP session data. It is used for the client identifier value. The application has just enough complexity so the Oracle technologies being discussed are not abstract, but it is no where near a production example. The sample application is simply intended to show the relationship between the web user and the database user, and to show how a client identifier can be used in the database. PHP session management requires careful design to minimize security issues. There are many external references discussing this problem which should be closely studied by every PHP developer. Michael McLaughlin's Database-Based Authentication for PHP Apps is a good place to begin reading more.

The core of the Parts application is a setup.sql file that creates the database objects. All the PHP scripts in the application will connect to the database using the PHPUSER schema, which owns the PARTS application table. The SQL script creates a second user PHP_SEC_ADMIN to hold security information about the application. This user is given some extra database privileges needed for the auditing example, shown later. The PHP_AUTHENTICATION table contains the application user names and passwords. Query access on this table is granted to the PHPUSER user so the PHP application only has to open one connection to the database, but that connection cannot modify the security information.

-- setup.sql

set echo on

-- Create PHP application user
connect system/welcome

-- Create the PHP application user
drop user phpuser cascade;
create user phpuser identified by welcome;
grant connect, resource to phpuser;
alter user phpuser default tablespace users
  temporary tablespace temp account unlock;

-- Create user owner security information about the application
drop user php_sec_admin cascade;
create user php_sec_admin identified by welcome;
alter user php_sec_admin default tablespace system 
  temporary tablespace temp account unlock;
grant create procedure, create session, create table, 
  resource, select any dictionary to php_sec_admin;

connect phpuser/welcome

-- "Parts" table for the application demo
create table parts 
  (id       number primary key, 
   category varchar2(20),
   name     varchar2(20));

insert into parts values (1, 'electrical', 'lamp');
insert into parts values (2, 'electrical', 'wire');
insert into parts values (3, 'electrical', 'switch');
insert into parts values (4, 'plumbing',   'pipe');
insert into parts values (5, 'plumbing',   'sink');
insert into parts values (6, 'plumbing',   'toilet');
commit;

connect php_sec_admin/welcome

-- Authentication table with the web user user names & passwords.
-- A real application would NEVER store plain-text passwords but this
-- article is about uses of client identifiers and not about
-- authentication.
create table php_authentication
  (app_username varchar2(20) primary key,
   app_password varchar2(20) not null);

insert into php_authentication values ('chris', 'tiger');
insert into php_authentication values ('alison', 'red');
commit;

grant select on php_authentication to phpuser;


Production applications would not use such simple passwords and would never store clear text passwords in tables. Applications could do end user authentication in a number of ways, including using LDAP.

Each script in the PHP application needs to know the Oracle DB credentials so they are stored in a common include file dbinfo.inc.php:

<?php
// dbinfo.inc.php
// All connections to the database use these credentials
define("ORA_CON_UN", "phpuser");
define("ORA_CON_PW", "welcome");
define("ORA_CON_DB", "localhost/orcl");
?>


In real life, consider using Oracle Wallet Manager and connecting with OCI_CRED_EXT instead of hard coding the database password. The operating system account for application administration, and the owner of the web server processes should be the only users with access to the wallet.

The application login page login.php is a typical simple PHP script that when first loaded displays a form:

phpclientid-f1

The login.php file looks like:

<?php

// login.php

require_once('./dbinfo.inc.php');

session_start();

function login_form($message)
{
  echo <<<EOD
  <body style="font-family: Arial, sans-serif;">

  <h2>Login Page</h2>
  <p>$message</p>
  <form action="login.php" method="POST">
    <p>Username: <input type="text" name="username"></p>

    <p>Password: <input type="text" name="password"</p>
    <input type="submit" value="Login">
  </form>
  </body>
EOD;
}

if (!isset($_POST['username']) || !isset($_POST['password'])) {
  login_form('Welcome');
} else {
  // Check validity of the supplied username & password
  $c = oci_pconnect(ORA_CON_UN, ORA_CON_PW, ORA_CON_DB);
  // Use a "bootstrap" identifier for this administration page
  oci_set_client_identifier($c, 'admin');

  $s = oci_parse($c, 'select app_username
                      from   php_sec_admin.php_authentication 
                      where  app_username = :un_bv
                      and    app_password = :pw_bv');
  oci_bind_by_name($s, ":un_bv", $_POST['username']);
  oci_bind_by_name($s, ":pw_bv", $_POST['password']);
  oci_execute($s);
  $r = oci_fetch_array($s, OCI_ASSOC);

  if ($r) {
    // The password matches: the user can use the application

    // Set the user name to be used as the client identifier in
    // future HTTP requests:
    $_SESSION['username'] = $_POST['username'];

    echo <<<EOD
    <body style="font-family: Arial, sans-serif;">

    <h2>Login was successful</h2>
    <p><a href="application.php">Run the Application</a><p>
    </body>
EOD;
    exit;
  }
  else {
    // No rows matched so login failed
    login_form('Login failed. Valid usernames/passwords ' .
               'are "chris/tiger" and "alison/red"');
  }
}

?>

If you copy this code, make sure the "heredoc" EOD tokens are at the very start of their lines.

For form submission, the script calls back to itself, which now validates the entered user name and password against the users in the PHP_AUTHENTICATION table. A client identifier of admin is set as a bootstrap value since at this initial point we don't know if we have a valid end user. Also the login script is an administrative component not doing any actual application work on behalf of an end user.

From the login page, authenticated users can click to the application inventory page:

phpclientid-f2

The user name is passed to the application page in PHP session data as $_SESSION['username']. This value will be used as the client identifier for the web user. In a real application a less obvious identifier would be recommended. For example, as part of application authentication for a successful end-user login, an initial look-up query or PL/SQL function could return a pre-computed obscure value to be used as the user's client identifier. This value would then be stored in the PHP session information for use in subsequent "real" application work. An obscure value would make it harder for attackers to predict identifier values. Also an identifier could be quickly changed if there was ever a concern about the authenticity of HTTP requests using it. 

The application page application.php checks that the user is authenticated - this application's definition of an authenticated user is simply that a user name is set. The code then sets the client identifier and shows the inventory list by querying the PARTS table:

<?php

// application.php

require_once('./dbinfo.inc.php');

session_start();

// Check the user is logged in according to our application authentication
if (!isset($_SESSION['username'])) {
  echo <<<EOD
    <h2>Unauthorized</h2>
    <p>You are not authenticated.<br>
    Valid usernames/passwords are "chris/tiger" and "alison/red"<p>

    <p><a href="login.php">Login Page</a><p>
EOD;
  exit;
}

// Generate the application page

$c = oci_pconnect(ORA_CON_UN, ORA_CON_PW, ORA_CON_DB);
// Set the client identifier after every connection call
// using a value unique for the web end user.
oci_set_client_identifier($c, $_SESSION['username']);

$username = htmlentities($_SESSION['username'], ENT_QUOTES);
echo <<<EOD
<body style="font-family: Arial, sans-serif;">
<h2>Parts Company</h2>
<table border='1'>

<caption><b>Inventory for $username </b></caption>
EOD;

$s = oci_parse($c, "select * from parts order by id");
oci_execute($s);
while (($row = oci_fetch_array($s, OCI_ASSOC+OCI_RETURN_NULLS))
        != false) {
  echo "<tr>\n";
  foreach ($row as $item) {
    echo "  <td>" .
      ($item!==null?htmlentities($item, ENT_QUOTES):"&nbsp;") .
      "</td>\n";
  }
  echo "</tr>\n";
}

echo <<<EOD

</table>
<p><a href="logout.php">Logout</a></p>
</body>
EOD;

?>


When logged in as Chris, the application shows:

phpclientid-f3

A logout script logout.php clears PHP's session information:

<?php

// logout.php

session_start();
unset($_SESSION['username']);

echo <<<EOD
<body style="font-family: Arial, sans-serif;">
<h2>Goodbye</h2>

<p>You are logged out.<p>

<p><a href="login.php">Login Page</a><p>
</body>
EOD;

?>

The logout page does not call dbms_session.clear_identifier to clear the database connection's identifier: that would need to be done in the files that use a database connection if there was concern about whether oci_set_client_identifier() was consistently used throughout the application.

The same application code will be used in the next sections without any modifications.

To summarize, this simple application is designed to show the relationship between database users and end users so that client identifiers can be discussed. It does not constitute a suitable example for production use. The application sets a client identifier with oci_set_client_identifier() immediately after each oci_pconnect() connection call. This identifier uniquely identifies the end user who is sitting at his or her web browser. For existing real-life applications, adding a call to oci_set_client_identifier() with a unique identifier per web user is the only application change that needs to be made to take advantage of client identification.

Using a Client Identifier in PHP for Auditing


Auditing lets you:

  • Identify inappropriate database changes
  • Investigate suspicious activity
  • Verify authorization or access control policies
  • Satisfy business compliance regulations
  • Gather data about database activities for use in capacity and resource allocation planning

Oracle auditing is powerful and multi-faceted. You can audit general activities such as the type of SQL statement executed. You can audit fine grained activities such as when specific values occur, or what IP address initiated a request. Auditing can occur on both successful and failed activities. The audit trail can be stored inside the database or outside it, suitable for analysis with various tools.

Setting a client identifier allows auditing to be associated with unique web users, and not just with the database table owner who authenticated the PHP OCI8 oci_pconnect() call to the database.

The auditon.sql script is a basic example of query auditing on the PARTS table:

-- auditon.sql

-- Turn on object auditing for the PARTS table

connect system/welcome

audit select on phpuser.parts by access; 
 

Run the script above in SQL*Plus. Then run the application and login as 'chris' or 'alison' (their passwords are in setup.sql). You can even query the table as the SYSTEM user in SQL*Plus outside the application:

select * from phpuser.parts;


This returns the expected parts list.

To show the audit trail from all these table accesses, the SQL script auditreport.sql queries the DBA_AUDIT_TRAIL view, which contains the audit data when the database initialization parameter AUDIT_TRAIL is set to DB.

-- auditreport.sql

-- View the audit trail for the PARTS table

connect system/welcome

set pagesize 100

col app_username format a13
col username format a13
col extended_timestamp format a37
col action_name format a13

select auth.app_username,
       dat.username,
       extended_timestamp, 
       action_name
from   dba_audit_trail dat 
       left outer join
       php_sec_admin.php_authentication auth 
       on auth.app_username = client_id
where  obj_name = 'PARTS'
order by extended_timestamp;


Running the reports shows the time each person accessed the PARTS table:

APP_USERNAME  USERNAME      EXTENDED_TIMESTAMP                    ACTION_NAME
------------- ------------- ------------------------------------- -------------
chris         PHPUSER       16-AUG-10 12.25.42.846153 PM -07:00   SELECT
alison        PHPUSER       16-AUG-10 12.25.50.870773 PM -07:00   SELECT
              SYSTEM        16-AUG-10 12.25.58.660922 PM -07:00   SELECT


There is no APP_USERNAME shown for the SYSTEM user because there was no client identifier set in the SQL*Plus session. Sometimes identifying data accesses where the client identifier is not correctly set is the desired auditing goal. Oracle's Fine-Grained Auditing can be used to audit specific events like this, helping monitor suspicious activity. This can be useful when client identifiers are used by Virtual Private Databases to restrict data access but complete auditing is not required.

When you are finished exploring the example, you can turn auditing off using the NOAUDIT command in SQL*Plus:

-- auditoff.sql

-- Turn off object auditing for the PARTS table

connect system/welcome

noaudit all on phpuser.parts;


More information about auditing can be found in the Verifying Security Access with Auditing chapter of the Oracle Database Security Guide 11g Release 2 (11.2) manual.

Using a Client Identifier in PHP with a VPD for Restricting Data Access


Limiting access to avoid misuse of sensitive data is an architectural goal of all applications. Oracle PHP applications can use the client identifier to restrict data access in a manually coded or an automatic way. The manual way is to modify every SQL and PL/SQL statement to use SYS_CONTEXT(), which returns the client identifier of the PHP connection. For example, queries could be written to returns rows from PARTS only when the identifier of the current connection is 'chris':

select * from parts
where sys_context('userenv', 'client_identifier') = 'chris';


When Alison (or any user with a different client identifier) is connected, then the WHERE clause evaluates to false and no rows will be returned. This kind of logic is cumbersome to code and error prone to consistently implement everywhere. Oracle Database Enterprise Edition's Virtual Private Database (VPD) technology comes to the rescue. It will automatically add a WHERE predicate to each statement the application executes.

To set up VPD, a PL/SQL function that returns the desired text of the restrictive WHERE clause needs to be created. To automatically restrict data returned from the query select * from parts the function would just need to return the string:

sys_context('userenv', 'client_identifier') = 'chris'


With VPD enabled to use such a function, the query would be executed by Oracle as if it had the restrictive WHERE clause, resulting in the same application behavior as discussed above for the manual implementation. Technically Oracle uses a transient view that enforces the WHERE clause and rewrites the application query to use the view instead of the base table, as described in the VPD documentation. Regardless of the implementation details, Oracle transparently handles the authorization, so security is consistent and programmers can be more productive on other tasks. Remember that from the database perspective, client identifiers are "insecure" because the database has to rely on externally provided information for policy enforcement. This is the outcome of using shared database connections and middle-tier authentication in a stateless web architecture and it places a reliance on having correct application code.

For the Parts application, the SQL script vpdon.sql sets up VPD. First it creates an application specific table of privileges. I've decided that Chris can only see electrical items, but Alison can see electrical and plumbing supplies. The VPD policy function F_POLICY_PARTS returns a subquery that checks the current client identifier has access to the part category of the row. Although F_POLICY_PARTS is passed the schema and table name that the policy is being applied to, in this example the policy is only used for one table so the function parameters are not referenced. With the policy function defined, the DBMS_RLS.ADD_POLICY procedure is used to enable it for the PARTS table.

-- vpdon.sql

set echo on

connect / as sysdba
grant execute on sys.dbms_rls to php_sec_admin;

connect php_sec_admin/welcome

-- Application policy table
drop table php_privs;
create table php_privs (username varchar2(64), category varchar2(20));

-- Chris should only see electrical items.  Alison can see
-- electrical and plumbing items
insert into php_privs values ('chris',  'electrical');
insert into php_privs values ('alison', 'electrical');
insert into php_privs values ('alison', 'plumbing');
commit;

grant select on php_privs to phpuser;

-- Policy function F_POLICY_PARTS returns a where clause to restrict access

create or replace function f_policy_parts
  (schema in varchar2, tab in varchar2) return varchar2 
as 
  predicate  varchar2(400);
 
begin
  predicate :=
   'category in 
     (select category 
      from php_sec_admin.php_privs
      where username = sys_context(''userenv'', ''client_identifier''))';
  return predicate;
end;
/
show errors

begin
    dbms_rls.add_policy (
       object_schema   => 'PHPUSER',
       object_name     => 'PARTS',
       policy_name     => 'ACCESS_CONTROL_PARTS',
       function_schema => 'PHP_SEC_ADMIN',
       policy_function => 'F_POLICY_PARTS',
       policy_type     => DBMS_RLS.STATIC);
end;
/

The policy functions in an application can be as complex as needed. Your own web sites can implement policy rules in the most suitable way for them, which is likely to be completely different to that used in this example. Take care with the POLICY_TYPE argument. Here the policy function returns a simple string, making the function identical for all uses. This means the type can be specified as STATIC allowing the function to be cached. The client identifier is not considered part of the user defined application context so if the policy function logic evaluates the identifier value then you will need to set the type to DYNAMIC.

Login to the Parts application as Chris see how the inventory list now only shows electrical supplies:

phpclientid-f4

When logged in as Alison you can continue to see everything:

phpclientid-f5

Oracle Database has a number of views for VPD management. One is the V$VPD_POLICY view that can be used to find the policies that were applied to executed SQL statements. This can be useful for debugging the values returned by the policy function.

A fun thing to do with VPD is to login to SQL*Plus as the owner of the PARTS table and check its contents:

connect phpuser/welcome
select * from parts;


No rows will be returned because the policy function F_POLICY_PARTS is applied even for the table owner. Without having an identifier validly set, the condition can never be satisfied. To remove this restriction and make administration of objects easier, Oracle has an EXEMPT ACCESS POLICY privilege for exempting users from VPD policies.

VPD is useful for more than personnel access control. The model can be extended to allow "shared hosting". The infrastructure for one application can be shared between multiple different groups of people who are never authorized to see data from any other group.

When you are finished with the VPD example, you can remove the policy by dropping it:

-- vpdoff.sql

set echo on

connect php_sec_admin/welcome

begin
   dbms_rls.drop_policy (
       object_schema => 'PHPUSER',
       object_name   => 'PARTS',
       policy_name   => 'ACCESS_CONTROL_PARTS');
end;
/


More information on VPD can be found in the Using Oracle Virtual Private Database to Control Data Access chapter of the Oracle Database Security Guide 11g Release 2 (11.2) manual.

Using a Client Identifier in PHP for Monitoring and Tracing


While many tuning projects start with the automatic performance diagnostics run by Oracle Database, or analyze overall system performance manually using AWR snapshots, this may not be possible in all environments. Sometimes on a shared system, monitoring and analyzing the behavior of one web user is more practical and simplifies the process of diagnosing performance problems in PHP. During development, the behavior of a proposed application patch can be isolated from what else is happening on the system. The client identifier allows focused monitoring via Oracle's End to End Application Tracing, a feature introduced for multi-tier applications.

To collect database statistics about a user's database resource usage, the database administrator can execute the DBMS_MONITOR.CLIENT_ID_STAT_ENABLE procedure in SQL*Plus:

connect system/welcome
execute dbms_monitor.client_id_stat_enable(client_id => 'chris');


The application can be run normally for any chosen actions and duration. Statistics can be accessed in various way, including from the V$CLIENT_STATS view. After Chris views the Parts application inventory once, the view might contain:

STAT_NAME                                VALUE
----------------------------------- ----------
user calls                                   1
DB time                                    943
DB CPU                                    2000
parse count (total)                          1
parse time elapsed                          91
execute count                                1
sql execute elapsed time                   494
opened cursors cumulative                    1
session logical reads                        7
physical reads                               0
physical writes                              0
. . .


This particular example shows a single SQL statement was parsed and executed. Standard Oracle manuals and literature describes interpreting all the values, and describe the other statistics views.

Statistics can be turned off and reset to zero with:

execute dbms_monitor.client_id_stat_disable(client_id => 'chris');


A database trace to show the SQL "Explain Plan" output for analyzing executed statements can also be turned on for each web user. It will show how statements actually got optimized - not just how you thought they would be run. The database administrator can enable tracing with the DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE procedure in SQL*Plus:

connect system/welcome
execute dbms_monitor.client_id_trace_enable(client_id => 'chris', waits => true, binds => true);


The application can then be run normally. After completion of the analysis period, tracing can be turned off with:

execute dbms_monitor.client_id_trace_disable(client_id => 'chris');


To examine the created trace files, find the trace directory using SHOW PARAMETER in SQL*Plus:

show parameter user_dump_dest


This gives output like::

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /home/oracle/app/diag/rdbms/or
                                                 cl/orcl/trace


The trace directory typically contains many trace files from normal operation. The trcsess utility can consolidate any of those created by Chris's use of the application. The consolidation (or an individual file) can then be formatted with TKPROF. For example, start a terminal window as the Oracle software owner and run:

cd /home/oracle/app/diag/rdbms/orcl/orcl/trace
trcsess output=/tmp/all.trc clientid=chris *.trc
tkprof /tmp/all.trc /tmp/tkprof.out explain=phpuser/welcome


This looks through all the trace files in the directory and aggregates those created by Chris. If you need to run trcsess on a subset of files, such as the files for a particular day, search for the client identifier near the top of the files and pass the relevant file names to trcsess. Individual trace files contain a section like:

*** 2010-08-16 15:29:12.481
*** SESSION ID:(143.943) 2010-08-16 15:29:12.481
*** CLIENT ID:(chris) 2010-08-16 15:29:12.481
*** SERVICE NAME:(orcl) 2010-08-16 15:29:12.481
*** MODULE NAME:(httpd@localhost (TNS V1-V3)) 2010-08-16 15:29:12.481
*** ACTION NAME:() 2010-08-16 15:29:12.481


Oracle Database can also name files with a given suffix, for example 'php', to make them easier to identify. Do this by executing the SQL command ALTER SESSION SET TRACEFILE_IDENTIFIER = 'php' in PHP after connecting. Database server trace file names would then look like orcl_ora_9414_php.trc. Logic would need to be added to each PHP file to decide the suffix to use, and what conditions to set it.

The output from TKPROF in tkprof.out contains analysis of the executed statements. Here is a section of the file analyzing results on my system:

SQL ID: af69s0fa3cjnp
Plan Hash: 3769467330
select * 
from
 parts order by id


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.00       0.16          0          8          0           0
Execute     33      0.00       0.00          0          0          0           0
Fetch       33      0.00       0.00          0        231          0         198
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       71      0.01       0.17          0        239          0         198

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 1602  (PHPUSER)

Rows     Row Source Operation
-------  ---------------------------------------------------
      6  SORT ORDER BY (cr=7 pr=0 pw=0 time=5 us cost=4 size=222 card=6)
      6   TABLE ACCESS FULL PARTS (cr=7 pr=0 pw=0 time=5 us cost=3 size=222 card=6)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      6   SORT (ORDER BY)
      6    TABLE ACCESS (FULL) OF 'PARTS' (TABLE)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      33        0.00          0.00
  SQL*Net message from client                    32     2219.78       5720.55
  cursor: mutex S                                 1        0.02          0.02
  library cache lock                              1        0.01          0.01
  cursor: pin S wait on X                         1        0.01          0.01
  Disk file operations I/O                        4        0.00          0.00
********************************************************************************


The manual section Understanding SQL Trace and TKPROF describes how to interpret the trace output.

For lover's of GUIs, Enterprise Manager 11g (Database Control) has a number of ways to check the impact of the application on the database. For example, to see statistics for a particular client identifier, start the Enterprise Manager console http://localhost:5500/em/ in a browser and navigate to Performance > Top Consumers > Top Clients. Set the View drop-down to "Clients with Aggregation Enabled". Click Add Client and specify the client identifier 'chris'. You can then select the row 'chris' and click the Enable SQL Trace button (this is same as DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE). Aggregation allows each run to be totalled together:

phpclientid-f6

Other areas of Enterprise Manager can also filter by client identifier, including the Top Activity report.

A single run of the small Parts application may not make Enterprise Manager's monitoring thresholds or be visible in the aggregation periods - I ran it several times in a row to capture the screen shot.

You might have noticed that trcsess and Enterprise Manager also let data be aggregated by Action and by Module. These values can be set in PHP OCI8 with the functions oci_set_action() and oci_set_module_name() respectively to identify which parts of a PHP application are being executed. Monitoring and tracing can show up the application-wide hot spots, and the SQL statements being executed can easily be identified.

Enterprise Manager is useful for tracing performance bottlenecks and tracking causes of database slowdowns during development. It allows live analysis in a large system without impacting other concurrent web users. For more information on tracing see Using Application Tracing Tools in the Oracle Database Performance Tuning Guide 11g Release 2 (11.2)

Conclusion


Client identifiers should be used by PHP web applications that allow multiple application users to connect to the database via a single database user name. The identifier is a developer chosen value that can be derived from session information about the end user already present in most web applications. Client identifiers are set by simply calling the oci_set_client_identifier() function in PHP scripts connecting to the database. Oracle Database uses identifiers to audit, automatically restrict access to sensitive data, and allow focused monitoring and tracing of resource usage. Oracle PHP applications should use client identifiers so these Oracle Database features can be utilized at any point in the lifetime of the application.



Christopher Jones is a Consulting Technical Staffer with Oracle. He is the author of the PHP and Oracle Blog.