Skip to Main Content
  • Questions
  • Auditing -- where are the records visible and how to find out whats enabled.

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, balraj.

Asked: June 04, 2001 - 10:26 am UTC

Last updated: July 31, 2012 - 12:20 pm UTC

Version: 8.1.6.3

Viewed 100K+ times! This question is

You Asked

Hi Tom,

When i have enabled the audting by the AUDIT Statements......

How/Where should I check the Audited information....?

Actually Oracle gives me these Tables/Views/Synonymns

1)Aud$ 2)DBA_Audit_Objcet 3)Audit$.. and many more

I want to know once I had Given Auditing command Where should i go to check the Auditd information..

Pl.Answer me in detail as how should I check the information and where should I delete the information after the Information is checked


Also I need to understand that DBA_AUDIT_OBJECT has a field

STATEMENTID

Where will i find more information about the Statement issued by the user..

I want to get the information of all the Events Audited in the database ,where will I find this


Thanks for The Answer in advance.
balraj



and Tom said...

to review the contents of the audit trail:

DBA_AUDIT_EXISTS lists audit trail entries produced by AUDIT NOT EXISTS.
DBA_AUDIT_OBJECT contains audit trail records for all objects in the system.
DBA_AUDIT_SESSION lists all audit trail records concerning CONNECT and
DISCONNECT.
DBA_AUDIT_STATEMENT lists audit trail records concerning GRANT, REVOKE,
AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the
database.
DBA_AUDIT_TRAIL lists all audit trail entries.


DBA_OBJ_AUDIT_OPTS - describes auditing options on all objects.
DBA_PRIV_AUDIT_OPTS - describes current system privileges being audited across
the system and by user.
DBA_STMT_AUDIT_OPTS - describes current system auditing options across the
system and by user.


followup to comment



the statementid doesn't tell you that.

the ACTION_NAME does.

If you are looking for the "actual statement" executed -- you can stop looking. that is not recorded in the audit trail.


Rating

  (119 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Was bit incomplete

A reader, June 04, 2001 - 12:52 pm UTC

It was helpful

but the last part was missed

I asked How could i know the SQL Statement against the staementID mentioned in the DBA_AUDIT_Object View

Pl.tell this also

Why Oracle cant audit DROP TABLE or DROP ANY TABLE etc DDL!

A reader, January 17, 2002 - 5:20 pm UTC

Hi Tom

I am trying to audit DDL in some schemas, for example create, drop, alter

I have read through the documentation in administration guide and sql reference

I tried this

1.- audit table
from sql ref it suppose to include auditing of
CREATE TABLE
DROP TABLE
TRUNCATE TABLE
and from Concepts, I am quoting here: DDL statements, regarding a particular type of database structure or schema object, but not a specifically named structure or schema object (for example, AUDIT TABLE audits all CREATE and DROP TABLE statements), however it is auditing CREATE TABLE correctly but NOT DROP TABLE (btw it is always auditing CREATE TABLE even I get rid of all audits, all DBA_STMT_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, DBA_AUDIT_OBJECT, DBA_AUDIT_SESSION, DBA_AUDIT_STATEMENT are empty, I dont understand why!!!)

2.- audit alter table
This suppose to audit every alter table command as far as I can understand from documentation since it's in Object Auditing Options (Table 8-3 from 8.1.7 SQL ref) however it is NOT working, it doesnt audit anything at all

3.- I thought I should try privilege auditing since I am auditing people with DBA Role as well so I did a small test after my desperation with statement auditing:

audit drop any table

not working neither unless this works like PL/SQL, the guy needs this priv directly instead through a role but I really doubt that?

basically what I am trying to accomplish is statement auditing rather than privilege or object auditing but it getting frustrating, nothing works except CREATE TABLE (I cant even get rid of it)! Metalink's helpless, been searching the whole afternoon without success. The documentation is somehow confusing so I wish you can give me a hand

Cheers

Tom Kyte
January 18, 2002 - 11:55 am UTC

Not knowing exactly what you did -- I cannot point out what you did wrong.  However, I can show you how to do this correctly.

I simply 

a) set audit_trail=true; and restarted.
b) ran the following:


ops$tkyte@ORA817DEV.US.ORACLE.COM> @connect "/ as sysdba"
ops$tkyte@ORA817DEV.US.ORACLE.COM> set termout off
sys@ORA817DEV.US.ORACLE.COM> GET afiedt.buf NOLIST
sys@ORA817DEV.US.ORACLE.COM> set termout on
sys@ORA817DEV.US.ORACLE.COM> 
sys@ORA817DEV.US.ORACLE.COM> truncate table aud$;

Table truncated.

sys@ORA817DEV.US.ORACLE.COM> audit table by access;

Audit succeeded.

sys@ORA817DEV.US.ORACLE.COM> audit alter table by access;

Audit succeeded.

sys@ORA817DEV.US.ORACLE.COM> 
sys@ORA817DEV.US.ORACLE.COM> @connect /
sys@ORA817DEV.US.ORACLE.COM> set termout off
ops$tkyte@ORA817DEV.US.ORACLE.COM> GET afiedt.buf NOLIST
ops$tkyte@ORA817DEV.US.ORACLE.COM> set termout on
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table audit_tab ( x int );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, action_name from dba_audit_trail;

USERNAME                       ACTION_NAME
------------------------------ ---------------------------
OPS$TKYTE                      CREATE TABLE

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table audit_tab add y date;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, action_name from dba_audit_trail;

USERNAME                       ACTION_NAME
------------------------------ ---------------------------
OPS$TKYTE                      CREATE TABLE
OPS$TKYTE                      ALTER TABLE

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table audit_tab;

Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, action_name from dba_audit_trail;

USERNAME                       ACTION_NAME
------------------------------ ---------------------------
OPS$TKYTE                      CREATE TABLE
OPS$TKYTE                      ALTER TABLE
OPS$TKYTE                      DROP TABLE


As you can see -- the create and drop (covered by the AUDIT TABLE BY ACCESS) as well as the ALTER (covered by the AUDIT ALTER TABLE BY ACCESS) were recorded.


I can only assume that in your attempts -- you entered many different audit commands and that is why the create table audit is hanging about.  To see what is in effect:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from dba_priv_audit_opts;

no rows selected

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from dba_stmt_audit_opts;

USER_NAME                      PROXY_NAME                     AUDIT_OPTION                             SUCCESS    FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
                                                              ALTER TABLE                              BY ACCESS  BY ACCESS
                                                              TABLE                                    BY ACCESS  BY ACCESS

that shows I have table and alter table and if I:


ops$tkyte@ORA817DEV.US.ORACLE.COM> @connect "/ as sysdba"
ops$tkyte@ORA817DEV.US.ORACLE.COM> set termout off
sys@ORA817DEV.US.ORACLE.COM> GET afiedt.buf NOLIST
sys@ORA817DEV.US.ORACLE.COM> set termout on
sys@ORA817DEV.US.ORACLE.COM> 
sys@ORA817DEV.US.ORACLE.COM> noaudit table;

Noaudit succeeded.

sys@ORA817DEV.US.ORACLE.COM> noaudit alter table;

Noaudit succeeded.

sys@ORA817DEV.US.ORACLE.COM> truncate table aud$;

Table truncated.

sys@ORA817DEV.US.ORACLE.COM> 
sys@ORA817DEV.US.ORACLE.COM> @connect /
sys@ORA817DEV.US.ORACLE.COM> set termout off
ops$tkyte@ORA817DEV.US.ORACLE.COM> GET afiedt.buf NOLIST
ops$tkyte@ORA817DEV.US.ORACLE.COM> set termout on
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table audit_tab ( x int );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, action_name from dba_audit_trail;

no rows selected

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table audit_tab add y date;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, action_name from dba_audit_trail;

no rows selected

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table audit_tab;

Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, action_name from dba_audit_trail;

no rows selected


I'm no longer auditing. 

A couple of questions more

A reader, January 18, 2002 - 2:09 pm UTC

Hi

I am doing this on Oracle 8.1.7 on Windows platform, it now works I dont know why...

My first question is, what is the differewnce between audit_trail=db and true??? In the documentation it suggests use none, db or os for audit_trail but I see your are using a boolean...

Second question is, in order to audit I must logon as sysdba?

The only differences between my tests and yours are these two, I used db instead of true for audit_trail parameter and I did not logon as sysdba (I did login as SYS but not with sysdba privs)

Finally, is it possible to audit a user like

audit table by user_name by session whenever successful

or we have to audit by access

Thanks a lot as usual

Tom Kyte
January 19, 2002 - 9:59 am UTC

true = db, they are the same.


You need not logon as SYSDBA, you just need the appropriate privs.

Auditing by SESSION will only get you one audit per session per action ( alter 5 times, one audit record).  By access would get you 5 records -- thats the difference.

Here is another example using circumstances like yours:

SQL*Plus: Release 8.1.7.0.0 - Production on Sat Jan 19 09:56:01 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production

ops$tkyte@ORA817DEV.US.ORACLE.COM> set echo on
ops$tkyte@ORA817DEV.US.ORACLE.COM> show parameter audit_trail

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
audit_trail                          string  DB

ops$tkyte@ORA817DEV.US.ORACLE.COM> @connect sys/manager

sys@ORA817DEV.US.ORACLE.COM> truncate table aud$;

Table truncated.

sys@ORA817DEV.US.ORACLE.COM> 
sys@ORA817DEV.US.ORACLE.COM> audit table by access;

Audit succeeded.

sys@ORA817DEV.US.ORACLE.COM> 
sys@ORA817DEV.US.ORACLE.COM> audit table by ops$tkyte by session whenever successful;

Audit succeeded.

sys@ORA817DEV.US.ORACLE.COM> 
sys@ORA817DEV.US.ORACLE.COM> @connect /

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table audit_tab ( x int );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, action_name from dba_audit_trail;

USERNAME                       ACTION_NAME
------------------------------ ---------------------------
OPS$TKYTE                      CREATE TABLE

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table audit_tab add y date;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, action_name from dba_audit_trail;

USERNAME                       ACTION_NAME
------------------------------ ---------------------------
OPS$TKYTE                      CREATE TABLE
OPS$TKYTE                      ALTER TABLE

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table audit_tab;

Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select username, action_name from dba_audit_trail;

USERNAME                       ACTION_NAME
------------------------------ ---------------------------
OPS$TKYTE                      CREATE TABLE
OPS$TKYTE                      ALTER TABLE
OPS$TKYTE                      DROP TABLE

ops$tkyte@ORA817DEV.US.ORACLE.COM> 


As you can see -- audit_trail = db, connected as SYS.   

thank you

A reader, January 19, 2002 - 10:09 am UTC

thank you very much, last problem... I copied ands pasted your statement as follows

sys@DEV817>r
1 audit table by lsc by session whenever
2* successful

Audit succeeded.

sys@DEV817>r
1* select * from dba_stmt_audit_opts

USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
-------------------- ---------- -------------- ---------- ----------
LSC TABLE BY ACCESS NOT SET

