This discussion is archived
4 Replies Latest reply: Dec 3, 2012 3:13 AM by 846231 RSS

Secure DML procedure

846231 Newbie
Currently Being Moderated
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
IS
BEGIN
  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,
Kinz
  • 1. Re: Secure DML procedure
    908002 Expert
    Currently Being Moderated
    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.
  • 2. Re: Secure DML procedure
    Justin Cave Oracle ACE
    Currently Being Moderated
    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
    BEGIN
      secure_dml;
    
      <<do some DML>>
    END;
    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.

    Justin
  • 3. Re: Secure DML procedure
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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!
  • 4. Re: Secure DML procedure
    846231 Newbie
    Currently Being Moderated
    I thank you all :)


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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points