Home » RDBMS Server » Server Administration » AUDIT on SYS (oracle, 10g, windows xp)
AUDIT on SYS [message #563375] Mon, 13 August 2012 06:15 Go to next message
muktha_22
Messages: 526
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67295
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 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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 Go to previous messageGo to next message
John Watson
Messages: 8320
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.
Re: AUDIT on SYS [message #563390 is a reply to message #563388] Mon, 13 August 2012 07:45 Go to previous messageGo to next message
Michel Cadot
Messages: 67295
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
Re: AUDIT on SYS [message #563391 is a reply to message #563389] Mon, 13 August 2012 07:46 Go to previous messageGo to next message
muktha_22
Messages: 526
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 #563393 is a reply to message #563391] Mon, 13 August 2012 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 67295
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
Re: AUDIT on SYS [message #563461 is a reply to message #563393] Tue, 14 August 2012 00:25 Go to previous messageGo to next message
muktha_22
Messages: 526
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 #563470 is a reply to message #563461] Tue, 14 August 2012 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 67295
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
Re: AUDIT on SYS [message #564459 is a reply to message #563375] Fri, 24 August 2012 01:21 Go to previous messageGo to next message
muktha_22
Messages: 526
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67295
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no output.

Regards
Michel
Re: AUDIT on SYS [message #564461 is a reply to message #564460] Fri, 24 August 2012 01:53 Go to previous messageGo to next message
muktha_22
Messages: 526
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.
Re: AUDIT on SYS [message #564462 is a reply to message #564461] Fri, 24 August 2012 01:58 Go to previous message
Michel Cadot
Messages: 67295
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to click on "Reply" then on "Upload file"...

Regards
Michel
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:
  


Current Time: Tue Aug 11 17:46:02 CDT 2020