It says by access.. :-(


Tom Kyte
January 19, 2002 - 11:01 am UTC

Sorry, should have pointed out (cut and pasted from the SQL ref manual):

BY ACCESS

causes Oracle to write one record for each audited statement.

If you specify statement options or system privileges that audit data definition language (DDL) statements, Oracle automatically audits by access regardless of whether you specify the BY SESSION clause or BY ACCESS clause.

For statement options and system privileges that audit SQL statements other than DDL, you can specify either BY SESSION or BY ACCESS. BY SESSION is the default.

COMMANDS_TEXT Column

Irfan, May 13, 2002 - 5:58 am UTC

dba_audit_object and dba_audit_trial shows the actions if auditing is enabled.
Please correct me, the column COMMENT_TEXT, does it stores all the commands typed by the user in SQL*Plus like for example, if a user inserts a row in a table. If yes, this column is always blank and ACTION_NAME column is filled in.
Else can you please help me finding where can I get the full information of the row inserted (including vlaues).
Thanks

Tom Kyte
May 13, 2002 - 7:16 am UTC

We do not audit to that level -- we do not capture the actual statement or values.

You would code a custom trigger to capture that information.

comment_text

Irfan, May 13, 2002 - 7:46 am UTC

If I use trigger to write to that column, will the audit table show two entries for the same statement. One from the application (trigger) and another from the system directly.

Tom Kyte
May 13, 2002 - 7:59 am UTC

You will NOT be writing to that column.

You will design your own audit trail tables.

From Admin Guide

Irfan, May 13, 2002 - 9:30 am UTC

Tom,
The Oracle Server Administrator's Guide in Appendix B has the details of Data Dictionary. From that
For dba_audit_object, dba_audit_statement, dba_audit_trail

COMMENT_TEXT Text comment on the audit trail entry
(inserted by an application program)

-- what does 'inserted by an application program' means?
because you say 'You will NOT be writing to that column'

Thanks.

Tom Kyte
May 13, 2002 - 10:10 am UTC

Don't know what they were trying to say there. I had to go back to 7.3 to even find a server admin guide with an appendix and that one only has an appendix A, no B.

Refer to:

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/toc.htm <code>

under static data dictionary views. This text does not appear.

Same in 8i

Irfan, May 13, 2002 - 10:21 am UTC

Tom,
Thanks for the quick help. But when I refer to 8i docs for data dictionary, for example dba_audit_statement has the column

COMMENT_TEXT VARCHAR2(4000) - Text comment on the audit trail, inserted by the application

How this column can be best utilized? Thanks again.


Tom Kyte
May 13, 2002 - 10:35 am UTC

Sorry -- i read dba_audit_trail.

It is nothing you can influence. ORACLE is the application. You can reference the data in this column, you cannot influence the contents of it.

Audit Trail

Rukshan Soza, May 15, 2002 - 6:09 am UTC

Tom,

Once init.ora parameter audit_trail = true is set does it automatically start auditing ?. Or do you have to then manually start auditing (eg: audit session) ?

Tom Kyte
May 16, 2002 - 12:47 pm UTC

audit_trail = true enables auditing

you have to then use the audit command to tell us what to audit.

Overhead of audit

Jan van Mourik, May 23, 2002 - 10:27 am UTC

Tom,

how much overhead does auditing put on the database? If I do "audit session", would that have any measurable negative impact on my database performance?

jan

Tom Kyte
May 23, 2002 - 3:25 pm UTC

benchmark it -- you'll be pleasantly surprised.

Just use statspack, measure your performance now.

enable it, run with it, use statspack again

compare the results.

Reader

A reader, July 26, 2002 - 10:36 am UTC

Tom,

SQL> connect user1
Enter password: 
Connected.
SQL> grant create any procedure to user2;

Grant succeeded.


SESSIONID                     : 4843963
ENTRYID                       : 2
STATEMENT                     : 8
TIMESTAMP#                    : 2002-07-25 16:02:08
USERID                        : USER1
USERHOST                      :
TERMINAL                      : ttyq5
ACTION#                       : 108
RETURNCODE                    : 0
OBJ$CREATOR                   :
OBJ$NAME                      :
AUTH$PRIVILEGES               : -
AUTH$GRANTEE                  : USER2
NEW$OWNER                     :
NEW$NAME                      :
SES$ACTIONS                   :
SES$TID                       :
LOGOFF$LREAD                  :
LOGOFF$PREAD                  :
LOGOFF$LWRITE                 :
LOGOFF$DEAD                   : 141
LOGOFF$TIME                   :
COMMENT$TEXT                  :
SPARE1                        : oracle8
SPARE2                        :
OBJ$LABEL                     :
SES$LABEL                     :
PRIV$USED                     :


ACTION#         => 108  =>  "SYSTEM GRANT" from audit_actions;

LOGOFF$DEAD     => 141  =>  "CREATE ANY PROCEDURE" from
                            system_privilege_map
Q1:
This is a bit strange that 141 is the correct code
for System Privelege Used but it is in LOGOFF$DEAD. Is this
a BUG .

Q2:
What is the significance of ENTRYID, STATEMENT.

Thanks
 

Tom Kyte
July 28, 2002 - 2:49 pm UTC

Looks like you are looking right as sys.aud$

Don't query the data dictionary tables like that. Use the supplied DBA_ views. The aud$ table is not documented for you or me, hence you can draw no conclusions based on that. What is important is -- do the views supplied to you supply the correct information. If so, no bug.

Action Name

Frank, November 17, 2002 - 9:58 am UTC

Hi Tom,

The action_name content in DBA_AUDIT_TRAILis mostly self explanatory. However, I saw one entry "SESSION REC" in my audit trail I couldn't figure out what that "REC" stands for, "RECEIVED", "RECREATED", ... Is there a place or view I could go to find all of these details. Thanks.

Tom Kyte
November 17, 2002 - 10:53 am UTC

Action 103 is an audit record inserted in AUD$ table each time a DDL or DML
operation is audited BY SESSION. It is a session recORD

See support note:
<Note:208855.1>
on metalink.oracle.com for the long winded and complete answer with examples.

Prudent

prudent, May 30, 2003 - 12:51 pm UTC

I have set the audit_trail=db in init.ora and the database is bounced back. When I try to audit the user by using AUDIT USER commant, I am not able to see the auditing information for create user and alter user but I see the information for drop user in dba_audit_trail. Is there any other way in which I can achieve the auditing of create user and alter user.

Thanks

Tom Kyte
May 30, 2003 - 2:01 pm UTC

give me a for example, like this:

ops$tkyte@ORA920> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB
ops$tkyte@ORA920> column obj_name format a5
ops$tkyte@ORA920> select username, action_name, obj_name, priv_used from dba_audit_trail;

no rows selected

ops$tkyte@ORA920> audit user;

Audit succeeded.

ops$tkyte@ORA920> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
ops$tkyte@ORA920> connect /
Connected.
ops$tkyte@ORA920>
ops$tkyte@ORA920> create user a identified by a;

User created.

ops$tkyte@ORA920> alter user a identified by z;

User altered.

ops$tkyte@ORA920> drop user a;

User dropped.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select username, action_name, obj_name, priv_used from dba_audit_trail;

USERNAME                       ACTION_NAME                 OBJ_N PRIV_USED
------------------------------ --------------------------- ----- ----------------------------------------
OPS$TKYTE                      DROP USER                   A     DROP USER
OPS$TKYTE                      CREATE USER                 A     CREATE USER
OPS$TKYTE                      ALTER USER                  A     ALTER USER

ops$tkyte@ORA920> noaudit user;

Noaudit succeeded.
 

ip address

Reader, June 13, 2003 - 10:53 pm UTC

Tom, is it possible to capture the ip address of the machine by turning audit on. Say, for example, audit table by access or audit connect and then does dba_audit_trail capture the ip address of the machine that connected to my database in client-server configuration?

Tom Kyte
June 14, 2003 - 8:35 am UTC

as long as they use tcp/ip -- the IP address of the client has been captured in the audit trail since version 7.3 when using audit connect

Which view?

Reader, June 14, 2003 - 11:06 am UTC

Tom, could you please suggest which data dictionary view gives that? I was looking at dba_audit_trail and could not find that information. May be I could not see. Thanks as always.

Thanks a bunch!

Reader, June 14, 2003 - 2:05 pm UTC


Audit statements and restarting of Oracle instance

Olaf, May 13, 2004 - 5:45 am UTC

Tom,

just for my understanding,
if I execute audit table statement from sqlplus as sys user and then restart the Oracle instance, are all tables in database still audited?



Tom Kyte
May 13, 2004 - 10:29 am UTC

for your understanding, nothing but nothing beats documentation!


you would have to enable auditing (audit_trail=xxxx in the init.ora). After that any AUDIT commands take effect immediately.


Read the admin guide, chapter on auditing....

Failed connections

A reader, August 16, 2004 - 9:53 am UTC

Tom,

Will this audit any failed/unsuccessful connections? Like, if some user attempts to login with a wrong password or an unauthorised user attempts to login to the database.

Thanks in advance.

Tom Kyte
August 16, 2004 - 7:26 pm UTC

yes.

A reader, August 17, 2004 - 10:57 am UTC

Great!

I assume I have to query DBA_AUDIT_SESSION to know who tried to connect the database etc.

Thanks a lot again!!

Tom Kyte
August 17, 2004 - 11:06 am UTC

that or just dba_audit_trail, yes.

stmt_audit_option_map

A reader, August 26, 2004 - 3:00 pm UTC

Tom,

What is stmt_audit_option_map?

Tom Kyte
August 26, 2004 - 3:40 pm UTC

ops$tkyte@ORA9IR2> select * from dba_tab_comments where table_name = 'STMT_AUDIT_OPTION_MAP';
 
OWNER                          TABLE_NAME                     TABLE_TYPE
------------------------------ ------------------------------ -----------
COMMENTS
-------------------------------------------------------------------------------
SYS                            STMT_AUDIT_OPTION_MAP          TABLE
Description table for auditing option type codes.  Maps auditing option type nu
mbers to type names
 
 

A reader, August 26, 2004 - 3:58 pm UTC

Tom,

I wanted to make sure that no auditing currently exists here. I already checked that in AUDIT_TRAIL, DBA_OBJ_AUDIT_OPTS DBA_STMT_AUDIT_OPTS and DBA_PRIV_AUDIT_OPTS. However, so many values in STMT_AUDIT_OPTION_MAP raised a concern. Thanks

How to avoid SYSDBA from editing audit trail(OS and DB)?

Jianhui, August 31, 2004 - 2:38 pm UTC

Tom,
I believe i have seen your answer to this question before but I can't find it now. If that's true, could you post the link?
If not, could you make comments on this question? Or, is it impossible to prevent users who have ORACLE(software owner) unix account from editing (removing) audit trail? If possible, how? I personally think that's impossible because the ORACLE processes running under ORACLE user privileges in the OS level, so whatever ORACLE processes can do, ORACLE OS user can also have access(Read or write). Unless there is a way the ORACLE processes can write a file but ORACLE OS user cant write that file?

Thank you,


Tom Kyte
August 31, 2004 - 3:04 pm UTC

lock the oracle account, don't let people have access to it. It is not neccesary (cept during software install/upgrade).

In fact, using it just HIDES identities!!! Make people use their own identity to do things.

with the advent of spfiles, rman and so on -- i see no reason for a DBA to have total access to the Oracle account.




how about UTL_FILE

jianhui, August 31, 2004 - 10:12 pm UTC

Hum,
locking ORACLE user? How about SYSDBA users using UTL_FILE to edit audit trail, using java to edit OS files? I still have no solution to prevent this from happening.

What are returncode in dba_audit_trail?

A reader, September 09, 2004 - 2:59 pm UTC

Tom,

Is there any documentation for the returncode in dba_audit_trail? I started "audit connect" and have bunch of different returncodes. How can I classify them?

Thanks again.

Tom Kyte
September 09, 2004 - 3:02 pm UTC

they are the oracle error code.  eg:

ops$tkyte@ORA9IR2> select returncode from dba_audit_trail;
 
RETURNCODE
----------
         0<b>
      1017</b>
         0

 
ops$tkyte@ORA9IR2> exec dbms_output.put_line( sqlerrm(<b>-1017</b>) );
ORA-01017: invalid username/password; logon denied
 
PL/SQL procedure successfully completed.
 

A reader, September 09, 2004 - 3:29 pm UTC


trying to audit a truncate on a specific table(s)

Gene, September 10, 2004 - 5:11 pm UTC

Is there a way I can audit truncate table on just a few
tables instead of all the tables in the database? I am concernted about overhead and possible bottleneck related to auditing all the tables

thanks

Tom Kyte
September 10, 2004 - 7:32 pm UTC

well..... i'd be *really concerned* if you truncate so frequently that you'd even be worried about it?

A reader, September 13, 2004 - 3:56 pm UTC


Tom Kyte
September 13, 2004 - 8:33 pm UTC

seriously -- given the truncate is about 15 billion times more expensive then the audit trail record -- and by definition will happen ONE at a time (no multi-user issues here) -- you are worried about *the wrong thing*.

you can use a DDL trigger on the tables of interest and do your own audit.


10gr1 audit trail

A reader, October 19, 2004 - 1:29 am UTC

When I try to audit connection in 10gr1 for a proxy user,
I do not get the name of the proxy account itself in the
comment_text.
I get "Authenticated by: PROXY;EXTERNAL NAME: ..."

In 9ir2 I get "Authenticated by: PROXY: PROXY_ACCOUNT..."
as expected.

Is this a bug or is the info I am looking for
somewhere else? I could not find it in any of the dba_audit_trail columns.
There is a column proxy_sessionid - not sure how to
use that.



ok got it..

A reader, October 19, 2004 - 1:56 am UTC

following gives both proxy user and proxied user name from
dba_audit_trail in 10gr1:
select a.username proxied_user, b.username proxy_user
from dba_audit_trail a, dba_audit_trail b
where a.proxy_sessionid = b.sessionid
/

My audit didn't work.

Sean, November 15, 2004 - 5:17 pm UTC

Hi Tom,

Here is what I did, but auditing didn't work properly.

SQL> show user
USER is "SYS"
SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     FALSE
audit_trail                          string      DB
transaction_auditing                 boolean     TRUE
SQL> audit user;

Audit succeeded.

SQL> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL> connect / as sysdba
Connected.
SQL> select count(*) from dba_audit_trail;

  COUNT(*)
----------
         0

SQL> create user a identified by a;

User created.

SQL> select count(*) from dba_audit_trail;

  COUNT(*)
----------
         0

SQL>

Thanks so much for your help.

 

Tom Kyte
November 15, 2004 - 9:21 pm UTC

SYS auditing is done differently.

read about audit_sys_operations -- the parameter.

True=DB ??

Sanjay Jha, November 18, 2004 - 12:18 am UTC

Tom,
Not getting exactly the same result with TRUE but with DB I do get the same result as yours. Was wondering whether are these two really same? Although I do see your results to be same for both but what else could influence in my situation?



Tom Kyte
November 18, 2004 - 10:24 am UTC

sysdba is not audited until you set that above referenced parameter.

Audit_trail=DB, tablespace issues.

Astrid, December 08, 2004 - 1:13 pm UTC

Hello Tom,

Is there a way I can change the tablespace in the aud$ table to use another tablespace instead of the system tablespace?

Thanking you in advance for your help.

Tom Kyte
December 09, 2004 - 12:43 pm UTC

please contact support for assistance and guidance on this.

SQL statement catpture

Sven, January 04, 2005 - 7:52 am UTC

Hi Tom,

Is it possible with standard auditing i.e "AUDIT..." on a table to capture a complete SQL statement that is being audited?
In a view DBA_AUDIT_TRAIL there is a column SQL_TEXT defined as NVARCHAR2(2000) so what will happened if a statement is longer that 2000 chars?

Thanks,

Sven

Tom Kyte
January 04, 2005 - 8:42 am UTC

no

you use dbms_fga for that (selects) or triggers for modifications (in 10g, dbms_fga can capture modifications as well)

Things are working on tom computer but not on mine

Amit Gupta, February 03, 2005 - 2:23 am UTC

Hi,

I read all of the answers. it was very userful. but when i tried samething on my database it was not working.

I execute following command.
Audit select on emp;

and did some select, i found record in AUD$

then i said AUDIT TABLE. i created a table, modify it and dropt it. Then i check AUD$, there was no records for these operation. Dont know why.

Thanks 'N' Regards

Amit Gupta

Tom Kyte
February 03, 2005 - 1:18 pm UTC

worked for me, you weren't doing this "as sysdba" were you?  you know "as sysdba", if audited is audited to the OS audit trail.



sys@ORA9IR2> @printtbl8 'select * from dba_audit_trail';
PL/SQL procedure successfully completed.
 
sys@ORA9IR2> audit table;
 
Audit succeeded.
 
sys@ORA9IR2> @connect /
ops$tkyte@ORA9IR2> drop table t;
drop table t
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
 
ops$tkyte@ORA9IR2> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte@ORA9IR2> @printtbl8 'select * from dba_audit_trail';
OS_USERNAME                   : "tkyte"
USERNAME                      : "OPS$TKYTE"
USERHOST                      : ""
TERMINAL                      : "pts/4"
TIMESTAMP                     : "03-feb-2005 13:12:47"
OWNER                         : "OPS$TKYTE"
OBJ_NAME                      : "T"
ACTION                        : "12"
ACTION_NAME                   : "DROP TABLE"
NEW_OWNER                     : ""
NEW_NAME                      : ""
OBJ_PRIVILEGE                 : ""
SYS_PRIVILEGE                 : ""
ADMIN_OPTION                  : ""
GRANTEE                       : ""
AUDIT_OPTION                  : ""
SES_ACTIONS                   : ""
LOGOFF_TIME                   : ""
LOGOFF_LREAD                  : ""
LOGOFF_PREAD                  : ""
LOGOFF_LWRITE                 : ""
LOGOFF_DLOCK                  : ""
COMMENT_TEXT                  : ""
SESSIONID                     : "3338"
ENTRYID                       : "3"
STATEMENTID                   : "12"
RETURNCODE                    : "0"
PRIV_USED                     : ""
CLIENT_ID                     : ""
SESSION_CPU                   : ""
-----------------
OS_USERNAME                   : "tkyte"
USERNAME                      : "OPS$TKYTE"
USERHOST                      : ""
TERMINAL                      : "pts/4"
TIMESTAMP                     : "03-feb-2005 13:12:39"
OWNER                         : "OPS$TKYTE"
OBJ_NAME                      : "T"
ACTION                        : "12"
ACTION_NAME                   : "DROP TABLE"
NEW_OWNER                     : ""
NEW_NAME                      : ""
OBJ_PRIVILEGE                 : ""
SYS_PRIVILEGE                 : ""
ADMIN_OPTION                  : ""
GRANTEE                       : ""
AUDIT_OPTION                  : ""
SES_ACTIONS                   : ""
LOGOFF_TIME                   : ""
LOGOFF_LREAD                  : ""
LOGOFF_PREAD                  : ""
LOGOFF_LWRITE                 : ""
LOGOFF_DLOCK                  : ""
COMMENT_TEXT                  : ""
SESSIONID                     : "3338"
ENTRYID                       : "1"
STATEMENTID                   : "10"
RETURNCODE                    : "942"
PRIV_USED                     : ""
CLIENT_ID                     : ""
SESSION_CPU                   : ""
-----------------
OS_USERNAME                   : "tkyte"
USERNAME                      : "OPS$TKYTE"
USERHOST                      : ""
TERMINAL                      : "pts/4"
TIMESTAMP                     : "03-feb-2005 13:12:44"
OWNER                         : "OPS$TKYTE"
OBJ_NAME                      : "T"
ACTION                        : "1"
ACTION_NAME                   : "CREATE TABLE"
NEW_OWNER                     : ""
NEW_NAME                      : ""
OBJ_PRIVILEGE                 : ""
SYS_PRIVILEGE                 : ""
ADMIN_OPTION                  : ""
GRANTEE                       : ""
AUDIT_OPTION                  : ""
SES_ACTIONS                   : ""
LOGOFF_TIME                   : ""
LOGOFF_LREAD                  : ""
LOGOFF_PREAD                  : ""
LOGOFF_LWRITE                 : ""
LOGOFF_DLOCK                  : ""
COMMENT_TEXT                  : ""
SESSIONID                     : "3338"
ENTRYID                       : "2"
STATEMENTID                   : "11"
RETURNCODE                    : "0"
PRIV_USED                     : "CREATE TABLE"
CLIENT_ID                     : ""
SESSION_CPU                   : ""
-----------------
 
PL/SQL procedure successfully completed.
 

Auditing Non Application Users

Kari Jacobs, February 07, 2005 - 5:21 pm UTC

Is it possible to EASILY audit a non-appplication user event? My system has an application owner (schema owner) and an application user (which comes from a middle-tier). I can limit this application user by grants, but there will be other ids in the database (DBAs and/or Developers). My requirements are to audit every time someone doesn't use the application the way it is intended. For example, a DBA or a developer logs onto the database and performs a query against the application owner's table. I can easily limit the tables I want to audit, but I may have a number of user ids that I want to monitor, and I prefer not to list each individual id in the 'BY' clause. Any suggestions?

Tom Kyte
February 08, 2005 - 1:14 am UTC

write a plsql routine that reads a table of users you are interested in monitoring and builds the appropriate audit statements I suppose.

That is, make it table driven.


Although I don't see why you need to limit by userid.

<quote>
My requirements are to audit
every time someone doesn't use the application the way it is intended
</quote>

so, why would anyone be exempt from that rule?

what is the name of os file

Anurag, February 11, 2005 - 12:21 am UTC

Hi,

When we say Audit_trail=OS, Where and what is the name of audit file it stores audit information.

regards

Tom Kyte
February 11, 2005 - 8:01 pm UTC

$ORACLE_HOME/rdbms/audit (unix) or the "event viewer" (windows)

audit file name (unix) is named after the "session", like ora-12343.aud



8.1.7.4

Sean, February 17, 2005 - 3:10 pm UTC

Tom,
i have setup the db audit trail and the audit for the statement ALTER SYSTEM.

i checked sys.aud$ and only found the action#49 (ALTER SYSTEM) and the statement=18.
What's statement=18? I need to know what the alter system statement issued.

Thanks

Tom Kyte
February 17, 2005 - 6:53 pm UTC

it audits that they used alter system, it does not capture the specifics of what they did.

statement is just an increasing number for that session -- to order by.

Sean, February 17, 2005 - 10:09 pm UTC

Is there any way to audit the specific ALTER SYSTEM?

Tom Kyte
February 18, 2005 - 7:24 am UTC

not that I am aware of, it fires no triggers, AUDIT captures the fact a person used a privilege, streams cannot mine this from the logs (it is not logged into the logs).


Only way I would know to do it would be to implement your own

create procedure alter_system( .... )


as a user with alter system and place whatever auditing/logging in there you wanted and use that procedure instead of granting alter system to users.

reader

A reader, March 15, 2005 - 11:23 am UTC

dba_audit_session.returncode

Is there a view that can be used to decode them or
just manually decode them using possible occurrance
of values (1017, 942 etc.)

Tom Kyte
March 15, 2005 - 11:50 am UTC

ops$tkyte@ORA9IR2> exec dbms_output.put_line( sqlerrm(-1017) );
ORA-01017: invalid username/password; logon denied
 
PL/SQL procedure successfully completed.
 
 

OK

James, March 29, 2005 - 12:27 pm UTC

Hi Tom,
I am not able to audit a session.
What can be the reason for this?
I use Oracle Version 9iRelease 2.

SQL> alter system set audit_trail = true scope = spfile
  2  /

System altered.

SQL> audit delete on scott.emp by session whenever successful
  2  /

Audit succeeded.

SQL> conn hr/hr
Connected.

SQL> delete from scott.emp where deptno = 20
  2  /

5 rows deleted.

SQL> roll back
Rollback complete.

SQL> conn sys/oracle as sysdba
Connected.

SQL> select comment_text from dba_audit_trail where username =       'HR'
  2  /

no rows selected.


 

Tom Kyte
March 29, 2005 - 12:31 pm UTC

you are missing 

SQL> startup force;


you altered the audit trail setting in the parameter file, it hasn't taken effect however, not until  you restart. 

A reader, April 06, 2005 - 8:56 am UTC

Tom,

ops$tkyte@ORA920> show parameter audit_trail

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB
.
.
.
ops$tkyte@ORA920> audit user;

audit trail is set and after that you issue audit user. if the instance is restarted should we have to reissue all the auditing options we enabled?

Thanks


Tom Kyte
April 06, 2005 - 9:18 am UTC

no, they stick around until you turn them off.

what are all the audit options enabled?

A reader, April 07, 2005 - 6:06 am UTC

Tom,

Thanks for the reply, can you please tell me which dba table i can refer to, to know what are all the audit options enabled?

Thanks

Tom Kyte
April 07, 2005 - 10:15 am UTC

DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS


aud$ table location

ana, April 07, 2005 - 12:06 pm UTC



We have until now used OS audit_trail. But now we are forced to use aud$ instead. I have seen documents on metalink that instructs how to move aud$ to out of system tablespace. But I am little wary of doing this when I know Oracle 'does not support it'.

Also if I choose not to move, and instead just purge audit data from time to time, how far-reaching will be the resulting fragmentation in system tablespace.

I would like to hear your thoughts and suggestions.

Thanks

Tom Kyte
April 07, 2005 - 12:22 pm UTC

you'll want to work with support on this topic. (and if you use 9i with LMTS, system will be an auto-allocate extent size thing....)

DBA_AUDIT_EXISTS

Nitin, April 22, 2005 - 1:51 pm UTC

Can you provide an example of using DBA_AUDIT_EXISTS?

Tom Kyte
April 22, 2005 - 1:56 pm UTC

assuming audit not exists had been run, then:



ops$tkyte@ORA9IR2> select * from dba_audit_exists;
 
no rows selected
 
ops$tkyte@ORA9IR2> select * from not_here;
select * from not_here
              *
ERROR at line 1:
ORA-00942: table or view does not exist
 
 
ops$tkyte@ORA9IR2> select * from dba_audit_exists;
 
OS_USERNAME
-------------------------------------------------------------------------------
USERNAME
------------------------------
USERHOST
-------------------------------------------------------------------------------
TERMINAL
-------------------------------------------------------------------------------
TIMESTAMP OWNER
--------- ------------------------------
OBJ_NAME
-------------------------------------------------------------------------------
ACTION_NAME                 NEW_OWNER
--------------------------- ------------------------------
NEW_NAME
-------------------------------------------------------------------------------
OBJ_PRIVILEGE    SYS_PRIVILEGE
---------------- ----------------------------------------
GRANTEE                         SESSIONID    ENTRYID STATEMENTID RETURNCODE
------------------------------ ---------- ---------- ----------- ----------
CLIENT_ID                                                        SESSION_CPU
---------------------------------------------------------------- -----------
tkyte
OPS$TKYTE
 
 
22-APR-05 OPS$TKYTE
NOT_HERE
SELECT
 
 
                                     4789          2          13        942
 
 

Audit ALTER SESSION SET cursor_sharing=force

Michael, April 27, 2005 - 1:23 am UTC

Hello Tom,

I'd like to audit the particular Statement
ALTER SESSION SET cursor_sharing=force.

After invoking AUDIT ALTER SESSION,
NO record was found in DBA_AUDIT_TRAIL!!
But issuing for example ALTER SESSION SET sql_trace=false a record IS inserted!

What's the difference betweeen those two ALTER's?
How can I audit in particular altering cursor_sharing?
And more importantly: How can I PREVENT users from altering their session setting cursor_sharing?

I tried even a DDL-Trigger, but the trigger does NOT fire after issuing ALTER SESSION set cursor_sharing=force, whereas it fires for sql_trace!!

There must be a way to prevent users from ALTER SESSION set cursor_sharing=force???


Any help is very appreciated!

Michael




Tom Kyte
April 27, 2005 - 7:52 am UTC

cursor sharing (sort area size) and others do not require "alter session", only things that require the alter session privilege are audited by audit alter session (you are auditing the use of a PRIVILEGE, not a command really)

You cannot prevent an end user who can type in arbitrary statements from doing many alter sessions, they are allowed to, no privilege is needed beyond create session.

In order to prevent users from doing things -- access to the database should be via an application. Otherwise, why shouldn't they be able to use cursor_sharing and such?

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_22a.htm#2052902 <code>

Prerequisites

To enable and disable the SQL trace facility, you must have ALTER SESSION system privilege.

To enable or disable resumable space allocation, you must have the RESUMABLE system privilege.

You do not need any privileges to perform the other operations of this statement unless otherwise indicated.


Audit ALTER SESSION SET cursor_sharing=force

Michael, April 27, 2005 - 10:07 am UTC

I'd like to prevent the developers from using cursor_sharing and to rely on that they use bind variables instead. I am afraid they would rely simply on cursor_sharing, instead of coding correctly ;)

How can I (technically) force them not to play with cursor_sharing.

Regards,

Michael

Tom Kyte
April 27, 2005 - 10:19 am UTC

you cannot. it is there for all. You can DETECT the use of it, you cannot PREVENT the use it.

Would you like to detect it? the signature is very easy to see.

Audit ALTER SESSION SET cursor_sharing=force

Michael, April 27, 2005 - 10:28 am UTC

Yes, detecting it would maybe suffice.
What would I need to do for that?



Tom Kyte
April 27, 2005 - 10:32 am UTC

ops$tkyte@ORA9IR2> alter session set cursor_sharing=force;
 
Session altered.
 
ops$tkyte@ORA9IR2> select 1 from dual look_for_me;
 
         1
----------
         1

 
ops$tkyte@ORA9IR2> select sql_text from v$sql where sql_text like '%look_for_me';
 
SQL_TEXT
-------------------------------------------------------------------------------
select :"SYS_B_0" from dual look_for_me


look in v$sqltext_with_newlines (entire sql statement) for ANYTHING with :"SYS_B_0" in it
 

Audit drop table

Parag Jayant Patankar, July 13, 2005 - 8:55 am UTC

Hi Tom,

In Oracle 9iR2 can I audit a particular table "when it was dropped ?" I do not want to set auditing for select/insert/update on this table only I want to know when it was dropped.

Will you pl show me how to do that ?

regards & thanks
pjp

Tom Kyte
July 13, 2005 - 12:51 pm UTC

sort of funny -- if you audit a drop for "a table" it'll only ever audit one thing...

you can audit DDL on the schema, check out the AUDIT command:

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_48a.htm#2059074 http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_48a.htm#2059526 <code>


audit drop table ?

Parag Jayant Patankar, July 14, 2005 - 12:59 am UTC

Hi Tom,

Thanks for your documentation links. I have tried to put following but something I am doing wrong

10:16:58 SQL> audit drop on parag.t;
audit drop on parag.t
      *
ERROR at line 1:
ORA-00956: missing or invalid auditing option


10:18:29 SQL> audit drop table on parag.t;
audit drop table on parag.t
      *
ERROR at line 1:
ORA-00956: missing or invalid auditing option

Sorry I am not able to locate error with syntax diagram. Kindly help me by showing correct command.

regards & thanks
pjp 

Tom Kyte
July 14, 2005 - 10:26 am UTC

schema, not table. you can audit table drops.

if you want to audit the dropping of a single table, that is sort of "not useful". It only happens ONCE. If you drop the table, thats it.

Are you really trying to prevent this table from being dropped.

auditing

parag jayant patankar, July 14, 2005 - 10:32 am UTC

Hi Tom,

In a test server we are running big application system. While testing we found that one particular table is getting dropped. But we are not able to identify when table is getting dropped and at what time. So by knowing this informationwe can isolate the problem.

If a DDL auditing on a particular table is not possible pl suggest me alternate way to do this.

regards & thanks
pjp

Tom Kyte
July 14, 2005 - 11:21 am UTC

ahh, finally "the goal"


</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3109217869403 <code>

"before drop on schema"

look at the ora_dict_obj_name and log any details from the v$ tables you want.


A reader, October 14, 2005 - 11:44 am UTC

Tom,

What could be the conditions/reasons for DBA_AUDIT_TRAIL.TERMINAL to be null assuming auditing is turned on.

Thanks.

session_cpu in dba_audit_session

Olaf, October 21, 2005 - 9:11 am UTC

Tom,

What value has session_cpu in dba_audit_session? Is it 0.01 of sec (means if I have 600 in the columns it is actually 1 sec)
It is not documented by Oracle. I spent an hour on Oracle site and could not find this.

Thanks

Olaf

Tom Kyte
October 21, 2005 - 10:22 am UTC

sys@ORA10GR2> connect /
Connected.
ops$tkyte@ORA10GR2> set echo on
ops$tkyte@ORA10GR2> @mystat "CPU used by this session"
ops$tkyte@ORA10GR2> set echo off

NAME                                                    VALUE
-------------------------------------------------- ----------
CPU used by this session                                    0
IPC CPU used by this session                                0
global enqueue CPU used by this session                     0
gc CPU used by this session                                 0

ops$tkyte@ORA10GR2> declare
  2          l_date date := sysdate;
  3  begin
  4          loop
  5                  exit when sysdate-l_date >= 1/24/60/60*5;
  6          end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> @mystat "CPU used by this session"
ops$tkyte@ORA10GR2> set echo off

NAME                                                    VALUE
-------------------------------------------------- ----------
CPU used by this session                                  556
IPC CPU used by this session                                0
global enqueue CPU used by this session                     0
gc CPU used by this session                                 0

ops$tkyte@ORA10GR2> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ops$tkyte@ORA10GR2> connect /
Connected.
ops$tkyte@ORA10GR2> set echo on
ops$tkyte@ORA10GR2> select username, session_cpu from dba_audit_trail;

USERNAME                       SESSION_CPU
------------------------------ -----------
OPS$TKYTE
OPS$TKYTE                              556


<b>would appear to be identical to the value in v$sysstat, in hundredths of seconds.</b>

 

A reader, October 21, 2005 - 11:56 am UTC

Thanks, Tom.

Your answer was really very useful for me. But what I don't understand is why Oracle use different format in each view:

select max(cpu_time) from v$sql;

MAX(CPU_TIME)
-------------
5121950000

it can't a ´0.01 of seconds. Why Oracle has 0,01 of seconds on one place, 0.001 in another (like old statspack report) and simple seconds in new statspack.
Before I can understand the number above (5121950000 is it
5121.950000 sec?) I have to spend hours?

dba_audit_session.userhost shows "1"

Sami, November 07, 2005 - 3:02 pm UTC

Dear Tom,

Q1)

