|
|
| Re: AUDIT_TRAIL [message #520482 is a reply to message #520480] |
Tue, 23 August 2011 01:32   |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
 |
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 |
|
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 #520542 is a reply to message #520535] |
Tue, 23 August 2011 06:15 |
|
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 |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
