Forum Stats

  • 3,723,801 Users
  • 2,244,620 Discussions
  • 7,850,712 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Audit SYS and SYSTEM user logging to Database

2796227
2796227 Member Posts: 112

Hi All,

I need to find SYS and SYSTEM user logging history.

Note-

SQL> show parameter audit;

NAME                                 TYPE                             VALUE

------------------------------------ -------------------------------- ------------------------------

audit_file_dest                      string                           /u02/app/oracle/admin/LIVESIE/adump

audit_sys_operations          boolean                          TRUE

audit_syslog_level                string

audit_trail                             string                           DB, EXTENDED

SQL>

Database Details

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Solaris: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

OS Details

bash-3.2# uname -a

SunOS sunlivedb 5.10 Generic_150400-57 sun4u sparc SUNW,SPARC-Enterprise

bash-3.2#

Answers

  • vanantha komara-Oracle
    vanantha komara-Oracle Member Posts: 87 Employee
    edited February 2020

    Hi,

    Starting auditing

    Stopping auditing

    Reading from the AUDIT TRAIL table

    Truncating the AUD$ table

    Using the AUDIT_TRAIL view

    Listing the AUDIT_TRAIL parameter

    Setting the AUDIT_TRAIL parameter

    Shutting down and startup a database using SPFILE

    Using the AUD$ table

    Auditing an auditor

    Auditing who deletes a record

    Viewing the AUD$ table

    Stopping all auditing trail

    Commands:

    TRUNCATE TABLE aud$

    SHOW PARAMETER

    ALTER SYSTEM SET audit_trail=db SCOPE=spfile

    SHUTDOWN IMMEDIATE

    CONNECT system/manager AS SYSDBA

    STARTUP

    AUDIT delete ON sys.aud$

    AUDIT DELETE ON BY ACCESS WHENEVER SUCCESSFUL

    NOAUDIT ALL

    View the SYS.AUD$ table to display the date and time who (username), and what (statement of an action) was done.
    SQL> SELECT
    TO_CHAR(timestamp#,'DD-MON-YYYY HH24:MI:SS')

    as "Date and Time", userid, name "Action by user"
    FROM sys.aud$ JOIN sys.audit_actions
    ON action = action#
    /

    Stop auditing
    Since auditing takes a lots of disk space be sure to stop auditing when it is not needed. Try, stop auditing.
    SQL> NOAUDIT ALL
    /

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited February 2020

    This is the WRONG forum for this type of question.  This forum is for discussion of users passing or have questions on taking Oracle Certification exams.

    MODERATORS: Please move this thread to the --> SQL-PL/SQL Community forum.

Sign In or Register to comment.