Why userhost column shows "1" instead of the actual hostname? The user did login from remore host and I could see the correct host name in V$session.

select count(*) from dba_audit_session
COUNT(*)
----------
44
1 row selected

select distinct userhost from dba_audit_session;
USERHOST
------------------------------------------------
1

Q2)

What is the difference beteen
a)audit connect by USERA
and
b)audit create session by USERA ?

Thanks in advance.

Tom Kyte
November 08, 2005 - 9:39 pm UTC

any chance that is the hostname? I cannot reproduce.

<quote>
Rather than specifying many individual system privileges, you can specify the roles CONNECT, RESOURCE, and DBA. Doing so is equivalent to auditing all of the system privileges granted to those roles.
</quote>

audit connect audits the use of any priv granted to connect - so in 10gr2 - they would be the same, but before, audit connect would audit more stuff.

How to find hostname from auditing view/table

Sami, November 09, 2005 - 5:09 pm UTC

Thanks Tom.

Oracle document says DBA_AUDIT_SESSION.USERHOST is "
Numeric instance ID for the Oracle instance from which the user is accessing the database"

My question is where can I get the hostname as part of auditing?


Tom Kyte
November 11, 2005 - 10:28 am UTC

which document says that?

Forgot to mention Release info in my previous comment

Sami, November 09, 2005 - 8:58 pm UTC

