14 Replies Latest reply: Feb 17, 2013 12:24 PM by EdStevens RSS

    trigger

    941949
      hi everybody ,
      can you people please help me to correct this trigger?i wrote this trigger to capture select,insert,update and delete statements.this trigger not showing any error.problem is in table named test i m not getting sql statements run by the users and when i check the dba_fga_audit_trail i m not getting client machine name,client ip address and client name who runs the sql statements in the database,why ?your help appreciated.thanks.


      CREATE TABLE test
      (user_id varchar2(30),
      sess_id number(10),
      logon_time varchar2(100),
      client_ip varchar2(20),
      client_name varchar2(150),
      client_machine varchar2(150),
      v_program varchar2(100)
      );


      CREATE OR REPLACE TRIGGER trg_session_info
      after logon on database
      ON DATABASE
      DECLARE
      cnt integer;
      CURSOR get_tab IS
      SELECT table_name FROM sys.dba_tables where owner='SCOTT';
      v_tabname get_tab%rowtype;
      begin
      insert into test
      (
      user_id,sess_id,logon_time,client_ip,
      client_name,client_machine,v_program
      )
      select username,sid,systimestamp,sys_context('USERENV','IP_ADDRESS'),
      osuser,machine,program
      from v$session
      where sid = (select sid from v$mystat where rownum = 1 )
      and ( upper(program) like '%TOAD%'
      or upper(program) like '%SQLPLUS%'
      );
      IF NOT get_tab%ISOPEN THEN
      OPEN get_tab;
      END IF;

      LOOP

      FETCH get_tab INTO v_tabname;
      EXIT WHEN get_tab%NOTFOUND;
      dbms_output.put_line(v_tabname.table_name);

      select count(*) into cnt from dba_audit_policies where object_schema='SCOTT' and object_name=v_tabname.table_name;
      if cnt=0 then
      dbms_fga.add_policy (
      object_schema => 'SCOTT',
      object_name => v_tabname.table_name,
      policy_name => 'AUDIT_' || v_tabname.table_name,
      audit_column => NULL,
      audit_condition => NULL,
      statement_types => 'SELECT,INSERT,UPDATE,DELETE'
      );
      end if;
      END LOOP;
      END;
        • 1. Re: trigger
          Nitesh.
          ...

          Edited by: Niteshkhush on Feb 12, 2013 5:05 PM
          • 2. Re: trigger
            941949
            is there anybody here on this forum to help me in solving this trigger issue
            • 3. Re: trigger
              Karthick_Arp
              Most of the time we feel the tool that we use is not working properly. But the actual issue would be that the choice of tool. Is the correct tool used to adress the problem.

              So rather than putting some piece of code and asking to fix it, if you could tell your requirement and what you are trying to achieve may be you could end up getting a better and more optimal solution.
              • 4. Re: trigger
                Padma....
                Hi,

                You have done an insert in the trigger but have not committed it. so changes would not be visible. So use a commit along with PRAGMA AUTONOMOUS TRANSACTION and i hope it would help.

                Thanks
                Padma...
                • 5. Re: trigger
                  John Stegeman
                  You have done an insert in the trigger but have not committed it. so changes would not be visible. So use a commit along with PRAGMA AUTONOMOUS TRANSACTION and i hope it would help.
                  No, please don't do this.

                  There's a reason that commit is not allowed in triggers. Well, more than one, actually.
                  • 6. Re: trigger
                    Paul  Horth
                    Padma.... wrote:
                    Hi,

                    You have done an insert in the trigger but have not committed it. so changes would not be visible. So use a commit along with PRAGMA AUTONOMOUS TRANSACTION and i hope it would help.

                    Thanks
                    Padma...
                    Noooooo! Do not commit inside trigger (except perhaps when error logging).

                    See http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4003705800346120218
                    • 7. Re: trigger
                      Chanchal Wankhade
                      Hi,

                      There is no practical way to capture the executing SQL statement text inside of a DML Trigger fired by that statement.
                      • 8. Re: trigger
                        941949
                        thank you all for quick response and my requirement is to write a trigger to capture "select,insert,update and delete" statements executed by the users(or clients) in the oracle database (10g or 10.2.0.3),from which machine(machine name)these statements executed,machine ipaddress,logon time of users who run the sql statements ,statements execution time .
                        your help HIGLY APPRECIATED.
                        • 9. Re: trigger
                          EdStevens
                          938946 wrote:
                          thank you all for quick response and my requirement is to write a trigger to capture "select,insert,update and delete" statements executed by the users(or clients) in the oracle database (10g or 10.2.0.3),from which machine(machine name)these statements executed,machine ipaddress,logon time of users who run the sql statements ,statements execution time .
                          your help HIGLY APPRECIATED.
                          If your requirement is to "write a trigger", then whoever created the requirement needs some serious re-eduction.

                          Your requirement should be to "capture <data>)"

                          The requirements are not the same.

                          If you see the requirement as "capture data", you will soon discover there are better methods than a trigger ....

                          If your only tool is a hammer, every problem looks like a nail.

                          Edited by: EdStevens on Feb 14, 2013 7:12 AM
                          • 10. Re: trigger
                            941949
                            my requirement is to capture tha data plus sql statements.i want customised auditing (for a single db user).
                            • 11. Re: trigger
                              Etbin
                              Taking a look at http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_fga.htm#ARPLS66348 might help

                              Regards

                              Etbin
                              • 12. Re: trigger
                                941949
                                is there naybody?who can solve the above trigger and modify the trigger according to my requirement?

                                Edited by: 938946 on Feb 17, 2013 4:59 AM
                                • 13. Re: trigger
                                  Etbin
                                  I try to aviod triggers whenever possible, nevertheless maybe this: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:59412348055

                                  Regards

                                  Etbin
                                  • 14. Re: trigger
                                    EdStevens
                                    938946 wrote:
                                    my requirement is to capture tha data plus sql statements.i want customised auditing (for a single db user).
                                    What, specifically, about your requirement do you feel cannot be met with either standard auditing or Fine Grained Auditing, already available as part of the oracle database? I seriously doubt you need to re-invent the wheel.