OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » AUDIT on SYS (oracle, 10g, windows xp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: AUDIT on SYS [message #563385 is a reply to message #563375]

Mon, 13 August 2012 07:06

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

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: 13920
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 #563389 is a reply to message #563375]

Mon, 13 August 2012 07:16

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

SYS logons are audited to the oprating system audit trail, try looking at your Windows application log.

Report message to a moderator

Re: AUDIT on SYS [message #563390 is a reply to message #563388]

Mon, 13 August 2012 07:45

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

cookiemonster wrote on Mon, 13 August 2012 14:13

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.

And this is the purpose of audit_sys_operations parameter.

Regards
Michel

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

Report message to a moderator

Re: AUDIT on SYS [message #563393 is a reply to message #563391]

Mon, 13 August 2012 08:11

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Unfortunately, there is no log getting generated in the default path, as the document says.

On Windows, it is in the system event log (as the documentation says: "The audit records are written to the operating system's audit trail.").

Regards
Michel

Report message to a moderator

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

Report message to a moderator

Re: AUDIT on SYS [message #563470 is a reply to message #563461]

Tue, 14 August 2012 01:49

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You cannot audit SYS with the standard audit. Don't you understand what I wrote in red?

Regards
Michel

Report message to a moderator

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:
$C:\Documents and Settings\muthu.krishnan\Desktop\misc\a.jpeg$

Regards
Muktha

[Updated on: Fri, 24 August 2012 01:24] by Moderator

Report message to a moderator

Re: AUDIT on SYS [message #564460 is a reply to message #564459]

Fri, 24 August 2012 01:23

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

There is no output.

Regards
Michel

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.

Report message to a moderator

Re: AUDIT on SYS [message #564462 is a reply to message #564461]

Fri, 24 August 2012 01:58

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You have to click on "Reply" then on "Upload file"...

Regards
Michel

Report message to a moderator

Previous Topic:	How much size of disk Tablespace can consume in proportion to Data in DB?
Next Topic:	non-expiring SYSTEM and SYS password

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 02:54:06 CDT 2024