9i R2 -3 Node RAC, Solaris 2.9

which document says that?

Sami, November 12, 2005 - 1:25 am UTC

Static Data Dictionary Views, 203 of 666
</code> http://www.cs.umb.edu/cs634/ora9idocs/server.920/a96536/ch2203.htm <code>

Tom Kyte
November 12, 2005 - 10:53 am UTC

ahh, I was reading:

</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3054.htm#sthref1700 <code>

USERHOST VARCHAR2(128) Client host machine name


it changed.

but anyway, v$instance - it has the instance number and the host_name, but it is the hostname of the server that was used.

In 9iR2, how to get the client host name from auditing information?

Sami, November 12, 2005 - 11:51 pm UTC

Thanks Tom,
In 9iR2, how to get the client host name from auditing information?

Tom Kyte
November 13, 2005 - 10:31 am UTC

if you have audit connect, you will see:

sys@ORA9IR2> select comment_text from dba_audit_trail;

COMMENT_TEXT
-------------------------------------------------------------------------------
Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.16
8.0.5)(PORT=1040))



the host= was my clients IP address

A reader, March 04, 2006 - 11:51 am UTC

Hi Tom,
Here is my requirments...
I want to audit table when a row is deleted from the table. i.e insert primary key column value in audit tables.

can I achive this using Oracle audit trail functionality?

Thanks




Tom Kyte
March 05, 2006 - 1:31 pm UTC

that would be a trigger, or the transactional API you write in PLSQL that everyone calls that does that.

Capture SQL

Anto, March 20, 2006 - 4:29 pm UTC

Hi Tom,

Is there any way we can capture all the SELECTs(only queries) executed by one particular user(not all users so that performance is not adversely affected) into a table via auditing or via login/logoff trigger or fine grain auditing etc ?

thanks
Anto

Tom Kyte
March 22, 2006 - 1:33 pm UTC

sys@ORA10GR2> grant create session to a identified by a;
 
Grant succeeded.
 
sys@ORA10GR2> grant create session to b identified by b;
 
Grant succeeded.
 
sys@ORA10GR2> grant create session to c identified by c;
 
Grant succeeded.
 
sys@ORA10GR2>
sys@ORA10GR2> connect /
Connected.
ops$tkyte@ORA10GR2> drop table emp;
 
Table dropped.
 
ops$tkyte@ORA10GR2> drop table dept;
 
Table dropped.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create table emp as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA10GR2> create table dept as select * from scott.dept;
 
Table created.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> begin
  2          dbms_fga.add_policy
  3          ( object_schema   => user,
  4            object_name     => 'EMP',
  5            policy_name     => 'GET_A',
  6            audit_condition => 'user = ''A'' ' );
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'EMP' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> grant select on emp to a;
 
Grant succeeded.
 
ops$tkyte@ORA10GR2> grant select on emp to b;
 
Grant succeeded.
 
ops$tkyte@ORA10GR2> grant select on emp to c;
 
Grant succeeded.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> connect a/a
Connected.
a@ORA10GR2> select ename from ops$tkyte.emp where rownum = 1;
 
ENAME
----------
SMITH
 
a@ORA10GR2> connect b/b
Connected.
b@ORA10GR2> select ename from ops$tkyte.emp where rownum = 1;
 
ENAME
----------
SMITH
 
b@ORA10GR2> connect c/c
Connected.
c@ORA10GR2> select ename from ops$tkyte.emp where rownum = 1;
 
ENAME
----------
SMITH
 
c@ORA10GR2> connect /
Connected.
ops$tkyte@ORA10GR2> exec print_table( 'select * from dba_fga_audit_trail' )
.SESSION_ID                   : 1214
.TIMESTAMP                    : 22-mar-2006 13:08:31
.DB_USER                      : A
.OS_USER                      : tkyte
.USERHOST                     : xtkyte-pc.us.oracle.com
.CLIENT_ID                    :
.ECONTEXT_ID                  :
.EXT_NAME                     : tkyte
.OBJECT_SCHEMA                : OPS$TKYTE
.OBJECT_NAME                  : EMP
.POLICY_NAME                  : GET_A
.SCN                          : 18495655
.SQL_TEXT                     : select ename from ops$tkyte.emp where rownum =
1
.SQL_BIND                     :
.COMMENT$TEXT                 :
.STATEMENT_TYPE               : SELECT
.EXTENDED_TIMESTAMP           : 22-MAR-06 01.08.30.713951 PM -05:00
.PROXY_SESSIONID              :
.GLOBAL_UID                   :
.INSTANCE_NUMBER              : 0
.OS_PROCESS                   : 10772
.TRANSACTIONID                :
.STATEMENTID                  : 10
.ENTRYID                      : 1
-----------------
 
PL/SQL procedure successfully completed.
 
 

A new view?

Mike, March 21, 2006 - 10:30 am UTC

I stumbled across some views called 

KU$_AUDIT_OBJ_BASE_VIEW
KU$_AUDIT_OBJ_VIEW
KU$_AUDIT_VIEW
SM$AUDIT_CONFIG

I cannot find ANY reference to these in the 9.2 docs, or on Metalink, or even out on Google. I queried one of them as follows:

SQL> desc sys.ku$_audit_view

 Name              Null?    Type
 ----------------- -------- ------------
 VERS_MAJOR                 CHAR(1)
 VERS_MINOR                 CHAR(1)
 USER_NUM                   NUMBER
 USER_NAME                  VARCHAR2(30)
 PROXY_NUM                  NUMBER
 AUDIT_OPTION               VARCHAR2(40)
 PROPERTY                   NUMBER
 SUCCESS                    NUMBER
 FAILURE                    NUMBER

select user_name, audit_option, property, success, failure from sys.ku$_audit_view;

USER_NAME  AUDIT_OPTION      PROPERTY   SUCCESS   FAILURE
---------- ---------------- --------- --------- ---------
PUBLIC     ALTER SYSTEM             0         2         2
PUBLIC     CREATE SESSION           0         0         2
PUBLIC     ALTER DATABASE           0         2         2

Can you point me to a reference that defines this view (and the others) and how to interpret it? Whatever this means, it was done by a predecessor.

 

Tom Kyte
March 22, 2006 - 2:23 pm UTC

these are not documented, not part of the external data dictionary that is to be used.

they are not for us. use at your own risk.

how to audit system privileges.

raju, March 21, 2006 - 11:41 am UTC

I am worndering sometimes it works when i seutp and sometimes it does not work like grant any privilege audit etc. Tom can you please help me to know what is the best way to configure and setup audit for auditing system privileges.

Tom Kyte
March 22, 2006 - 2:30 pm UTC

er?

things pretty much work consistently - so "sometimes it does not work" does not "compute".

the "best way" depends on what you want to capture. Basically, enable auditing (audit_trail=xx in init.ora), and the use the AUDIT command to capture the information you want to capture.

Audit DBA

A reader, April 06, 2006 - 12:16 am UTC

First of all, I'd like to quote Jonathan Lewis as follows:

"When youÂ’ve got a problem, stripping it back to the simplest scripted test case is often enough to make you understand what the problem is and how to fix it. But even if you canÂ’t spot the problem, youÂ’ve got a rock-solid, unambiguous statement of the problem to pass on to someone else. Maybe the problem will be obvious to them the moment they read, or run, the script."

I tried to read everything about this even though I can't see the solution. It could be simple anyway.

I have this scenario: Oracle 9.2.0.6 - HP-UX 11i and went through the Admin Guide #26 (Auditing Database Use)

"After you have a clear understanding of the reasons for auditing,
you can devise an appropriate auditing strategy and avoid unnecessary auditing."

I have clear reason for having audit:
- Auditors will want to see any action comes from dba users such as apply patch.

Therefore the DBA users should be audited (either action DML or DDL). In that schema we have this:

OBJECT_TYPE COUNT(*)
------------------ -------------
DATABASE LINK 4
FUNCTION 493
INDEX 6578
LIBRARY 21
LOB 2
PACKAGE 2911
PACKAGE BODY 2807
PROCEDURE 892
SEQUENCE 17524
SYNONYM 11557
TABLE 8583
TRIGGER 781
TYPE 6
VIEW 1568

14 rows selected.

As you can see a huge schema. When a patch has to be apply, the dba logs on, run the script (such as update ie) and leaves the system. That action needs to be tracked down in somewhere "as is" in way off independent (audit trail). After that, we're going to join the log with the ticket to be documented the action and close the issue.

Any insight?

Tom Kyte
April 06, 2006 - 10:01 am UTC

not sure what you are looking for - we can audit schema changes, but the audit routines do not have the concept of "apply patch" at all.

You would/should be using OS auditing and the individual audits performed would be collected in the file system which would then be archived off.

Audit an user

Marcio, April 06, 2006 - 10:48 am UTC

Suppose this:

users a, b and c are dbas. They need to be audited every time that they get on database. Thus every action either DML or DDL should be pointed out "as is", I mean:

update t set x = 10;
...

I saw that sysdba can be audited on OS level and the file has that information as is. I'm looking for something similar because I wouldn't like to use "as sysdba" frequently.


Tom Kyte
April 07, 2006 - 4:19 pm UTC

that sort of auditing - of the dml statement to the filesystem - is in 10gr2

we can use dbms_fga to capture that


</code> http://docs.oracle.com/docs/cd/B19306_01/network.102/b14266/cfgaudit.htm#sthref1859 <code>

Thanks for the Help

Marcio Portes, April 06, 2006 - 11:58 am UTC

Thanks Tom, but I guess it is not possible what I was looking for.
According to Note:309798.1 "How to Trace Specific Database Users to Collect Full DML Statements Executed" from metalink the way to go is exactly what I have done at first shot. I used a logon trigger to enable a sql tracing for the DBAs.

Regards,

Alexander the ok, April 07, 2006 - 4:29 pm UTC

Tom,

Does auditing offer ways to track changes for records in tables from one point in time to another?

Like say I wanted to know all the records that have been changed in table t from 8 am to 5 pm everyday, and log them or whatever. Or would I have to write code to do that?

Tom Kyte
April 08, 2006 - 9:40 am UTC

workspace manager can do that, search this site for dbms_wm

that is but one method. streams can be used as well.

Alexander the ok, April 10, 2006 - 10:59 am UTC

Thanks Tom, the workspace manager looks like it could help a lot. One thing I don't understand though, when I used DBMS_WM.EnableVersioning as you did in your example on a different thread, it changed the name of the table I was interested in tracking?

