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
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
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.. :-(
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
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.
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.
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.
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) ?
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
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
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.
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
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?
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.
June 14, 2003 - 1:49 pm UTC
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?
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.
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!!
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?
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,
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.
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
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
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.
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?
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.
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
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
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?
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
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
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?
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.)
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.
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
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
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
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?
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
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
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?
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
July 13, 2005 - 12:51 pm UTC
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
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
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
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.
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?
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
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?
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
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
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.
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.
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?
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.
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?
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.
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.
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
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.
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...
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.
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
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
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?
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.
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
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
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.
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.
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??
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.
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..
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
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.
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.
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
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?
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?
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
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.
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?
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.
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?
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
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 :-)
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?
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
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
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
March 24, 2011 - 8:17 am UTC
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
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
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.
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;
/
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, wastefulcreate 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 deletesI 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 imageYes, 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..
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
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.
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
July 31, 2012 - 12:20 pm UTC
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