2 Replies Latest reply on Feb 24, 2020 7:24 PM by jaramill

    Audit SYS and SYSTEM user logging to Database

    2796227

      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#

        • 1. Re: Audit SYS and SYSTEM user logging to Database
          vanantha komara-Oracle

          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
          /

          • 2. Re: Audit SYS and SYSTEM user logging to Database
            jaramill

            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.