So if I do exec DBMS_WM.EnableVersioning('T', 'VIEW_NO_OVERWRITE');

I'll be left with T_AUX and T_LT? If so that's definitely a really bad thing.....I have tons of code touching the tables I want to track, this would pretty much kill this completely from being a solution I could use.

Tom Kyte
April 11, 2006 - 10:23 am UTC

you are left with a view T that represents your original table, you may ignore the other real tables, you will be using T.


T is what you query, modify and so on.

It is a view, but that is OK. It is a view that hides the extra stuff you need to add for versioning.

Alexander the ok, April 11, 2006 - 11:22 am UTC

Upon further searching through the docs I did find the explanation as you stated. I still have some concerns about instead of triggers replacing my table. I'm about as fond of triggers as you are (not at all) so when I picture many a user performing considerable dml against these tables (that would now be views....) I see limited scalability, mutating table errors, more parsing etc.

Would you discourage using for any reasons or do you think it's safe regardless of the table size and dml operations performed against it? Thanks.

Tom Kyte
April 11, 2006 - 5:56 pm UTC

you would either always see mutating table constraints or not.

You know what I always say:

o benchmark it

does this add to the amount of resources needed? Yes.
does it add to much? We don't know until you try using your conditions.

OS audits to one file...

Craig, October 03, 2006 - 3:54 pm UTC

Tom,

I read the following in the 9i Concepts document, "Oracle allows audit trail records to be directed to an operating system audit trail if the operating system makes such an audit trail available to Oracle." How would one do this in Unix? Would it be possible to direct the OS audit trail to one file rather then one file per session? I know 10g is more flexible with audit information, but we won't be there for a while. Thanks in advance!

Regards,
Craig

Tom Kyte
October 03, 2006 - 4:53 pm UTC

no, it'll audit to individual files.

Tracking CLIENT os user name for ddl events

Pulakesh Dey., January 08, 2007 - 1:17 am UTC

Hi tom,

I have gone through the all the given solution Audit trail. Actually I am facing a serious problem in our present system. It has different versions of oracle here all are above 9i. I have tried to make a ddl event trigger to track all the events like drop, alter, create and all others ddl events we have but I was unable to get the CLIENT MACHINE OS USERID here for that ddl trigger. Our system is a online system so, I can' shut down it for a moment. I need a way to make something which will give me a way to track the object_name, object_type, ddl_event, and client_os_user_id, date,time. Please send me the answer as soon as possible . We have way to track the user_name of the oracle, but here multiple person use the same oracle user_id. I am facing a very big problem for this please help me.
Tom Kyte
January 08, 2007 - 12:23 pm UTC

why couldn't you get it? you don't even show us how you tried.

DDL event trigger

Pulakesh Dey, January 10, 2007 - 6:26 am UTC

I am sorry, I thought that you don't require that. I was expecting some way to do that irrespective what I have done. Any way I am giving the code bellow what I have used.

CREATE OR REPLACE TRIGGER DDLTrigger
AFTER DDL ON DATABASE
BEGIN
IF ora_login_user <> 'SYS' THEN
INSERT INTO DDL_LOGGER
(UserName, DDLDate, DDLObjType, DDLObjOwner, DDLObjName, DDLEvent,client_ip) VALUES
(ora_login_user, sysdate, ora_dict_obj_type,
ora_dict_obj_owner, ora_dict_obj_name, ora_sysevent,ora_client_ip_address);
END IF;
END;

this is not giving me the client_ip and I can't shutdown the database anyway so, I can make any changes in configuration files.Is there any way to get the client_ip or OS user ID

how to find which user drop a table

chitta, April 12, 2007 - 10:52 am UTC

In my oracle 9i database some one drop the table and recently recreated it. now i want to know who did this things? is it possible to find out?. i got metalink Document 197598.1 but it tell the steps for before drop the table.but the Table was droped.do i need to Audit the table?
could you tell me the steps.
thanks in Advance...
Tom Kyte
April 13, 2007 - 12:06 pm UTC

you MIGHT be able to mine this out of the redo logs (see admin guide, using log miner).

However, in order to have a record of who did what and when - you would need to have enabled auditing proactively, before the fact, yes.

Getting DIANA dump

Pulakesh Dey, April 19, 2007 - 8:27 am UTC

in 10g I have tried to get the DIANA tree using the following code:


exec dumpdiana.dump(aname => 'pk_validate_date');

but I have faced the following error. what are possible reasons cau you suggest me?
-------------------------------------------------
declare
*
ERROR at line 1:
ORA-06544: PL/SQL: internal error, arguments: [ptgxtn: incorrect session LUT
usage], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [ptgxtn: incorrect session LUT usage]
ORA-06512: at "SYS.DUMPDIANA", line 5
ORA-06512: at "SYS.DUMPDIANA", line 20
ORA-06512: at "SYS.DUMPDIANA", line 37
ORA-06512: at "SYS.DUMPDIANA", line 87
ORA-06512: at line 4

Thanks again for the previous reply you have given to me.
Pulakesh Dey.

Tom Kyte
April 19, 2007 - 9:48 am UTC

ora-600 => support

however, as this is an undocumented internal package not designed for our use, it is unlikely to get much attention.

DIANADUMP

Pulakesh Dey, April 20, 2007 - 7:04 am UTC

Sorry, tom I can't understand the comment you have given to me.

ora-600 => support
Tom Kyte
April 20, 2007 - 7:19 am UTC

ora-600 gets/implies support
ora-600 =>           support



just like you would read:

exec dbms_output.put_line( a => 'hello world' );

as

"a gets 'hello world"


Process Status

Pulakesh Dey, May 21, 2007 - 4:24 am UTC

Hi Tom,

I what to know how I can see from oracle itself about the status of SMON, PMON, DBWR, LGWR processes. Is there any triger event which can tell me the change of the status of these processes?

Thanks,
Pulakesh
Tom Kyte
May 21, 2007 - 10:26 am UTC

that doesn't make sense.

what do you mean by "status", they are either

a) alive and well
b) not


Reasons for process stop

Pulakesh Dey, May 23, 2007 - 5:57 am UTC

Hi,

I have gone through your feed back. Is there any way to know that the process may stop. What are the possible reasons behind this?
Tom Kyte
May 23, 2007 - 7:08 am UTC

if they (smon, pmon, dbwr, lgwr) 'stop', something really bad and unexpected has happened.

Since I cannot forecast every unexpected thing - I cannot give you possible reasons.

It would be a failure of some sort, one that is not supposed to happen - eg: there are no stock list of reasons, it isn't supposed to happen and if it does, it would be a problem to be worked with support.

Process

Pulakesh Dey, July 17, 2007 - 6:16 am UTC

Hi Tom,
I know the situation and also understood what you told to me, but like other server related erors is there any error oracle use to through if processes like smon, pmon, dbwr, lgwr stopped by any reason?

Thanks,
Pulakesh.
Tom Kyte
July 17, 2007 - 11:45 am UTC

you would normally see a message in the alert log and a trace file in the background dump destination

Database Audit

A reader, July 19, 2007 - 11:37 am UTC

Hello Tom,

I have read whole thread and it is very useful. Here in my company we are using triggers for database auditing not the standard DB auditing(audit_trail=true).
Following are the activities which we are monitoring:
¿ Starting the database.
¿ Stopping the database.
¿ Logins to the database.
¿ Logoffs from the database.
¿ Failed logins to the database.
¿ Altering, Creating and Dropping activity on
database.

Questions:

1. Can it posssible to monitor all these activities using standard DB auditing means by enabling audit_trail=TRUE.
If yes, then which method is better using triggers or standard DB auditing ?


2. How to monitor DBA activities in such way so that DBA does not have any control on audited information? . Becuase right now all the triggers are inserting data into a table called audit_details which can be manipulated by DBA.

Thanks in Advance





Tom Kyte
July 19, 2007 - 12:24 pm UTC

1) yes.

my stock answer is always "if you need not write code, don't"

2) http://docs.oracle.com/docs/cd/B19306_01/network.102/b14266/auditing.htm#sthref1299

you can audit DBA and SYSDBA activities, and you can do that to a place the DBA cannot touch.

database auditing

A reader, July 19, 2007 - 2:56 pm UTC

Hello Tom,

Thank you very much for your quick response.

I got the idea that using syslog audit trail we can audit in a way so that DBA can not touch.But the link which you sent me does not tell how to configure syslog to log information in a file and where exactly it writes the log.

one more thing i am using oracle 8.1.6 so it does not have that parameter audit_sys_operation. is it a problem if i want to configure syslog?

Thanks

Tom Kyte
July 19, 2007 - 2:59 pm UTC

well, I linked to otn.oracle.com

that would be where ALL documentation is, you got the pointer, just keep reading and reading there.


and so sorry - 8.1.6, that is like being in the stone age - yes most features we have come to rely on in 2007 were not invented back then - sorry, but it just wasn't an option way back when.

database auditing

A reader, July 19, 2007 - 3:12 pm UTC

Hey Tom,

Thanks a million for such useful info...

Yes i know that 8.1.6 is very-very old...so you mean there is no way i can audit database like syslog in oracle8i..

I am forcing my company management to upgrade it to 10g.

Thanks Again

Insertion problem

Pulakesh Dey, July 31, 2007 - 5:27 am UTC

Hi Tom,
I have faced a Peculiar problem in single statement insertion statement. I am giving you the statement I have used. This table is having around 50 million record. After executing bellow given statement, I killed that session after 1 hour because it was taking long time. Next time again I tried to execute the same statement but it gave me error for primary key violation. That table was empty before I started the operation for first time. I have tried it by removing the constraint of primary key but in this case it inserted redundant record.

The statement is:

insert into AAA select * from BBB;

Why it is happening because first time I tried to insert but it failed that means that table should remain empty.

Please help me out. It is very urgent for me.

Thanks,
Pulakesh dey.
Tom Kyte
August 02, 2007 - 9:16 am UTC

we are missing some bit of information - for the simple reason that it does not work the way you describe.

we'd need the full example.

Insertion anomalies

Pulakesh Dey., August 21, 2007 - 3:10 am UTC

Hi Tom,
I understood your concern. I am again trying to explain the actual problem and the sequence of execution.
I have faced a Peculiar problem in single statement insertion statement. I am giving you the statement I have used. This table is having around 50 million records. After executing bellow given statement, I killed that session after 1 hour because it was taking long time. Next time again I tried to execute the same statement but it gave me error for primary key violation. That table was empty before I started the operation for first time. I have tried it by removing the constraint of primary key but in this case it inserted redundant record.

Sequence of execution
1. truncate table AAA
2. Insert into AAA select * from BBB
3. After 1 hour killed the session
4. Insert into AAA select * from BBB
5. Got error for primary key violation
6. Remove the constraint
7. Insert into AAA select * from BBB
8. Now this time it inserted redundant data.

Here BBB is having 50 millions of data.
Both of the tables are having 545 columns.

Due to professional restriction I can not give you the actual insertion statement or table name and column name.

Thanks,
Pulakesh Dey.

Tom Kyte
August 22, 2007 - 11:23 am UTC

I'll say it again

it does not work that way.


please use support, they'll be able to get stuff out of you I cannot right here - like what is *really* happening.

Auditing !!

A reader, August 22, 2007 - 2:09 am UTC


Hi Tom,
I have one question for you regarding Auditing.
Database Version: 9.2. Production Database.

The user, say X, is not being audited. The user does not feature in any of the *OPTS views. Also none of its objects are being audited as I confirmed it from DBA_OBJ_AUDIT_OPTS.

But still the user is being audited and all its actions as well. The information is available from DBA_AUDIT_TRAIL.

My question: How??




Tom Kyte
August 22, 2007 - 1:33 pm UTC

audit connect;

that'll audit everyone. for example. You do not have to audit individuals, you can audit globally.

Thanks..

A reader, August 22, 2007 - 10:03 pm UTC


Thanks for the prompt reply. But is there a view from where I can confirm the same.
Thanks as always.

Tom Kyte
August 23, 2007 - 11:13 am UTC

sys%ORA10GR2> select * from dba_priv_audit_opts;

USER_NAME                      PROXY_NAME
------------------------------ ------------------------------
PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------

CREATE SESSION                           BY ACCESS  BY ACCESS


AUDIT SYSTEM                             BY ACCESS  BY ACCESS


ALTER SYSTEM                             BY ACCESS  BY ACCESS


sys%ORA10GR2> noaudit connect;

Noaudit succeeded.

<b>audit on create session usage is gone:</b>

sys%ORA10GR2> select * from dba_priv_audit_opts;

USER_NAME                      PROXY_NAME
------------------------------ ------------------------------
PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------

AUDIT SYSTEM                             BY ACCESS  BY ACCESS


ALTER SYSTEM                             BY ACCESS  BY ACCESS


sys%ORA10GR2> audit connect;

Audit succeeded.

<b>and back:</b>

sys%ORA10GR2> select * from dba_priv_audit_opts;

USER_NAME                      PROXY_NAME
------------------------------ ------------------------------
PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------

CREATE SESSION                           BY ACCESS  BY ACCESS


AUDIT SYSTEM                             BY ACCESS  BY ACCESS


ALTER SYSTEM                             BY ACCESS  BY ACCESS

Audit Connect

A reader, August 27, 2007 - 7:22 am UTC


Hi Tom,
Sorry for bothering you again and again with this.
But Create Session gathers audit data only about Logon and Logoff.
But all the actions for the user XXX is being audited e.g. Logon, Logoff, Select, Create, Alter etc. How is that possible?

Also, I am a bit confused with:

DBA_PRIV_AUDIT_OPTS - describes current system privileges being audited across
the system and by user.
DBA_STMT_AUDIT_OPTS - describes current system auditing options across the system and by user.

DBA_PRIV_AUDIT_OPTS audits the systenm privileges as they are executed in the database.
But what auditing information does DBA_STMT_AUDIT_OPTS define?

Thanks as always..


Tom Kyte
September 04, 2007 - 11:48 am UTC

you do not have to audit by username, you can just say "hey, database - AUDIT SELECTS on this thing"

which you must have done.

Trace COMMIT statements

Althaf, October 07, 2007 - 5:44 am UTC

Tom,

I have read some document pertains to auditing but never have a chance to see the details of ¿how to trace commit statement¿. Would it be possible trace commit statement, if yes then which would be the ideal solution.. ie Audit_trail OS or DB..

Thanks Much!

