December 2003

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

December 2003
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: Auditing Database Access

You’re invited to submit your Oracle-related questions to us at askdbspecialists12@dbspecialists.com. Include your name, telephone number and problem description. If your question is answered in The Specialist next month, you will receive a free copy of Chris Lawson’s book, The Art and Science of Performance Tuning. (US shipping addresses only). Cloning Oracle Applications Environments

This month’s question comes from Paul in Oak Ridge, TN: We’ve been testing the AUDIT feature on our Oracle 8.1.7 instance. We entered AUDIT ALL, AUDIT INSERT TABLE BY username, AUDIT UPDATE TABLE BY username, AUDIT DELETE TABLE BY username, and a number of other statements not included in the “ALL” designation.

Logging on as “username”, we then ran a number of SQL statements against the database via SQL*Plus. An entry for most of them shows up in SYS.AUD$ for each test we ran, but out of the nine tests ran over three days, only one entry for INSERT, UPDATE, and DELETE can be found. We have spool files of the SQL statements run to verify that the statements did actually execute. What could be the problem?

Terry Sutton of the Database Specialists team responds: When you establish auditing of statements for a user using AUDIT INSERT TABLE BY username, etc., the default of “BY SESSION” auditing is used. This means that in the AUD$ table (or DBA_AUDIT_TRAIL view), one row appears for each table on which an audited action is performed in each of the user’s sessions. If 1000 inserts and 70 deletes are performed on a table in one session, only one row will appear in the DBA_AUDIT_TRAIL view.

If you query DBA_AUDIT_TRAIL like this:

 

SELECT obj_name, sessionid, username, ses_actions, timestamp
FROM   dba_audit_trail;

you’ll get a result like (columns have been shortened for readability):

OBJ_NAME  SESSIONID USERNAME SES_ACTIONS         TIMESTAMP
-------- ---------- -------- ------------------- ------------------
EMP          328523 TSUTTON  ---S--S---S-----    04-NOV-03 14:28:12
DEPT         328523 TSUTTON  ------S---------    04-NOV-03 14:28:17
DEPT         328549 TSUTTON  ---S------------    04-NOV-03 15:28:41
DEPT         328551 TSUTTON  ------S---B-----    04-NOV-03 15:30:49
EMP          328551 TSUTTON  ------S---------    04-NOV-03 15:32:22
DEPT         328552 TSUTTON  ----------F-----    04-NOV-03 15:35:02
EMP          328552 TSUTTON  ------S---------    04-NOV-03 15:39:44

The TIMESTAMP column indicates the time of the first audited action within the session. The SES_ACTIONS column is a session summary—a string of 16 characters, one for each action type in the order ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, EXECUTE, READ. (Positions 15, and 16 are reserved for future use). The characters are: – for none, S for success, F for failure, and B for both.

So we can see that the first session performed delete(s), insert(s), and update(s) on the EMP table and insert(s) on the DEPT table. The fourth session (328552) failed trying to update the DEPT table (eleventh character of the string is ‘F’). The third session (328551) had both successes and failures while performing updates (eleventh character of the string is ‘B’). There is no way of knowing if there were one or one million of any of these actions.

If you want more detail on the user actions, then you can use the “BY ACCESS” auditing option, as in AUDIT INSERT TABLE BY username BY ACCESS.

If you query DBA_AUDIT_TRAIL like this:

 

SELECT action_name, obj_name, username, timestamp
FROM   dba_audit_trail;

you’ll get a result like:

ACTION_NAME  OBJ_NAME    SESSIONID USERNAME  TIMESTAMP
------------ ---------   --------- ----------------------------
SESSION REC  EMP         328523    TSUTTON   04-NOV-03 14:28:12
SESSION REC  DEPT        328523    TSUTTON   04-NOV-03 14:28:17
SESSION REC  DEPT        328549    TSUTTON   04-NOV-03 15:28:41
SESSION REC  DEPT        328551    TSUTTON   04-NOV-03 15:30:49
SESSION REC  EMP         328551    TSUTTON   04-NOV-03 15:32:22
SESSION REC  DEPT        328552    TSUTTON   04-NOV-03 15:35:02
SESSION REC  EMP         328552    TSUTTON   04-NOV-03 15:39:44
INSERT       PRODUCT     328561    BWILLIAMS 04-NOV-03 15:47:53
DELETE       PRODUCT     328561    BWILLIAMS 04-NOV-03 15:48:16
INSERT       PRODUCT     328587    BWILLIAMS 04-NOV-03 16:50:31
INSERT       PRODUCT     328587    BWILLIAMS 04-NOV-03 16:50:37
INSERT       PRODUCT     328587    BWILLIAMS 04-NOV-03 16:51:56

The user TSUTTON had auditing set up BY SESSION and we see that the ACTION_NAME appears as ‘SESSION REC’ in each case, which shows that auditing is being done at the session level. The user BWILLIAMS had auditing set at the access level, and here we see a listing of every insert and delete, along with the timestamp of the action, even though several inserts were performed on the same table in the same session.

You can see that you get much more detail with BY ACCESS auditing. But, of course, there is a price to pay. Every audited action adds a row to the AUD$ table (which DBA_AUDIT_TRAIL is a view of), and this table will get huge if there is a lot of audited activity. So, it will need to be watched and probably pruned occasionally.

 

Cloning Oracle Applications instances can be a daunting task. Developers are constantly asking for refreshes of both the database and code trees. With the advent of Oracle Applications 11i and its myriad port numbers, meeting cloning schedules can be challenging to say the least. The good news is that once you have upgraded to Oracle Applications patch level 11.5.8, you have at your disposal an advanced method of cloning the application tree (called Rapid Clone) and you stand a greater chance of making your customers happy. If you are running an earlier release of Oracle Applications than 11.5.8, do not despair—a patch is availabile that allows you to use Rapid Clone without upgrading application modules to 11.5.8. NoCOUG Fall Conference Wrap-up

Robert Townsend, Senior Staff Consultant at Database Specialists, has written a brief technical paper on this subject. Robert outlines ten basic steps involved in cloning an instance, along with a number of helpful tips. To read this useful overview, check out http://dbspecialists.wpengine.com/presentations.html.

 

Yet again, you’ll find some great resources on the website of the Northern California Oracle Users Group, which held a full-day conference last month. Many of the speakers made their presentations available for download. Here is a sampling of topics: A Year of Resources

  • Performance Problems from the Field—Gary Goodman, Hotsos
  • Frequently Asked Questions: A Smorgasbord of Common Questions and Problems Received by World Wide Support and How to Resolve Them—David Austin, Oracle Corporation
  • Working With Partitioned Tables: The Unpleasant Details—Brian Hitchcock, Sun Microsystems

To view these presentations and more, browse the NoCOUG website at http://www.nocoug.org/presentations.html.

 

As 2003 draws to a close, we’d like to take a moment to thank all of you for the continued success of The Specialist. We hope you have enjoyed the news and resources we have shared pertaining to Oracle technology. Highlights include a white paper on interpreting wait events to boost system performance and one on loading your data faster. They’re all free and available at http://dbspecialists.wpengine.com/resources.html.

This year we also launched the Ask DB Specialists column in which our team answers questions on topics such as system change numbers, third party grants, null events, and slow query response times. (You’ll find back issues at http://dbspecialists.wpengine.com/specialist.html.) Keep those questions coming!

We encourage your input and suggestions. And we look forward to sharing more with you in 2004!

 

Leave a Reply

Your email address will not be published. Required fields are marked *