5 Replies Latest reply: Nov 21, 2012 5:51 PM by rp0428 RSS

    stacking quotes - need another pair of eyes

    EdStevens
      Oracle 11.2.0.1 SE-One, 64-bit
      Oracle Linux 5.6 x86-64

      Given the following procedure, and focusing on the call to dbms_scheduler
      create or replace 
      PROCEDURE dw.fix_job_timezone(
          p_jobschema_in IN VARCHAR2 default null) 
      IS
        
      type sched_jobs_tbl_type
      IS
        TABLE OF dba_scheduler_jobs.job_name%TYPE INDEX BY binary_integer;
        t_sched_jobs sched_jobs_tbl_type;
        
        v_job          VARCHAR2(128);
      BEGIN
        SELECT
          job_name 
        bulk collect INTO
          t_sched_jobs
        FROM
          dba_scheduler_jobs
        WHERE
          owner = p_jobschema_in
        ORDER BY
          job_name ;
          
        FOR i IN t_sched_jobs.first .. t_sched_jobs.last
        LOOP
          v_job := '"' || p_jobschema_in || '"."'||t_sched_jobs(i) || '"';
          dbms_output.put_line('Processing '||v_job);
          dbms_scheduler.set_attribute_null (v_job, 'START_DATE');  
        END LOOP;
      
      END;
      If the owner of the above procedure connects and calls dbms_scheduler directly:
      SQL> show user
      USER is "DW"
      SQL> exec DBMS_SCHEDULER.set_attribute_null ('"ESTEVENS"."EDS_SQLNAV_JOB1"', 'ST
      ART_DATE');
      
      PL/SQL procedure successfully completed.
      Yet, when calling the above procedure:
      SQL> exec dw.fix_job_timezone('ESTEVENS');
      Processing "ESTEVENS"."EDS_SQLNAV_JOB1"
      BEGIN dw.fix_job_timezone('ESTEVENS'); END;
      
      *
      ERROR at line 1:
      ORA-27486: insufficient privileges
      ORA-06512: at "SYS.DBMS_ISCHED", line 4398
      ORA-06512: at "SYS.DBMS_SCHEDULER", line 2892
      ORA-06512: at "SYS.DBMS_SCHEDULER", line 3028
      ORA-06512: at "DW.FIX_JOB_TIMEZONE", line 78
      ORA-06512: at line 1
      
      
      SQL>
      Maybe a problem with the enclosing quotes, or lack of?
      Change the key line to
      dbms_scheduler.set_attribute_null ('v_job', 'START_DATE');  
      And we get
      SQL> exec dw.fix_job_timezone('ESTEVENS');
      Processing "ESTEVENS"."EDS_SQLNAV_JOB1"
      BEGIN dw.fix_job_timezone('ESTEVENS'); END;
      
      *
      ERROR at line 1:
      ORA-27476: "DW.V_JOB" does not exist
      ORA-06512: at "SYS.DBMS_ISCHED", line 4398
      ORA-06512: at "SYS.DBMS_SCHEDULER", line 2892
      ORA-06512: at "SYS.DBMS_SCHEDULER", line 3028
      ORA-06512: at "DW.FIX_JOB_TIMEZONE", line 78
      ORA-06512: at line 1
      Or
      dbms_scheduler.set_attribute_null ('''||v_job||''', 'START_DATE');  
      And get
      SQL> exec dw.fix_job_timezone('ESTEVENS');
      Processing "ESTEVENS"."EDS_SQLNAV_JOB1"
      BEGIN dw.fix_job_timezone('ESTEVENS'); END;
      
      *
      ERROR at line 1:
      ORA-27452: '||v_job||' is an invalid name for a database object.
      ORA-06512: at "SYS.DBMS_ISCHED", line 4398
      ORA-06512: at "SYS.DBMS_SCHEDULER", line 2892
      ORA-06512: at "SYS.DBMS_SCHEDULER", line 3028
      ORA-06512: at "DW.FIX_JOB_TIMEZONE", line 78
      ORA-06512: at line 1
      dbms_scheduler.set_attribute_null ('v_job', 'START_DATE');  
      And get
      ERROR at line 1:
      ORA-27476: "DW.V_JOB" does not exist
      ORA-06512: at "SYS.DBMS_ISCHED", line 4398
      ORA-06512: at "SYS.DBMS_SCHEDULER", line 2892
      ORA-06512: at "SYS.DBMS_SCHEDULER", line 3028
      ORA-06512: at "DW.FIX_JOB_TIMEZONE", line 78
      ORA-06512: at line 1
      I’ve been chasing my tail to get the right combination of single-quotes and possibly concatenation, but it has eluded me.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
        • 1. Re: stacking quotes - need another pair of eyes
          Frank Kulash
          Hi,
          EdStevens wrote:
          ... If the owner of the above procedure connects and calls dbms_scheduler directly:
          SQL> show user
          USER is "DW"
          SQL> exec DBMS_SCHEDULER.set_attribute_null ('"ESTEVENS"."EDS_SQLNAV_JOB1"', 'ST
          ART_DATE');
          
          PL/SQL procedure successfully completed.
          Yet, when calling the above procedure:
          SQL> exec dw.fix_job_timezone('ESTEVENS');
          Processing "ESTEVENS"."EDS_SQLNAV_JOB1"
          BEGIN dw.fix_job_timezone('ESTEVENS'); END;
          
          *
          ERROR at line 1:
          ORA-27486: insufficient privileges
          ORA-06512: at "SYS.DBMS_ISCHED", line 4398
          ORA-06512: at "SYS.DBMS_SCHEDULER", line 2892
          ORA-06512: at "SYS.DBMS_SCHEDULER", line 3028
          ORA-06512: at "DW.FIX_JOB_TIMEZONE", line 78
          ORA-06512: at line 1
          Remember that privileges granted through roles don't count in AUTHID DEFINER stored procedures.
          Your procedure doesn't specify AUTHID, so it defaults to DEFINER; therefore, the privilges to do anything in the procedure must be granted directly to the procedure owner (or to PUBLIC), and not merely to some role that the procedure owner has.
          Maybe a problem with the enclosing quotes, or lack of?
          I don't think so. If the quotes were wrong, I would expect a compile-time error, not a run-time error. Also, regardless of when you got the error, I would expect the error message for misplaced quotes to be something about syntax, like "keyword not found where expected', rather than something about privileges.

          You're already doing the smart thing:
          {code}
          ...
          dbms_output.put_line('Processing '||v_job);
          dbms_scheduler.set_attribute_null (v_job, 'START_DATE');
          END LOOP;
          {code}
          displaying v_job pefore running it. If you can run that same command in an EXEC command (as the procedure owner), then it's definitely an AUTHID DEFINER problem, and you need the privileges granted directly to you.
          • 2. Re: stacking quotes - need another pair of eyes
            AlbertoFaenza
            Hi,

            Just a question: do you have CREATE JOB or CREATE ANY JOB granted directly to "DW" (and not through a ROLE)?

            Regards.
            Al
            • 3. Re: stacking quotes - need another pair of eyes
              EdStevens
              Alberto Faenza wrote:
              Hi,

              Just a question: do you have CREATE JOB or CREATE ANY JOB granted directly to "DW" (and not through a ROLE)?

              Regards.
              Al
              that was it (it worked after granting CREATE ANY JOB to DW), but I'm confused. I thought I understood about owner vs. invoker rights. But in this case the owner IS the invoker. And the same owner was able to make the call directly, but not in a procedure that the same owner .. owned.
              • 4. Re: stacking quotes - need another pair of eyes
                Frank Kulash
                Hi,
                EdStevens wrote:
                ... But in this case the owner IS the invoker.
                It doesn't matter who the invoker is. In an AUTHID DEFINER strored procedure, privileges have to be granted directly to the procedure owner.
                And the same owner was able to make the call directly, but not in a procedure that the same owner .. owned.
                Exactly. The owner must have had privileges granted only via some role.
                • 5. Re: stacking quotes - need another pair of eyes
                  rp0428
                  >
                  If the owner of the above procedure connects and calls dbms_scheduler directly:


                  SQL> show user
                  USER is "DW"
                  SQL> exec DBMS_SCHEDULER.set_attribute_null ('"ESTEVENS"."EDS_SQLNAV_JOB1"', 'ST
                  ART_DATE');

                  PL/SQL procedure successfully completed.
                  >
                  Does DW have the correct privileges?

                  SET_ATTRIBUTE_NULL Procedure
                  http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CIHDAIIH
                  >
                  Usage Notes
                  To run SET_ATTRIBUTE_NULL for a window, window group, or job class, you must have the MANAGE SCHEDULER privilege. Otherwise, you must be the owner of the object being altered or have ALTER privileges on that object or have the CREATE ANY JOB privilege.
                  >
                  If it is not a privilege issue try using dynamic sql to execute the call. Declare a variable that contains your actual call and then do an execute immediate on it. Can't test this right now but you want something like
                  myProcCall VARCHAR2(4000);
                  . . .
                  myProcCall := q'[DBMS_SCHEDULER.set_attribute_null ('"ESTEVENS"."EDS_SQLNAV_JOB1"', 'START_DATE')]';
                  
                  execute immediate myProcCall;
                  You would construct the 'myProcCall' value dynamically in the loop and then use execute immediate to execute it.