Althaf

Confusion about DBA_AUDIT_EXISTS

MEF, November 29, 2007 - 5:04 am UTC

I'm confused.  I do

AUDIT NOT EXISTS;

Then, I log in as HR and do:

SQL> SELECT * FROM OE.ORDERS;
SELECT * FROM OE.ORDERS
                 *
ERROR at line 1:
ORA-00942: table or view does not exist

Then as SYSTEM:

SQL> select username, obj_name from dba_audit_exists;

no rows selected

Next as HR:

SQL> SELECT * FROM NOT_A_REAL_TABLE;
SELECT * FROM NOT_A_REAL_TABLE
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Then as SYSTEM:

SQL> select username, obj_name from dba_audit_exists;

USERNAME
------------------------------
OBJ_NAME
--------------------------------------------------------------------------------
HR
NOT_A_REAL_TABLE


I get the same ORA-00942 for both statements but only get an audit record for the second one where the object does not exist in any schema.  Is this the correct behavior? Why does Oracle give the same error message for both cases instead of insufficient privileges in the first case?

Alexander, February 21, 2008 - 3:24 pm UTC

Isn't this incorrect:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams016.htm#sthref93

"You can use the SQL statement AUDIT to set auditing options regardless of the setting of this parameter."
Tom Kyte
February 21, 2008 - 5:06 pm UTC

not technically. It is a tricky statement those.

You can use the AUDIT command to set the auditing options.

It is just that if you happen to have set audit_trail to none, those options are not used.


I think they are trying to say "the audit command will work without error regardless of the setting of this parameter.. but this parameter controls whether the auditing you have set is performed or not and where it will be recorded to"

so, technically not wrong, but I can see how it would be confusing

auditing

A reader, March 10, 2008 - 7:09 pm UTC

TOm:

Is there a way to monitor any new objects created for a user schema. Like new tables, triggers, etc.

If anyone creates an object, I want to be alerted about it.
Tom Kyte
March 10, 2008 - 9:21 pm UTC

well, most of the rest of the world uses....

source code control

you could create a system event trigger that fired upon CREATE statements in that schema and did something (like called dbms_job to send an email) when it did.

Auditing of mult. queries within a single session ID...

J from Vancouver, March 20, 2008 - 1:13 pm UTC

Hello Tom.

The business need to to log all queries (and sql statements) against a particular view. So I'm in my little XE database trying to get a test case going.  The set up:

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\ORACLEXE\APP\ORACLE\ADMIN\X
                                                 E\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      DB, EXTENDED

SQL> select * from dba_obj_audit_opts;

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -----------------
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
JHSMYTH                        CTSA_PI_FED_V                  VIEW
-/- -/- -/- -/- -/- -/- -/- -/- -/- S/S -/- -/- -/- -/- -/- -/- -/-

[Okay, so auditing is on and is set for select statements against CTSA_PI_FED_V. Great!]

SQL> select count(*) from ctsa_pi_fed_v;

  COUNT(*)
----------
        75

SQL> select sql_text
  2  from dba_audit_trail
  3  where obj_name = 'CTSA_PI_FED_V';

SQL_TEXT
--------------------------------------------------------------------------------
select count(*) from ctsa_pi_fed_v

[Wonderful!]

SQL> select count(*) from ctsa_pi_fed_v where organization = 'AG';

  COUNT(*)
----------
        20

SQL> select sql_text
  2  from dba_audit_trail
  3  where obj_name = 'CTSA_PI_FED_V';

SQL_TEXT
--------------------------------------------------------------------------------
select count(*) from ctsa_pi_fed_v

Huh?
Where's the second select statement against the view?

In fact, NO additional queries I make in the session against the view are logged.

If create a new session ID (like open another sql window in Toad), the FIRST access against the view is logged for that session, but never for subsequent queries.

How can I make the auditing happen for all queries against the view in a session?  I dove through the Oracle manuals, but nothing popped out at me.  Any advice would be appreciated - Many, many thanks and best regards.

Tom Kyte
March 24, 2008 - 10:29 am UTC

sounds like you did auditing for the first access only. but you didn't give us the entire example :(

typically to grab ALL SQL statements - you would use find grained auditing - DBMS_FGA - in 10g and up it can gather all sql statements and their bind variable values in the audit trail.

RE: Auditing of mult. queries within a single session ID...

J from Vancouver, March 21, 2008 - 10:45 am UTC

Hah! Got it after finding the proper document section. There's a BY ACCESS parameter that I missed when setting up the audit. Please disregard the previous post (and have a Happy Easter!)

doubt

sai pradeep, November 25, 2009 - 11:05 pm UTC

hi tom,
we are using oracle 8i(8.1.0.7) on windows os, can you send me the steps to enable the auditing in our database?
regards,
sai
Tom Kyte
November 28, 2009 - 12:37 pm UTC

laugh out loud.

Ok, here is one set of steps:

set audit_trail=true in your init.ora and restart the database.

There, you are now auditing - probably auditing nothing, but you have auditing enabled.

One would need to be a tad more specific as to what one would actually like to audit for one to get "steps" for anything.


I do suggest you read the chapter in the admin guide on auditing BEFORE you type in more information here. You will almost certainly be able to answer your question based on that.

audit_trail values in 10.2.0.4.0

Reader, January 05, 2010 - 3:00 am UTC

Hi Tom,

I have done the below 2 steps for auditing
1) audit_trail=true in spfile
2) audit session by access whenever successful

I have bounced the db.
but still auditing is not happening when the user cannot connect, but the same works on 9i.
I have checked the data from aud$ table. There is no record for that user and action# 100 that tells u failed logon.
Basically we have upgraded the database from 9i to 10g. In 9i auditing was happeneing but on 10g after upgrade it isn't, even though all the auditing options are the same.
Are the values TRUE/FALSE for audit_trail deprecated in 10g?


Tom Kyte
January 05, 2010 - 9:47 am UTC

sys%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> show parameter audit_trail;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      TRUE
ops$tkyte%ORA10GR2> audit session by access whenever not successful;

Audit succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec print_table( 'select * from DBA_AUDIT_SESSION' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> connect scott/tiger
Connected.
scott%ORA10GR2> connect scott/foobar
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
scott%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> exec print_table( 'select * from DBA_AUDIT_SESSION' );
.OS_USERNAME                  : tkyte
.USERNAME                     : SCOTT
.USERHOST                     : dellpe
.TERMINAL                     : pts/2
.TIMESTAMP                    : 05-jan-2010 10:46:28
.ACTION_NAME                  : LOGON
.LOGOFF_TIME                  :
.LOGOFF_LREAD                 :
.LOGOFF_PREAD                 :
.LOGOFF_LWRITE                :
.LOGOFF_DLOCK                 :
.SESSIONID                    : 1350443
.RETURNCODE                   : 1017
.CLIENT_ID                    :
.SESSION_CPU                  :
.EXTENDED_TIMESTAMP           : 05-JAN-10 10.46.28.122165 AM -05:00
.PROXY_SESSIONID              :
.GLOBAL_UID                   :
.INSTANCE_NUMBER              : 0
.OS_PROCESS                   : 6748
-----------------

PL/SQL procedure successfully completed.



works for me, show me your work.....

audit_trail values in 10.2.0.4.0

Reader, January 05, 2010 - 3:11 am UTC

Sorry Tom,

I meant whenever NOT successful

audit_trail values in 10.2.0.4.0

A reader, January 05, 2010 - 12:53 pm UTC

Hi Tom,

From the oracle reference manual its says

10g Release1
AUDIT_TRAIL = { db | os | none | true | false | db_extended }

10g Release2
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
Release2 there is no TRUE value


The scenario that I explained to you happens at my work place(Development Environment 10.2.0.4.0)

At my personal PC I installed 10g 10.2.0.1.0 and did the same steps and it worked with TRUE value ....so right now Im really clueless as to what the actual reason is?

Shall post you my steps too once I get back to work. Untill then any insight?

Tom Kyte
January 06, 2010 - 6:51 am UTC

show
your
work.

audit_trail values in 10.2.0.4.0

A reader, January 06, 2010 - 2:10 am UTC

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      TRUE


SQL> select userid,timestamp#,action# from system.aud$ where trunc(timestamp#) = trunc(systimestamp)
;

no rows selected


SQL> select * from dba_stmt_audit_opts;

USER_NAME                      PROXY_NAME
------------------------------ ------------------------------
AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------

CREATE SESSION                           NOT SET    BY ACCESS


SQL> select * from dba_priv_audit_opts;

USER_NAME                      PROXY_NAME
------------------------------ ------------------------------
PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------

CREATE SESSION                           NOT SET    BY ACCESS


SQL> select * from dba_obj_audit_opts;

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -----------------
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
SYS                            AUD$_ORIG                      TABLE
-/- -/- -/- A/A -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-


SQL> conn ****/****@****
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> conn ****/****@****
Connected.
SQL> select userid,timestamp#,action# from system.aud$ where trunc(timestamp#) = trunc(systimestamp)
;

no rows selected.


Just by looking at these tables I find that there are no differences between 9i and 10g audit options? Please help

Tom Kyte
January 06, 2010 - 8:24 am UTC

run my example, my way.

do not use aud$, use the views provided.

with those ****, I cannot tell what systems you are connected to, especially INITIALLY

audit_trail values in 10.2.0.4.0

A reader, January 07, 2010 - 2:41 am UTC

SQL*Plus: Release 10.1.0.2.0 - Production on Thu Jan 7 13:54:05 2010

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> show parameter audit_trail;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      TRUE

SQL> SET SERVEROUTPUT ON
SQL> exec print_table( 'select * from DBA_AUDIT_SESSION where trunc(timestamp) = trunc(systimestamp)
' );

PL/SQL procedure successfully completed.

SQL> select * from dba_stmt_audit_opts;

USER_NAME                      PROXY_NAME
------------------------------ ------------------------------
AUDIT_OPTION                             SUCCESS    FAILURE
---------------------------------------- ---------- ----------

CREATE SESSION                           NOT SET    BY ACCESS


SQL> 
SQL> select * from dba_priv_audit_opts;

USER_NAME                      PROXY_NAME
------------------------------ ------------------------------
PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------

CREATE SESSION                           NOT SET    BY ACCESS


SQL> select * from dba_obj_audit_opts;

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -----------------
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
SYS                            AUD$_ORIG                      TABLE
-/- -/- -/- A/A -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-


SQL>  conn trial/trial1@****.****
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "SQLTEXT": invalid identifier
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>  conn ****/****@****.****
Connected.
SQL> SET SERVEROUTPUT ON
SQL> exec print_table( 'select * from DBA_AUDIT_SESSION where trunc(timestamp) = trunc(systimestamp)
' );

PL/SQL procedure successfully completed.

SQL> exec print_table( 'select COUNT(*) from DBA_AUDIT_SESSION where trunc(timestamp) = trunc(systim
estamp)' );
COUNT(*)                      : 0
-----------------

PL/SQL procedure successfully completed.


Sorry Tom cannot give any DB credentials but I'm surely connecting to the same database. I created a dummy user TRIAL and used that here. The last audit that happened was before upgrading to oracle 10g after that there has been no record added in the aud$ table when the user fails to login.

Tom Kyte
January 11, 2010 - 8:30 am UTC

you are not doing the simple test I asked.


please read

Followup January 5, 2010 - 9am Central time zone:


and do that.


Hey, what is UP with this:


SQL>  conn trial/trial1@****.****
ERROR:
ORA-00604: error occurred at recursive SQL level 1<b>
ORA-00904: "SQLTEXT": invalid identifier <<<=======!!!!!!!!</b>
ORA-01017: invalid username/password; logon denied


do you have trigger of your own in place?

audit_trail values in 10.2.0.4.0

A reader, January 18, 2010 - 1:27 am UTC

Sorry about the delay Tom

The error can be due to the logon trigger but not sure.

I shall reproduce the steps the same way except for the user credentials. I shall display * as earlier. Is that fine with you?
Tom Kyte
January 18, 2010 - 5:40 pm UTC

sure, but you'll find it is your trigger...

audit_trail values in 10.2.0.4.0

A reader, January 22, 2010 - 3:42 am UTC

Hi Tom.

The LOGON trigger did exist even in 9i when auditing was working and basically this trigger is on AUD$ table. Whenever there is failed logon this trigger insert that data into another table.That's all what this trigger does.

I even tried disabling the trigger and still the failed logon gives the same error i.e.
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "SQLTEXT": invalid identifier
ORA-01017: invalid username/password; logon denied

my steps belows

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> show parameter audit_trail;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      TRUE

SQL> audit session by access whenever not successful;

Audit succeeded.

SQL> set serveroutput on
SQL> exec print_table( 'select * from DBA_AUDIT_SESSION where trunc(timestamp) = trunc(systimestamp)
' );

PL/SQL procedure successfully completed.

SQL> conn */*@*
Connected.
SQL> conn */*@*
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "SQLTEXT": invalid identifier
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn */*@*
Connected.
SQL> exec print_table( 'select * from DBA_AUDIT_SESSION where trunc(timestamp) = trunc(systimestamp)
' );

PL/SQL procedure successfully completed.

Tom Kyte
January 25, 2010 - 1:43 am UTC

you are NOT allowed to have a trigger on the audit trail, get rid of it now and in all places it exists.

your fault entirely here, you do NOT put triggers on OUR stuff - never.

audit_trail values in 10.2.0.4.0

A reader, January 25, 2010 - 8:39 am UTC

Hi Tom

Its not possible to take out the trigger as this is a very vast and 10 year old project. We would have to go ahead with the same functionality. Can you please suggest?
Tom Kyte
January 29, 2010 - 8:26 am UTC

you can and will have to take it out

never was supported
is not supported
will not be supported in the future


there is no need of a trigger, write a job that filters the audit trail AFTER THE FACT (eg: every N minutes where N is as big as you can get) and do it the right way.

No other way. I will not comment further until the trigger is *gone*. You can (really you can) remove it. You might choose not to, but then look where you are?

audit_trail values in 10.2.0.4.0

A reader, February 11, 2010 - 5:54 am UTC

Hi Tom,

Even after removing trigger it does not work. The aud$ table is created in SYSTEM schema, can this be the reason?
should the AUD$ exists in SYS schema? Please correct me if Im wrong


Tom Kyte
February 16, 2010 - 9:38 am UTC

it does work if everything is correct (no trigger). That aud$ is in system is OK.

You still have something non-standard in place - what is the error you are getting somewhere else?

audit_trail values in 10.2.0.4.0

A reader, February 18, 2010 - 4:38 am UTC

Hi Tom,

I finally got the answer to the below error
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "SQLTEXT": invalid identifier
ORA-01017: invalid username/password; logon denied

The aud$ table is created in system schema and there is a aud$ view created on this table in SYS schema. In 10g the aud$ table has 11 additional columns, so after upgrade the view did not contain these extra columns that were in system.aud$ table. SQLTEXT is a column in the table which did not exist in view.
Oh goodness such simple things yet so complicated ... anyways Tom I really appreciate your time and inputs to get to this.
Even after this if it does not work I shall get back to you :-)
Tom Kyte
February 18, 2010 - 9:44 am UTC

now you know why playing around with this stuff as you have is not only completely unsupported but a REALLY BAD IDEA.

It is not that it is complicated, it is that you don't have sufficient information to do this - and never well. Those are our tables, not yours.

dba_audit_statement not getting populated

Stuart, February 18, 2010 - 4:54 pm UTC

Hi Tom,

I'm wanting to see auditing data from dba_audit_statements, but the view is empty.  

Metalink doc 158348.1 indicates it is populated by 'audit system grant', which I've tried, but the view is still empty.

The database is 10.2.0.4

For example:
TEST: SYS AS SYSDBA> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u04/oracle/product/10.2.0/db_1/rdbms/audit
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_trail                          string      DB


TEST: SYS AS SYSDBA> select * from dba_audit_statement;

no rows selected

TEST: SYS AS SYSDBA> audit system grant;

Audit succeeded.

TEST: SYS AS SYSDBA> grant dba to scott;

Grant succeeded.

TEST: SYS AS SYSDBA> alter system set undo_retention=172800 scope=both;

System altered.

TEST: SYS AS SYSDBA> revoke dba from scott;

Revoke succeeded.

TEST: SYS AS SYSDBA> revoke select on general.patient from scott;

Revoke succeeded.

TEST: SYS AS SYSDBA> select * from dba_audit_statement;

no rows selected

Am I missing something?  Maybe it is some other audit statement I'm missing?

Tom Kyte
February 18, 2010 - 7:44 pm UTC

stop doing things as SYSDBA

just stop it, try revoking it from yourself.

sys/sysdba are special, they are different, we can audit them, but you have to audit them special and to the OS (would not make sense to audit sysdba into the database, sysdba can do *ANYTHING* including destroy the audit records)

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams014.htm#REFRN10005


please just stop using sys, system, as sysdba. 99.9999999999999% of the time you THINK you need sysdba, you really only need sysoper (to shutdown/startup).

Stop using them - they are magic, they work differently, things happen differently for them than any other user.

Use your own accounts granted with a minimal set of privileges.

SYSDBA

Stuart, February 18, 2010 - 8:41 pm UTC

Thank you.

Point taken about SYSDBA usage. You are totally right.

Thanks also for highlighting the problem. It works fine when I'm not SYSDBA.

Looks like I've got some homework for the weekend.

v$session vs dba_audit_session

mfz, September 28, 2010 - 10:50 am UTC

The user 'TESTUSER' is the user accessed from web application with connection pooling . 
The connection pooling was set to 20 connections ( Tomcat is the app/ web Server) 


However , When I queried v$session , I am seeing 27 records . 

Also , when I look at dba_audit_session , there are hundreads of 'LOGON' action names with this user. 

When I assigned a profile with maximum of 20 sessions , I was getting returncode of 2391 ( SESSIONS_PER_USER ) . 

Questions :

1) Why would dba_audit_session shows hundreads of "LOGON" actions ?
2) If the pool is set to 20 , why would v$session shows over 20 ?




