4 Replies Latest reply on Dec 3, 2012 11:13 AM by 846231

    Secure DML procedure

      Hi all,

      I am browsing lots of procedures in our database, so I could learn different technics from them.

      I fould this one:
      create or replace 
      PROCEDURE secure_dml
        IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
              OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
           RAISE_APPLICATION_ERROR (-20205,
                'You may only make changes during normal office hours');
        END IF;
      END secure_dml;
      It seems that this prohibits developers from changing codes during non-office hours.
      How do I run or apply it?

      Thanks a lot,
        • 1. Re: Secure DML procedure
          Either you have to add to trigger of all tables ( before insert or update or delete), if yoyu require for all tables / for a necessary tables.

          Or you can have this y writing a trigger on database logon.
          1 person found this helpful
          • 2. Re: Secure DML procedure
            I'm not sure that I see what relationship this has to making code changes. Code changes are DDL not DML.

            You would call this procedure from whatever procedure you are using to do DML
              <<do some DML>>
            or you could call the procedure from a trigger on whatever table you wanted.

            Of course, I'm not sure why you would want to restrict people from making DML changes outside of business hours. It seems reasonable to suspect that people will occasionally want to start working before 8am or stay after 6pm or work on a weekend (particularly if you ever envision supporting users in multiple time zones). Explaining to the business that allowing them to finish the changes they are working on will require a code change because they couldn't get them done by 6pm seems like a poor career move.

            If you wanted to apply this to DDL (despite the name of the procedure), you could potentially invoke it from a DDL trigger. But, again, that doesn't seem like the greatest idea. A developer's ability to make code changes in production should be nonexistant. Changes should be made by a DBA based on scripts stored in source control. Code changes are almost always made before or after normal business hours-- the business is rarely willing to let the system be unavailable during normal hours just for the convenience of the DBAs so that they don't have to apply changes after hours.

            1 person found this helpful
            • 3. Re: Secure DML procedure
              KinsaKaUy? wrote:

              I am browsing lots of procedures in our database, so I could learn different technics from them.
              Looking at the example posted, there's not much to learn.

              Programming standards and naming conventions? Missing!

              Changing a date into a string to perform date logic/conditional branching? Wrong!

              Hard coding system calls (like <i>raise_application_error</i>) and not wrapping these into custom wrapper procedures or functions? Highly questionable!
              1 person found this helpful
              • 4. Re: Secure DML procedure
                I thank you all :)

                Maybe this is just a test code made by some developers? and not been used at all.