Home » RDBMS Server » Security » AUDIT_TRAIL (Oracle 11g (11.2.0.1.0))
AUDIT_TRAIL [message #520480] Tue, 23 August 2011 01:06 Go to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

HI All,

I have enabled Auditing in my oracle Database but I am not able to see any database for the operations of sys user or any other user in my "SYS.AUD$" and "SYS.FGA_LOG$" tables.

Value for the parameter "AUDIT_TRAIL" is set to "db,extended".

I am working as "SYS" user and I have shutdown and again startup the database but neither there was any information in both the tables nor I can see any files at the destination specified by "AUDIT_FILE_DEST".


If anyone can explain me the reason for the same.

Thanks
Deepak
Re: AUDIT_TRAIL [message #520482 is a reply to message #520480] Tue, 23 August 2011 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do NOT use SYS for your test.

- Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
- SYS/SYSDBA is special
- SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
- SYS/SYSDBA does not act like any other user
- When you use SYS/SYSDBA Oracle deactivates some code path and activates others
- Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS/SYSDBA for anything that can be done by another user.
Use SYS/SYSDBA ONLY for something that can't be done by someone else.


Quote:
If anyone can explain me the reason for the same.

You did it wrong but as you did not post what you did and got (copy and paste) we cannot say how you did it wrong.

Regards
Michel

[Updated on: Tue, 23 August 2011 01:33]

Report message to a moderator

Re: AUDIT_TRAIL [message #520487 is a reply to message #520482] Tue, 23 August 2011 02:19 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Hello Sir,

Thanks for the update now it working fine..

SQL> select * from sys.fga_log$ where NTIMESTAMP# like '23-AUG-11%';

no rows selected

SQL> audit select on deepak.test;

Audit succeeded.

SQL> audit update on deepak.test;

Audit succeeded.

SQL> audit insert on deepak.test;

Audit succeeded.




SQL> conn deepak/deepak
Connected.
SQL> insert into test values ('sharma');

1 row created.

SQL> insert into test values ('ram');

1 row created.

SQL> insert into test values ('raaj');

1 row created.

SQL> update test set name='ROHIT' where name='raaj';

1 row updated.

SQL> commit;

Commit complete.



There after connected as sys and check for the logs..

SQL> conn / as sysdba
Connected.
SQL>select SESSIONID,USERID,OBJ$CREATOR,OBJ$NAME,NTIMESTAMP#,COMMENT$TEXT,SQLTEXT from sys.aud$ where ntimestamp# like '23-AUG-11%';


 SESSIONID USERID  OBJ$CREATOR    OBJ$NAME             NTIMESTAMP#                    COMMENT$TEXT                               SQLTEXT
---------- ------- -------------- -------------------- ------------------------------ -------------------------------------------------- ----------------------------------------------
     31995 DEEPAK                                      23-AUG-11 05.50.53.361000 AM
     31995 DEEPAK                                      23-AUG-11 05.56.03.026000 AM
     33402 DEEPAK                                      23-AUG-11 07.14.38.709000 AM   Authenticated by: DATABASE
     33402 DEEPAK                                      23-AUG-11 07.16.11.441000 AM
     33409 DEEPAK                                      23-AUG-11 07.20.40.055000 AM   Authenticated by: DATABASE
     33409 DEEPAK  DEEPAK         TEST                 23-AUG-11 07.20.57.178000 AM                                              insert into test values ('sharma')
     33409 DEEPAK  DEEPAK         TEST                 23-AUG-11 07.21.04.935000 AM                                              insert into test values ('ram')
     33409 DEEPAK  DEEPAK         TEST                 23-AUG-11 07.21.09.743000 AM                                              insert into test values ('raaj')
     33409 DEEPAK  DEEPAK         TEST                 23-AUG-11 07.21.29.831000 AM                                              update test set name='ROHIT' where name='raaj'
     33409 DEEPAK                                      23-AUG-11 07.21.36.122000 AM

10 rows selected.


Here I have individually fired query to enable audit for select,insert,update for user 'deepak'. How can I audit all the ddl and dml for the users I want.

Thanks
Deepak
Re: AUDIT_TRAIL [message #520491 is a reply to message #520487] Tue, 23 August 2011 02:43 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
23-AUG-11 01.20.38.153000 PM +05:30

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
+05:30

SQL> set time on

13:22:02 SQL> conn deepak/deepak
Connected.

13:22:10 SQL> insert into test values('dsdjfsd');
1 row created.

13:22:22 SQL> insert into test values('shdshdfbks');
1 row created.

13:22:27 SQL> commit;
Commit complete.

13:22:30 SQL> conn sys/oracle as sysdba
Connected.

13:22:42 SQL> select SESSIONID,USERID,OBJ$CREATOR,OBJ$NAME,NTIMESTAMP#,COMMENT$TEXT,SQLTEXT from sys.aud$ where ntimestamp# like '23-AUG-11%';

 SESSIONID USERID  OBJ$CREATOR    OBJ$NAME             NTIMESTAMP#                    COMMENT$TEXT                               SQLTEXT
---------- ------- -------------- -------------------- ------------------------------ -------------------------------------------------- ------------------------------------------
     33448 DEEPAK  DEEPAK         TEST                 23-AUG-11 07.52.22.311000 AM                                              insert into test values('dsdjfsd')
     33448 DEEPAK  DEEPAK         TEST                 23-AUG-11 07.52.27.072000 AM                                              insert into test values('shdshdfbks')
     33448 DEEPAK                                      	  23-AUG-11 07.52.42.603000 AM

17 rows selected.

13:22:49 SQL>



Why my value for current_timestamp and time value before the SQL prompt are matching then the value of NTIMESTAMP# in sys.aud$ table?

Regards
Deepak

[Updated on: Tue, 23 August 2011 02:43]

Report message to a moderator

Re: AUDIT_TRAIL [message #520496 is a reply to message #520487] Tue, 23 August 2011 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Here I have individually fired query to enable audit for select,insert,update for user 'deepak'. How can I audit all the ddl and dml for the users I want.

By reading the documentation: AUDIT

And PLEASE, keep your lines of code and result in 80 character width.

Regards
Michel
Re: AUDIT_TRAIL [message #520497 is a reply to message #520491] Tue, 23 August 2011 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Why my value for current_timestamp and time value before the SQL prompt are matching then the value of NTIMESTAMP# in sys.aud$ table?

One is client time, the other one is server time.

Regards
Michel
Re: AUDIT_TRAIL [message #520534 is a reply to message #520497] Tue, 23 August 2011 05:33 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Quote:

One is client time, the other one is server time.


Sorry? But I think I am working on the server itself Sir.

which Time doest this NTIMESTAMP# in sys.aud$ table denotes?

Thanks
Deepak
Re: AUDIT_TRAIL [message #520535 is a reply to message #520534] Tue, 23 August 2011 05:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
which Time doest this NTIMESTAMP# in sys.aud$ table denotes?

Server time in db time zone.

select dbtimezone, sessiontimezone from dual;


Regards
Michel
Re: AUDIT_TRAIL [message #520542 is a reply to message #520535] Tue, 23 August 2011 06:15 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

SQL> select dbtimezone, sessiontimezone from dual;

DBTIME SESSIONTIMEZONE
------ ---------------------------------------------------------

+05:30 +05:30


Both are same but still there is difference in the current_timestamp and the value of NTIMESTAMP# column in sys.aud$ table.

Thanks
Deepak

[Updated on: Tue, 23 August 2011 06:16]

Report message to a moderator

Re: AUDIT_TRAIL [message #520546 is a reply to message #520542] Tue, 23 August 2011 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NTIMESTAMP# gives GMT/UTC time.
This is why I said do NOT use SYS tables, use catalog views.
If you use DBA_AUDIT_TRAIL instead you will have the same time
as the view converts to your local time.

Catalog views are documented and MUST be used.
SYS tables are NOT documented and can be used when information is not available in catalog views
AND when you know what you do.

Regards
Michel
Re: AUDIT_TRAIL [message #520556 is a reply to message #520546] Tue, 23 August 2011 07:34 Go to previous message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Thanks for the relpy Sir.

Regards
Deepak
Previous Topic: ORA-01932: ADMIN option not granted for role
Next Topic: vpd implementation
Goto Forum:
  


Current Time: Fri Mar 29 08:30:35 CDT 2024