SQL> SElect username , status ,  count(*)  from v$session
  2   where username ='TESTUSER'
  3  GROUP BY username, status
  4  ;

USERNAME   STATUS     COUNT(*)
---------- -------- ----------
TESTUSER INACTIVE         27




SQL> Select * from
  2  (
  3  select  os_username,
  4  username,
  5  userhost,
  6  to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') timestamp,
  7  returncode
  8  from dba_audit_session
  9  where action_name = 'LOGON'
 10  and username like 'TEST%'
 11  and to_char(timestamp,'MM-DD-YYYY') = '09-28-2010'
 12  order by timestamp desc
 13  )
 14  Where rownum < 100;

OS_USERNAM USERNAME   USERHOST   TIMESTAMP           RETURNCODE
---------- ---------- ---------- ------------------- ----------
root       TESTUSER jdbcclient 09/28/2010 11:31:13          0
root       TESTUSER jdbcclient 09/28/2010 11:31:07          0
root       TESTUSER jdbcclient 09/28/2010 11:31:01          0
root       TESTUSER jdbcclient 09/28/2010 11:30:47          0
root       TESTUSER jdbcclient 09/28/2010 11:30:36          0
root       TESTUSER jdbcclient 09/28/2010 11:30:35          0
root       TESTUSER jdbcclient 09/28/2010 11:30:32          0
root       TESTUSER jdbcclient 09/28/2010 11:30:25          0
root       TESTUSER jdbcclient 09/28/2010 11:30:25          0
root       TESTUSER jdbcclient 09/28/2010 11:30:14          0
root       TESTUSER jdbcclient 09/28/2010 11:29:47          0
root       TESTUSER jdbcclient 09/28/2010 11:29:42          0
root       TESTUSER jdbcclient 09/28/2010 11:29:40          0
root       TESTUSER jdbcclient 09/28/2010 11:29:39          0
root       TESTUSER jdbcclient 09/28/2010 11:29:28          0
root       TESTUSER jdbcclient 09/28/2010 11:29:24          0
root       TESTUSER jdbcclient 09/28/2010 11:29:20          0
root       TESTUSER jdbcclient 09/28/2010 11:29:06          0
root       TESTUSER jdbcclient 09/28/2010 11:29:01          0
root       TESTUSER jdbcclient 09/28/2010 11:28:51          0
root       TESTUSER jdbcclient 09/28/2010 11:28:37          0
root       TESTUSER jdbcclient 09/28/2010 11:28:32          0
root       TESTUSER jdbcclient 09/28/2010 11:28:30          0
root       TESTUSER jdbcclient 09/28/2010 11:28:13          0
root       TESTUSER jdbcclient 09/28/2010 11:28:13          0
root       TESTUSER jdbcclient 09/28/2010 11:28:12          0
root       TESTUSER jdbcclient 09/28/2010 11:28:05          0
root       TESTUSER jdbcclient 09/28/2010 11:27:58          0
root       TESTUSER jdbcclient 09/28/2010 11:27:56          0
root       TESTUSER jdbcclient 09/28/2010 11:27:52          0
root       TESTUSER jdbcclient 09/28/2010 11:27:33          0
root       TESTUSER jdbcclient 09/28/2010 11:27:18          0
root       TESTUSER jdbcclient 09/28/2010 11:27:08          0
root       TESTUSER jdbcclient 09/28/2010 11:26:49          0
root       TESTUSER jdbcclient 09/28/2010 11:26:47          0
root       TESTUSER jdbcclient 09/28/2010 11:26:46          0
root       TESTUSER jdbcclient 09/28/2010 11:26:40          0
root       TESTUSER jdbcclient 09/28/2010 11:26:40          0
root       TESTUSER jdbcclient 09/28/2010 11:26:33          0
root       TESTUSER jdbcclient 09/28/2010 11:26:30          0
root       TESTUSER jdbcclient 09/28/2010 11:26:30          0
root       TESTUSER jdbcclient 09/28/2010 11:26:29          0
root       TESTUSER jdbcclient 09/28/2010 11:26:23          0
root       TESTUSER jdbcclient 09/28/2010 11:26:18          0
root       TESTUSER jdbcclient 09/28/2010 11:26:16          0
root       TESTUSER jdbcclient 09/28/2010 11:26:09          0
root       TESTUSER jdbcclient 09/28/2010 11:25:59          0
root       TESTUSER jdbcclient 09/28/2010 11:25:55          0
root       TESTUSER jdbcclient 09/28/2010 11:25:53          0
root       TESTUSER jdbcclient 09/28/2010 11:25:26          0
root       TESTUSER jdbcclient 09/28/2010 11:25:22          0
root       TESTUSER jdbcclient 09/28/2010 11:25:02          0
root       TESTUSER jdbcclient 09/28/2010 11:24:45          0
root       TESTUSER jdbcclient 09/28/2010 11:24:44          0
root       TESTUSER jdbcclient 09/28/2010 11:24:42          0
root       TESTUSER jdbcclient 09/28/2010 11:24:09          0
root       TESTUSER jdbcclient 09/28/2010 11:23:59          0
root       TESTUSER jdbcclient 09/28/2010 11:23:58          0
root       TESTUSER jdbcclient 09/28/2010 11:23:37          0
root       TESTUSER jdbcclient 09/28/2010 11:23:33          0
root       TESTUSER jdbcclient 09/28/2010 11:23:25          0
root       TESTUSER jdbcclient 09/28/2010 11:23:19          0
root       TESTUSER jdbcclient 09/28/2010 11:23:18          0
root       TESTUSER jdbcclient 09/28/2010 11:23:16          0
root       TESTUSER jdbcclient 09/28/2010 11:23:03          0
root       TESTUSER jdbcclient 09/28/2010 11:22:53          0
root       TESTUSER jdbcclient 09/28/2010 11:22:22          0
root       TESTUSER jdbcclient 09/28/2010 11:22:21          0
root       TESTUSER jdbcclient 09/28/2010 11:21:59          0
root       TESTUSER jdbcclient 09/28/2010 11:21:42          0
root       TESTUSER jdbcclient 09/28/2010 11:21:36          0
root       TESTUSER jdbcclient 09/28/2010 11:21:25          0
root       TESTUSER jdbcclient 09/28/2010 11:21:20          0
root       TESTUSER jdbcclient 09/28/2010 11:21:15          0
root       TESTUSER jdbcclient 09/28/2010 11:21:14          0
root       TESTUSER jdbcclient 09/28/2010 11:21:10          0
root       TESTUSER jdbcclient 09/28/2010 11:21:00          0
root       TESTUSER jdbcclient 09/28/2010 11:20:59          0
root       TESTUSER jdbcclient 09/28/2010 11:20:46          0
root       TESTUSER jdbcclient 09/28/2010 11:20:38          0
root       TESTUSER jdbcclient 09/28/2010 11:20:14          0
root       TESTUSER jdbcclient 09/28/2010 11:20:12          0
root       TESTUSER jdbcclient 09/28/2010 11:20:05          0
root       TESTUSER jdbcclient 09/28/2010 11:19:48          0
root       TESTUSER jdbcclient 09/28/2010 11:19:35          0
root       TESTUSER jdbcclient 09/28/2010 11:19:27          0
root       TESTUSER jdbcclient 09/28/2010 11:19:23          0
root       TESTUSER jdbcclient 09/28/2010 11:19:21          0
root       TESTUSER jdbcclient 09/28/2010 11:19:04          0
root       TESTUSER jdbcclient 09/28/2010 11:18:59          0
root       TESTUSER jdbcclient 09/28/2010 11:18:51          0
root       TESTUSER jdbcclient 09/28/2010 11:18:49          0
root       TESTUSER jdbcclient 09/28/2010 11:18:47          0
root       TESTUSER jdbcclient 09/28/2010 11:18:33          0
root       TESTUSER jdbcclient 09/28/2010 11:18:24          0
root       TESTUSER jdbcclient 09/28/2010 11:17:55          0
root       TESTUSER jdbcclient 09/28/2010 11:17:48          0

OS_USERNAM USERNAME   USERHOST   TIMESTAMP           RETURNCODE
---------- ---------- ---------- ------------------- ----------
root       TESTUSER jdbcclient 09/28/2010 11:17:48          0
root       TESTUSER jdbcclient 09/28/2010 11:17:27          0






SQL> Select * from product_component_version;

PRODUCT                                       VERSION         STATUS
--------------------------------------------- --------------- ---------------
NLSRTL                                        11.2.0.1.0      Production
Oracle Database 11g Enterprise Edition        11.2.0.1.0      64bit Productio
                                                              n

PL/SQL                                        11.2.0.1.0      Production
TNS for 64-bit Windows:                       11.2.0.1.0      Production

Tom Kyte
September 28, 2010 - 11:20 am UTC

...
1) Why would dba_audit_session shows hundreads of "LOGON" actions ?
2) If the pool is set to 20 , why would v$session shows over 20 ?...


(1) because connection pools frequently "cycle" connections. By design most of them use a connection for some number of calls and then close it and open a new one. An attempt to avoid issues with any memory/resource leaks the application might have.

(2) because the number 20 is probably the LOWER limit of the connection pool - you have configured (probably) AT LEAST 20 connections to always be available.

And the upper limit would be much higher.

Check out your connection pool documentation.

Auditing

A reader, March 23, 2011 - 11:30 am UTC

Hi Tom,

There ia an audit requirement which is as follows:
Audit trails should be captured so that any change made to the database can be audited...

I know the above sentence is very broad and open to interpretation but the idea is any changes manually made to the database should be tracked for any change such as records being modified : update, delete, insert etc.

What would you suggest to use ? Workspace management etc.

Many thanks

Tom Kyte
March 23, 2011 - 1:19 pm UTC

wouldn't it satisfy the requirement just to capture the SQL ?

Otherwise, flashback data archive - but to enable that for an entire database would be probably "not reasonable" - as would any solution over time, think about the storage.

Auditing

a Reader, March 24, 2011 - 3:44 am UTC

Hi Tom,

Thanks for your prompt reply.
What exactly will be the solution in order to capture the sql?


Thanks & Regards

DBMS_FGA

A reader, March 29, 2011 - 6:10 am UTC

Hi Tom,

Could you advise me if Fine-Grained Audting is suitable for dataware housing environment to capture mainly the following actions:
INSERT
UPDATE
DELETE

for all users

Many thanks
Tom Kyte
March 29, 2011 - 6:17 am UTC

if you need to capture just the fact that they did an insert/update/delete - AUDIT is the right command.

If you need to capture the actual SQL statement issued - then dbms_fga is the right approach.


But - a data warehouse where end users do insert/update/delete? That isn't normal :)


Fine-grained auditing

A reader, March 29, 2011 - 6:29 am UTC

Thanks Tom,

