PHP Web Database Auditing, Authorization and Monitoring
By cj on Sep 10, 2010
Oracle Database "client identifiers" are cool because they let Oracle Database trace and monitor DB resource usage for individual web users, not just for the username that connected to the DB. You know how your web app files all connect using the same DB username like $c = oci_connect('phpuser', 'secret', 'mydb'), and so as a consequence how all your DB monitoring scripts show PHPUSER as the main (or only) resource consumer? Well by adding a simple oci_set_client_identifier($c, 'chris') call to your PHP files you can get a DB audit trail of the operations that 'chris' does, and can do real-time monitoring of the statements executed and see what his DB personal resource usage is. This can be used to track data access and highlight suspicious activity. It can identify heavy consumers or pinpoint the cause of a runaway situation. It could also be used during development to isolate the impact of a particular set of code changes from the load that other developers are putting on the dev database.
Client identifiers can also be used to automatically restrict data access. How? You create a PL/SQL function that simply returns the text string of WHERE clause and tell Oracle to automatically apply that clause whenever a particular table is accessed. The function logic and/or returned string can reference the client id. Just like a normal WHERE clause, if the clause returns true for a particular row, then the user sees that data, otherwise not. The application doesn't need any changes or recoding - the clause is automatically and consistently applied by Oracle.
There is a lot more detail on the topic in a brand new OTN article at PHP Web Auditing, Authorization and Monitoring with Oracle Database.
Thanks to everyone who reviewed and gave technical input to the article. It is much better as a result.