6 Replies Latest reply on Oct 27, 2015 10:36 AM by comader

    ASP for Oracle DB Audit Trail via Syslog

      Hi all,

       

      someone has already developed an ASP for parsing the Oracle Database Audit sent via Syslog?

      Nitro ERC by default collects Orace Database Audit events (9i 10i and 11i) connecting through the listener and using the database internal audit trail table (SYS.AUD$). Refer to KB74694 https://kc.mcafee.com/corporate/index?page=content&id=KB74694&actp=search&viewlo cale=en_US&searchid=1355265898086

       

      The problem with this solution is that Oracle does not log SYS activity on the internal audit trail table (SYS.AUD$) but only on OS syslog or on an xml external file. Actually ELM is not able to collect SYS operations, and SYS is the most powerful user in Oracle (such as root or administrator in unix and windows environments)

       

      Thanks a lot for any advice or suggestion

       

      Andrea

        • 1. Re: ASP for Oracle DB Audit Trail via Syslog

          With

           

          audit_sys_operations=TRUE

           

          in init*.ora you can log SYS operations.

          • 2. Re: ASP for Oracle DB Audit Trail via Syslog
            rashid47010

            HI

             

            We want to audit oracle DB for that we follow below procedure:

             

            Open the init<ORACLE_SID>.ora file.

             

            Enter the following two lines of text to the file:

                      audit_trail = DB

                      audit_sys_operations=TRUE

             

            Restart the database.

             

            Run the appropriate command(s) as SYS user from SQL*PLUS, for example:

             

            audit select any table;

            audit update any table;

            audit delete any table;

            audit insert any table;

            audit all privileges;

            audit session;

             

            and under the properties of Oracle DS:

             

            Data Dource Model: Oracle Audit

             

            We create one user under the oracle which have the full rights against sys.aud$ table.

             

            but in the nitro view I just see the logs that show me that user( the user that we use in the properties of oracle DS) connects with oracle.

             

            No other logs at all

             

            Please advise

            • 3. Re: ASP for Oracle DB Audit Trail via Syslog
              uzanatta

              Hi,

               

              some DataSource are not well documentated on the ESM documentation. I struggled for a while with Oracle ASP; only doing a reserve engineering on the configuration file I found the solution.

               

              The parser looks at the DBA_COMMON_AUDIT_TRAIL table in order to fetch audit events.

               

              Rgds,

              • 4. Re: ASP for Oracle DB Audit Trail via Syslog
                rashid47010

                Thanks for your efforts.

                We can see the logs through Oracle Audit(ASP) but the problem is that

                 

                1- The oracle is hosted on Solaris system and we need the logs from both Oracle and Solaris system.

                2- Some one from Mcafee recommend that configure the One DS for both type of (Oracle Audit(ASP) and Linux(ASP)) Logs.

                 

                I have question that that how system will differenciate both type of logs although login (both system and in oracle) will catagorize under one catagory.

                 

                Alternatively we create two data sources one for Oracle and other for Solaris.

                 

                Regarding oralce, we select Oracle Audit in which SIEM is directly fetching the events from Oracle DB. but

                 

                1- I can see the traffic on port 1521 through tcpdump. but no log is appreaing under the respective DS folder. only one log count with shows that the user(which we configure under DS configuration to connect with DB) is connected with DB.

                 

                Secondly is all type of logs (Login, Logff, Create table, drop table, grant, revoke etc) are going to your mentioned table(DBA_COMMON_AUDIT_TRIAL)...........?

                 

                Please advise accordingly.

                 

                 

                 

                Rashid

                • 5. Re: ASP for Oracle DB Audit Trail via Syslog
                  uzanatta

                  Hi,

                   

                  Oracle ASP and Solaris use different collectors so you can set up two different datasources with the same Ip, or you can enable Oracle rules on Solaris datasource.

                  You should set up Oracle in order to send all logs into DBA_COMMON_AUDIT_TRIAL table.

                   

                  Rgds,

                  • 6. Re: ASP for Oracle DB Audit Trail via Syslog
                    comader

                    Hello guys,

                     

                    How to set up Oracle in order to send log to DBA_COMMON_AUDIT_TRIAL table?


                    ESM Version: 9.5

                    Data Source Model: Oracle Audit - SQL Pull (ASP)

                     

                    Now we made these steps:

                    1. Open the init<ORACLE_SID>.ora file.
                    2. Add the following two lines of text to the file:

                      audit_trail = DB
                      audit_sys_operations=TRUE


                    3. Restart the database.
                    4. Run the appropriate command(s) as SYS user from SQL*PLUS, for example:

                      audit select any table;
                      audit update any table;
                      audit delete any table;
                      audit insert any table;
                      audit all privileges;
                      audit session;

                     

                    SIEM connects to DB, credentials are OK, but there are no logs from DB. Using tcpdump I see packets from DB to SIEM on port 1521. Using wireshark I see data from DB:

                     

                    ORA-01403: no data found

                     

                    ORA-0 0942: table or view does not exist

                     

                    Could someone please suggest any solution or troubleshootings steps?

                     

                    Regards,