1 2 3 Previous Next 33 Replies Latest reply: Dec 15, 2010 4:17 AM by Karan Kukreja RSS

    Confusion about AUDIT parameters

    Karan Kukreja
      Hi all ,

      Hope everyone had a wonderful weekend..


      My database is installed on oracle 11.1.0.7 and OS is SPARC 64 bit.

      In my init.ora file the following are the audit parameters :


      audit_trail = none
      audit_sys_operations = true

      and

      SQL> show parameter audit_trail;

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      audit_trail string NONE





      SQL> show parameter audit

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      audit_file_dest string /apps/oracle/product/11.1.0.7.
      2010Q1/rdbms/audit
      audit_sys_operations boolean TRUE
      audit_syslog_level string
      audit_trail string NONE


      Since the Audit_sys_operations is TRUE and audit_trail is NONE , what does it mean, Is audit enabled ?

      Becuase as per my knowledge audit_sys_operations is auditing of all the operations done by sys user.. but here Audit_trail is NONE.



      Also , if I just want to track login and logout time for a particular user , is there any method ? using Audit or without Audit ?


      regards
      KKuKreja

      PS: please tell me how to put the output from SQL in proper format here.
        • 1. Re: Confusion about AUDIT parameters
          Karan Kukreja
          while i posted here , i googled also and this link was helpful :

          http://www.oracle-base.com/articles/10g/Auditing_10gR2.php


          regards
          KKuKreja
          • 2. Re: Confusion about AUDIT parameters
            Fahd.Mirza
            Yes, that article is very helpful. Also see the related docs at otn.oracle.com

            regards
            • 3. Re: Confusion about AUDIT parameters
              Karan Kukreja
              Hi Fahd ,

              thanks for your reply.

              I still have 2 doubts :

              1. if Audit_trail=none and audit_sys_operations=true , will it be performing any audit for sys and putting it in sys.AUD$ table ?

              2. If Auditing is not enabled , how can i track the login and logout for that user ?


              regards
              KKuKreja
              • 4. Re: Confusion about AUDIT parameters
                731759
                If the audit_trail=none and the audit_sys_operations=true, the audit informations will not written into sys.AUD$ table.

                refer this,
                http://www.red-database-security.com/wp/sentrigo_webinar.pdf
                http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams014.htm

                If auditing is not enabled write a logon trigger to find out the logging users.

                Also check this scenario this can clear your doubts,
                http://ayyudba.blogspot.com/2007/10/auditsysoperations-set-to-false-yet.html

                Thanks

                Edited by: Cj on Dec 12, 2010 10:08 PM
                • 5. Re: Confusion about AUDIT parameters
                  Karan Kukreja
                  Hi Cj ,

                  Yes, i reailsed that we need to create an after logon and before logoff trigger for this. But our database access is done through an API and not manually .. So we need to check if that works..

                  Also when you say :

                  If the audit_trail=none and the audit_sys_operations=true, the audit informations will not written into sys.AUD$ table.

                  Then it means it will stored in OS audit trail.



                  Thanks and Regards
                  KKuKreja

                  Edited by: KKuKreja on Dec 12, 2010 10:58 PM
                  • 6. Re: Confusion about AUDIT parameters
                    Karan Kukreja
                    Thanks all :)
                    • 7. Re: Confusion about AUDIT parameters
                      Karan Kukreja
                      THIS IS RE-OPENED SINCE I HAD A FEW MORE QUERIES
                      .. i Have a more clear picture now..

                      i am on 11G and in the init.ora file

                      audit_trail=none and audit_sys_operations=true

                      is set.

                      We also have an API which is used by a user to access a procedure. But as far as i could understand , logon and logoff trigger wont be able to track this users activity on that procedure.


                      We dont have any access to the API.


                      What could be the possible solution ?
                      Is auditing the only option ?


                      thanks
                      ~K

                      Edited by: KKuKreja on Dec 13, 2010 5:00 AM
                      • 8. Re: Confusion about AUDIT parameters
                        Karan Kukreja
                        PLease follow up. I have reopened the question.
                        • 9. Re: Confusion about AUDIT parameters
                          Karan Kukreja
                          Someone please have a look and suggest..

                          thanks and regards
                          ~K
                          • 10. Re: Confusion about AUDIT parameters
                            Chinar
                            --KKuKreja-- wrote:
                            Someone please have a look and suggest..

                            thanks and regards
                            ~K
                            You have to enable auditing(setting audit_trail) and need\
                            audit execute procedure by access 
                            or
                            audit execute procedure on <procedure name>  by access
                            • 11. Re: Confusion about AUDIT parameters
                              Karan Kukreja
                              Hi Chinar,

                              Thanks for your reply.

                              I am a little confused here..

                              On asking my team more , this is what i got to know :

                              1.we know the name of the user also
                              2. We also got to know the Procedure it is accessing.

                              The user is connecting through an API which we are not aware of.

                              So as per your answer :

                              I need to change my current audit_trail=db.


                              This will enable auditing.. Now how will it decide what all to audit.???

                              That will be decided by the command :

                              __________________________________________________________
                              audit execute procedure by access


                              or

                              audit execute procedure on <procedure name>  by access

                              ____________________________________________________________

                              But this will audit entire procedures or one procedure by all .

                              I need only by a particlualr user.

                              I checked on this link :
                              http://www.oracle-base.com/articles/10g/Auditing_10gR2.php

                              and found out this :

                              AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;

                              But here Its not giving me any place where i can fit my procedure name.

                              Is there anyway i can make it more specific , like

                              audit for one user accessing only one procedure.

                              and what would be the exact syntax.
                              • 12. Re: Confusion about AUDIT parameters
                                Karan Kukreja
                                Hi Chinar ,


                                I am logged in as / as sysdba

                                and when i gave this :
                                AUDIT EXECUTE PROCEDURE BY <user_name> BY ACCESS;
                                worked , but when i am executing :


                                audit execute procedure on <schema_name.procedure name> by access;
                                it is throwing this error :

                                AUDIT EXECUTE PROCEDURE ON abc.procedure BY ACCESS;
                                *
                                ERROR at line 1:
                                ORA-00933: SQL command not properly ended

                                The '*' is coming under ON.

                                PLease check..

                                thanks

                                ~K
                                • 13. Re: Confusion about AUDIT parameters
                                  CKPT
                                  sorry

                                  give
                                  AUDIT EXECUTE PROCEDURE by abc.procedure BY ACCESS;
                                  Thanks
                                  • 14. Re: Confusion about AUDIT parameters
                                    Karan Kukreja
                                    Hi CKPT,

                                    AUDIT EXECUTE PROCEDURE by schema.proc_name BY ACCESS
                                    *
                                    ERROR at line 1:
                                    ORA-00956: missing or invalid auditing option


                                    now the '*' is coming at the '.' in schemaname and procedurename.


                                    please suggest

                                    ~K
                                    1 2 3 Previous Next