The purpose is to audit any changes whether by end users (which is unlikely) or by any IT staff through a normal change. I understand your recommendation.

Have a nice day.

audit

sam, March 29, 2011 - 8:16 am UTC

Tom:

<<If you need to capture the actual SQL statement issued - then dbms_fga is the right approach. >>


I think if you set AUDIT_TRAIL= EXTENDED the DB will catpure all sql statements an binds.

If you want selected SQl statemends then you can use FGA

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams016.htm


http://www.oracle-base.com/articles/10g/Auditing_10gR2.php

Truncate statement not registered

Antonio, July 14, 2011 - 10:53 am UTC

Hello Tom,
on my database the TRUNCATE table statements are not registered in the AUD$ and other DBA_AUDIT_* tables.
I have the DB_EXTENDED audit_trail option activated.
I've tried to set the auditing in many ways like AUDIT truncate in general, audit truncate by user, audit all on the table and other, but in the DBA_AUDIT_* tables nothing was registered.

Thanks for your support.
Tom Kyte
July 18, 2011 - 8:37 am UTC

audit TABLE captures - create/drop and truncate of a table.

ops$tkyte%ORA11GR2> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB
ops$tkyte%ORA11GR2> connect / as sysdba
Connected.

sys%ORA11GR2> select * from aud$;

no rows selected


sys%ORA11GR2> audit table;

Audit succeeded.

sys%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> truncate table t;

Table truncated.

ops$tkyte%ORA11GR2> exec print_table( 'select * from dba_audit_trail' );
.OS_USERNAME                  : tkyte
.USERNAME                     : OPS$TKYTE
.USERHOST                     : localhost.localdomain
.TERMINAL                     : pts/2
.TIMESTAMP                    : 18-jul-2011 09:18:09
.OWNER                        : OPS$TKYTE
.OBJ_NAME                     : T
.ACTION                       : 85
.ACTION_NAME                  : TRUNCATE TABLE
.NEW_OWNER                    :
.NEW_NAME                     :
.OBJ_PRIVILEGE                :
.SYS_PRIVILEGE                :
.ADMIN_OPTION                 :
.GRANTEE                      :
.AUDIT_OPTION                 :
.SES_ACTIONS                  :
.LOGOFF_TIME                  :
.LOGOFF_LREAD                 :
.LOGOFF_PREAD                 :
.LOGOFF_LWRITE                :
.LOGOFF_DLOCK                 :
.COMMENT_TEXT                 :
.SESSIONID                    : 17174950
.ENTRYID                      : 1
.STATEMENTID                  : 14
.RETURNCODE                   : 0
.PRIV_USED                    :
.CLIENT_ID                    :
.ECONTEXT_ID                  :
.SESSION_CPU                  :
.EXTENDED_TIMESTAMP           : 18-JUL-11 09.18.09.941306 AM -04:00
.PROXY_SESSIONID              :
.GLOBAL_UID                   :
.INSTANCE_NUMBER              : 0
.OS_PROCESS                   : 7312
.TRANSACTIONID                : 0000000000000000
.SCN                          : 16463217
.SQL_BIND                     :
.SQL_TEXT                     :
.OBJ_EDITION_NAME             :
.DBID                         : 52489347
-----------------

PL/SQL procedure successfully completed.

Antonio, July 18, 2011 - 10:53 am UTC

It works, thank you very much

Auditing on Application Tables

Rajeshwaran, Jeyabal, August 16, 2011 - 10:35 pm UTC

Tom:

We have an table named PO where records will be inserted and updated by application, changes to this table should be recorded into PO_AUDIT table which will be utilized in the month end for some reporting purpose.

I am planning to make code like below, do you still suggest to use AUDIT command? Can this be used to record changes into PO_AUDIT table?

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4007.htm#i2059073

procedure prc_po_audit(l_po_rec po%rowtype)
as
begin
 insert into  po_audit ... values(....);
end;
/
procedure po_txn(p_po_id in number,p_sts out number)
as
 l_po_rec po%rowtype;
begin
 <...some business logic goes here..>
 
 if <condition> then
  insert into po....values(....) returning ...into l_po_rec;
  prc_po_audit(l_po_rec);
 else
  update po set
   ....
  where po_id = p_po_id
  returning ...into l_po_rec;
  prc_po_audit(l_po_rec);
 end if;
end;
/

Tom Kyte
August 17, 2011 - 4:08 am UTC

the audit command only audits the use of privileges - it cannot, will not record the bits and bytes that are modified.

I don't know why you would record inserts in your history, they are readily available in the real table. typically one would records only the before images of updates and deletes - having the inserts in there is just redundant, not necessary, wasteful.

Your approach would not capture deletes.
Your approach would capture the after image of the update when you want the before image.


I would suggest:
http://www.oracle.com/us/products/database/options/total-recall/index.html

if you are in 11g - flashback data archive would do this easily without any code, without the performance impact to the end user.


before that - you should just 'audit' the before image of DELETES and UPDATES - not the inserts at all.

Auditing on Application Tables

Rajeshwaran, Jeyabal, August 17, 2011 - 10:47 am UTC

I don't know why you would record inserts in your history, they are readily available in the real table. typically one would records only the before images of updates and deletes - having the inserts in there is just redundant, not necessary, wasteful

create table t(x number,y date);
insert into t values(1,sysdate);

update t set y = to_date('01-JAN-2011') where x = 1;
update t set y = to_date('01-FEB-2011') where x = 1;
update t set y = to_date('01-MAR-2011') where x = 1;


Now my application table 'T' will have only the latest data changes.
select * from t
x   y
-- -----------
1,  01-MAR-2011


Now my audit table will have the entire data along with its changes..which gives me a complete picture.

select * from t_audit;
x   y
-- -----------
1,  17-AUG-2011
1,  01-JAN-2011
1,  01-FEB-2011
1,  01-MAR-2011


1) Do you still think "having the inserts in there is just redundant, not necessary, wasteful." ?


Your approach would not capture deletes
I mentioned in the above question that We have an table named PO where records will be inserted and updated by application. We dont delete data only inserts and updates

Your approach would capture the after image of the update when you want the before image

Yes, when ever any dml is fired in table 'T' the after image should be in 'T_AUDIT'...so at any point of time we have the complete picture in t_audit table

I will use the lag() on T_AUDIT to see what values are really changed..
Tom Kyte
August 17, 2011 - 4:36 pm UTC

if you just record the before images of updates and deletes in your audit trail, you will have EVERYTHING YOU NEED, think about this please. auditing the insert is wasteful in a large way, it gives you *nothing* you don't have just by auditing the updates and deletes.


so, say you insert into table t( x int ) the record:

insert into t values ( 1 );


In table T, you will have the 1 right now. so see the entire history of table T, you would select * from t union all select * from t_hist;

Now, you update T and set x = 2 where x = 1. In table T you will have 2, in table T_HIST - you will have saved x=1. You have the entire history.

Now you update T again and set x=3 where x=2. In t you have 3, in t_hist you have 1,2 - the entire history.

and so on, and so on and so on.

audit table with before image after image column

george joseph, August 21, 2011 - 10:08 pm UTC

In the post above, we could try for a column in the audit table T_AUDIT(old_val,new_val) (guess you would have to make old_val,new_val a varchar2 type field), that way you could avoid going to the main table T at all, to get everything you need for auditing purpose. If you have a clob field in T then perhaps you would need to investigate more into it.
Tom has already a code sample for d-i-y audting

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:59412348055

We have used this template in my current project it has been a neat and efficient way of d-i-y audit(Having calls to the package.proc instead of writing code in the triggers).

How to stop auditing of SQLs inside procedure etc

A DBA, August 31, 2011 - 7:28 am UTC

Hi Tom,

I have gone through the post and its very enlightening. However I have a query.
What is the simplest way to stop statements inside stored pl/sql to be audited.

i.e. a create a procedure which modifies a table. I don't want the SQL inside to be audited.

audit create table,alter table,insert table,update table,delete table by USER_B by access;
create table user_a.t_abc (a varchar2(1));
create or replace procedure user_a.test_noaudit as
begin
insert into T_ABC values ('Y');
commit;
end;
grant execute on user_a.t_abc to user_b;
noaudit execute procedure;


-------
user_b/user_b
exec user_a.test_noaudit ;

------
I find below audit information for the inserts performed inside test_noaudit proc.

select db_user,os_user,statement_type,sql_text from dba_common_audit_trail where DB_USER='USER_B';

DB_USER OS_USER STATEMENT_TY SQL_TEXT
------  ----- -------- ------------------------------------
USER_B  XXXXX INSERT   INSERT INTO T_ABC VALUES ('Y')


I only want to audit sqls when user_b explicitly fires them and not through stored pl/sql.

Thanks
Tom Kyte
August 31, 2011 - 1:55 pm UTC

sorry, it doesn't work that way - it is just another insert being initiated by user_b to us (run with the privileges of user_a - but run BY user_b)

raj, November 04, 2011 - 8:15 am UTC

hi

The table AUD in the oracle,iam getting 103(session rec) event multiple times(it is in lakhs).i dont want to get that event.can we stop that event.if yes,plzzzz let me know.


Tom Kyte
November 07, 2011 - 9:59 am UTC

sure, you can use the NOAUDIT command to stop auditing, you can disable the audit trail entirely (init.ora audit_trail parameter)

You are audting something BY ACCESS - you asked for this information. You can use noaudit <whatever you are auditing> and then audit by session if you just want a single record to say "session X did something", rather than a record every time session X does something.

delete sys.aud$

Florian, July 31, 2012 - 1:35 am UTC

Hi Tom;

I have a question concerning the deletion of sys.aud$.
We have a schema with all necessary role grants, so that we can query and delete sys.aud$:

08:12:35 DB_ADM@DB>select count(*) from sys.aud$;

  COUNT(*)
----------
      2823

1 row selected.

08:12:49 DB_ADM@DB>delete from sys.aud$;

2402 rows deleted.

08:12:55 DB_ADM@DB>rollback;

Rollback complete.



However if we try to delete from sys.aud$ in a procedure, it fails:

create procedure test_del is
begin
delete from sys.aud$ where sessionid = 951298;
end;
/

Warning: Procedure created with compilation errors.

17:21:53 DB_ADM@DB>sho err
Errors for PROCEDURE TEST_DEL:

LINE/COL
---------------------------------------------------------------------------------
ERROR
---------------------------------------------------------------------------------
3/1
PL/SQL: SQL Statement ignored

3/17
PL/SQL: ORA-00942: table or view does not exist

-- #########################################
-- #########################################

create procedure test_del is
begin
execute immediate 'delete from sys.aud$ where sessionid = 951298';
end;
/

Procedure created.

17:20:17 DB_ADM@DB>exec test_del
BEGIN test_del; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "DB_ADM.TEST_DEL", line 3
ORA-06512: at line 1


Problem with DBMS_AUDIT_MGMT

Florian, August 23, 2012 - 2:33 am UTC

Hi Tom;

Thanks for your help.
Since the
delete from sys.aud$
isn't working that good (since the operations against the AUD$ are audited as well and so polluting our SYSTEM tablespace), we are using the DBMS_AUDIT_MGMT package. However we have a new problem:

08:08:14 SQL> -- Bereinigung der SYS.AUD$ ueber DBMS_AUDIT_MGMT Mechanismus
08:08:14 SQL> begin
08:08:14   2    if sys.dbms_audit_mgmt.is_cleanup_initialized(sys.dbms_audit_mgmt.audit_trail_aud_std) = TRUE then
08:08:14   3      sys.dbms_audit_mgmt.deinit_cleanup(sys.dbms_audit_mgmt.audit_trail_aud_std);
08:08:14   4    end if;
08:08:14   5
08:08:14   6    -- erster Versuch, den AuditTrail Tablespace zu veraendern
08:08:14   7    begin
08:08:14   8      sys.dbms_audit_mgmt.set_audit_trail_location(sys.dbms_audit_mgmt.audit_trail_aud_std, 'SYSTEM');
08:08:14   9    exception when others then
08:08:14  10      null;
08:08:14  11    end;
08:08:14  12
08:08:14  13    sys.dbms_audit_mgmt.init_cleanup(sys.dbms_audit_mgmt.audit_trail_aud_std, 999);
08:08:14  14    sys.dbms_audit_mgmt.set_audit_trail_location(sys.dbms_audit_mgmt.audit_trail_aud_std, 'SYSTEM');
08:08:14  15    sys.dbms_audit_mgmt.set_last_archive_timestamp(sys.dbms_audit_mgmt.audit_trail_aud_std, to_timestamp('&last_archive_timestamp.', 'yyyymmddhh24missff'));
08:08:14  16
08:08:14  17    if sys.dbms_audit_mgmt.is_cleanup_initialized(sys.dbms_audit_mgmt.audit_trail_aud_std) = TRUE then
08:08:14  18      dbms_output.put_line('cleanup initialized');
08:08:14  19      sys.dbms_audit_mgmt.clean_audit_trail(sys.dbms_audit_mgmt.audit_trail_aud_std, TRUE);
08:08:14  20      sys.dbms_audit_mgmt.deinit_cleanup(sys.dbms_audit_mgmt.audit_trail_aud_std);
08:08:14  21    else
08:08:14  22      dbms_output.put_line('failed to initialize cleanup');
08:08:14  23    end if;
08:08:14  24  end;
08:08:14  25  /
old  15:   sys.dbms_audit_mgmt.set_last_archive_timestamp(sys.dbms_audit_mgmt.audit_trail_aud_std, to_timestamp('&last_archive_timestamp.', 'yyyymmddhh24missff'));
new  15:   sys.dbms_audit_mgmt.set_last_archive_timestamp(sys.dbms_audit_mgmt.audit_trail_aud_std, to_timestamp('20120823080814500514', 'yyyymmddhh24missff'));
begin
*
ERROR at line 1:
ORA-02002: error while writing to audit trail
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1087
ORA-06512: at line 13


The same error occurs as well when calling the procedure
sys.dbms_audit_mgmt.set_audit_trail_location(sys.dbms_audit_mgmt.audit_trail_aud_std, 'SYSTEM');

with a tablespace other then SYSTEM (e.g. SYSAUX)
It seems that this problem occurs only with ORACLE 11g databases. On the other hand, we have at least 2 ORACLE 11g database where this is working??? May that be a bug (i haven't found anything so far in ORACLE Metalink) or am I missing something?

thanks,
Florian