11 Replies Latest reply: Jul 28, 2004 7:54 AM by 403551 RSS

    applaying trigger

    421632
      hi
      i want to create a log file of all activity took place in my data base at table level
      actually i want to implement security in my database
      i have a single user data base testing/test
      and a number of users will access it i am using form 6i dont have enterprise manager
      i want to some of my users can update delete or create new records and some can only update and some cant do any thing except reading data
      from database
      and along with it i want to create a log file when ever any one connect with testing/test
      and i want to keep track of every activity he/she took place along with its time and date
      and duration of session as well
      i want to create that log file on server where the database will be installed and my forms will be installed on different machines
      plz help me in detail i dont know any thing about it
      thanx
        • 1. Re: applaying trigger
          403551
          hi
          i want to create a log file of all activity took
          place in my data base at table level
          r.- If you want to audit all the people do with the tables, I recommend you to use the audit options of the RDBMS instead of creating a lot of triggers. This is possible if you have the Enterprise Versión.
          actually i want to implement security in my database

          i have a single user data base testing/test
          and a number of users will access it i am using form
          6i dont have enterprise manager
          i want to some of my users can update delete or
          r.- If all get loging with the same user you will be able to at 100% what you want.
          create new records and some can only update and some
          cant do any thing except reading data
          from database
          r.- To do that you have to have single users of the database to login on it and for implanting something like that you must:

          - Create users on demand
          - Stablish roles and user groups and so on
          and along with it i want to create a log file when
          ever any one connect with testing/test
          r.- That can be done with triggers on logon.
          and i want to keep track of every activity he/she
          took place along with its time and date
          and duration of session as well
          R.- try to avoid to create triggers for all because that can decrease the performance of your database.
          i want to create that log file on server where the
          database will be installed and my forms will be
          installed on different machines
          plz help me in detail i dont know any thing about it

          thanx
          If you need more guide you can replay this thread

          Joel Pérez
          http://otn.oracle.com/experts
          • 2. Re: applaying trigger
            421632
            hi Joel Pérez
            its my luck or what i got post from u i use to see ur profile on ONT Experts and i was thinking that one day i will be like u and have my pic over there as well
            i have just completed my masters degree and working as an internee in an organization

            i think u would have calculated that by my questions such a basic i dont even know that but i am learning and trying to get ur place or a place with at ur side

            i have plained to do DBA as well and very soon i am going to start my classes for preparation for DBA online test

            any way that was all just .... actually i am very excited to have post from u

            u have told that i can implement trigger on logon and then u said dont apply triggers they will slow down my database then whet to do how can i achieve that and plz if u can send me a sample trigger so that i can change it or can lear from it
            whet if i only create a log file to keep track of login time + ip address from where the request comes and logout time of that ip
            is that possible plz help me and plz avoide my english i know its not soo good :)
            take care
            bye
            • 3. Re: applaying trigger
              403551
              Hi Munzer Javed

              Where are you from ?

              Have you created any trigger ?

              Joel Pérez
              http://otn.oracle.com/experts
              • 4. Re: applaying trigger
                403551
                Here I am going to help you with an example:

                1.- In a specific schema create table named times_visited so:

                create table times_visited ( c1 varchar2(40));

                2.- Get connection as Sys and create the followinf trigger:

                CREATE OR REPLACE TRIGGER <USERNAME>.AUDIT_TRIGGER1
                AFTER
                LOGON ON DATABASE
                BEGIN
                insert into TIMES_VISITED
                select 'LOG-ON-> ' ||to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;
                END;

                USERNAME : must be the username that you choose in the step 1

                4.- Get log in with that schema and check the inserted on the mentioned table.

                Joel Pérez
                http://otn.oracle.com/experts
                • 5. Re: applaying trigger
                  403551
                  Here I am going to help you with an example:

                  1.- In a specific schema create table named times_visited so:
                  create table times_visited ( c1 varchar2(40));
                  2.- Get connection as Sys and create the followinf trigger:
                  CREATE OR REPLACE TRIGGER <USERNAME>.AUDIT_TRIGGER1
                  AFTER
                  LOGON ON DATABASE 
                  BEGIN 
                  insert into TIMES_VISITED
                  select 'LOG-ON-> ' ||to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;
                  END;
                  USERNAME : must be the username that you choose in the step 1

                  4.- Get log in with that schema and check the inserted on the mentioned table.

                  if you need more help on this you can replay the times you want. Go ahead in your career as DBA. I am 25 years old.

                  Joel Pérez
                  http://otn.oracle.com/experts
                  • 6. Re: applaying trigger
                    421632
                    hi Joel Pérez

                    thank you for ur replay and sorry for my late replay actully i was busy in some family probs

                    well i am from Pakistan > Islamabad and have just completed my masters in computer sciences and working as internee in Ufone ( cell phone comp) and i am 24

                    i have tried to create trigger but it gives me error
                    invalid trigger name
                    with start on the username (testing) and trigger failes i have created the table in my schema
                    after creating table i connected with system/manager
                    and past ur code of tigger in that
                    am i doing some thing wrong
                    plz guide me
                    take good care of ur self coz its importent for those who cears about u
                    Allah Hafiz ( God bless u )
                    bye
                    • 7. Re: applaying trigger
                      403551
                      Suppose you have to audit the logins of user scott , you have to do this:

                      1.- Create that table in scott schema
                      2.- Get connection as sys and create the trigger
                      3.- Test the functionality.

                      I am 25 years old. I am from Venezuela and I am working in Venezuela and I am preparing all to work in Europe or USA.

                      Joel Pérez
                      http://otn.oracle.com/experts
                      • 8. Re: applaying trigger
                        421632
                        hi Joel Pérez

                        i am sorry for distrubing u again and again for a smiple thing but that error i am receving after connectin sys and creating trigger

                        its good that u have planed to go abroad but what i think is UK, USA are now saturated u should try for Canada, Australia, Europe any way best of Luck

                        STEP NO 1

                        SQL> connect scott/tiger
                        Connected.
                        SQL> create table times_visited ( c1 varchar2(40));

                        Table created.

                        SQL>

                        STEP NO 2
                        SQL> connect sys/change_on_install
                        Connected.
                        SQL> ed
                        Wrote file afiedt.buf

                        1 CREATE OR REPLACE TRIGGER <scott>.AUDIT_TRIGGER1
                        2 AFTER
                        3 LOGON ON DATABASE
                        4 BEGIN
                        5 insert into TIMES_VISITED
                        6 select 'LOG-ON-> ' ||to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;
                        7* END;
                        8 /
                        CREATE OR REPLACE TRIGGER <scott>.AUDIT_TRIGGER1
                        *
                        ERROR at line 1:
                        ORA-04070: invalid trigger name
                        • 9. Re: applaying trigger
                          403551
                          Do not worry friend!

                          You have to create it so:
                          CREATE OR REPLACE TRIGGER SCOTT.AUDIT_TRIGGER1
                          AFTER
                          LOGON ON DATABASE
                          BEGIN
                          insert into TIMES_VISITED
                          select 'LOG-ON-> ' ||to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;
                          END;
                          Joel Pérez
                          http://otn.oracle.com/experts
                          • 10. Re: applaying trigger
                            421632
                            hi Joel Pérez

                            yes its working know i am going to impliment it on my project

                            should i say thinx coz i think no thanx and sorry for friends

                            :)
                            take good care
                            Allah Hafiz (God Bless u)
                            bye
                            • 11. Re: applaying trigger
                              403551
                              Your welcome Munzer Javed

                              If you any another question you can post a new thread. I recommend you to use this forum a lot to help you to work efficiently with Oracle Technology.

                              My personal e-mail is : ing_joelperez@hotmail.com

                              Joel Pérez
                              http://otn.oracle.com/experts