| AUDIT on SYS [message #563375] |
Mon, 13 August 2012 06:15  |
muktha_22
Messages: 527
Registered: December 2009
|
Senior Member |
|
|
Hi All,
I want to get the details of the SYS/SYSTEM users logon and Logoff's time.
Steps 1:
=======
SQL> SHOW PARAMETER AUDIT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN
\DB10G\ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE
SQL>
Step 2:
=======
Set the Necessary parameters:
-----------------------------
alter system set audit_trail=db scope=spfile;
Step 3:
=======
Shutdown the DB
Step 4:
=======
Restart the DB
Step 5:
=======
For auditing the Session. Fire the below command.
AUDIT CREATE SESSION;
Step 6:
=======
select DISTINCT USERNAME,os_username,timestamp,logoff_time
from DBA_audit_session;
When I fire the above query, I couldn't get the SYS user's LOGOFF time. But for rest of the users, I am getting.
Kindly help me out on this.
Regards
Muktha
|
|
|
|
| Re: AUDIT on SYS [message #563385 is a reply to message #563375] |
Mon, 13 August 2012 07:06  |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
My standard answer:
- 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.
Regards
Michel
[Updated on: Mon, 13 August 2012 07:06] Report message to a moderator |
|
|
|
| Re: AUDIT on SYS [message #563388 is a reply to message #563385] |
Mon, 13 August 2012 07:13 |
cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
While I agree with your standard answer Michel, I don't think it is necessarily an argument for not auditing sys.
Even if no-one should have access it pays to know when someone did access it.
EDIT: typo
[Updated on: Mon, 13 August 2012 07:26] Report message to a moderator |
|
|
|
|
|
|
|
| Re: AUDIT on SYS [message #563391 is a reply to message #563389] |
Mon, 13 August 2012 07:46 |
muktha_22
Messages: 527
Registered: December 2009
|
Senior Member |
|
|
Hi Everyone.
My Requirement is to know the person, who are all connected with the SYSDBA and their logon/logoff time.
Unfortunately, there is no log getting generated in the default path, as the document says.
Regards
Muktha
|
|
|
|
|
|
| Re: AUDIT on SYS [message #563461 is a reply to message #563393] |
Tue, 14 August 2012 00:25 |
muktha_22
Messages: 527
Registered: December 2009
|
Senior Member |
|
|
Hi Michel,
Thanks for the reply. But could you please guide me to do the same with the ORACLE AUDIT option.
Regards
Muktha
|
|
|
|
|
|
| Re: AUDIT on SYS [message #564459 is a reply to message #563375] |
Fri, 24 August 2012 01:21 |
muktha_22
Messages: 527
Registered: December 2009
|
Senior Member |
|
|
Hi All,
Kindly correct me if I am wrong.
I could able to find out the SYS user's LOGIN Time with the below setting.
audit_sys_operations=TRUE
audit_trail=XML
Using the below query to get the output.
SQL> ed
Wrote file afiedt.buf
1 select db_user,to_char(extended_timestamp,'DD-MON-YY--(HH12:MI AM)') "LOGON",os_user,terminal,
2 os_privilege,sql_text
3 from
4 V$XML_AUDIT_TRAIL
5 where SQL_TEXT='CONNECT'
6* order by db_user,LOGON
Below is the output:
Regards
Muktha
[Updated on: Fri, 24 August 2012 01:24] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: AUDIT on SYS [message #564461 is a reply to message #564460] |
Fri, 24 August 2012 01:53 |
muktha_22
Messages: 527
Registered: December 2009
|
Senior Member |
|
|
HI Michel,
Sorry, I am not able to attach the IMAGE output.
['img']
C:\Documents and Settings\muthu.krishnan\Desktop\misc\a.jpeg
['/img']
Kindly quide me.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
