9 Replies Latest reply: Mar 6, 2013 5:41 AM by Frank Kulash RSS

    Error in "Execute Immediate with Grant" command.

    bhatt t
      Hi!

      I have one procedure, which should GRANT ALL privileges on all TABLES,
      but it shows errors as below;

      ------------------------------------------------------------------------------------------------------------------------------------------
      SQL> ed
      Wrote file afiedt.buf

      1 create or replace procedure grnt2
      2 as
      3 Cursor tmp is select object_name from tmp_tab2
      4 vobj varchar2(100);
      5 begin
      6 open tmp;
      7 loop
      8 fetch tmp into vobj;
      9 exit when tmp%notfound;
      10 execute immediate 'grant all on '||vobj||' to sasnew2';
      11 end loop;
      12 close tmp;
      13* end;
      SQL> /

      Warning: Procedure created with compilation errors.

      SQL> exec grnt2;
      BEGIN grnt2; END;

      *
      ERROR at line 1:
      ORA-06550: line 1, column 7:
      PLS-00905: object SASNEW.GRNT2 is invalid
      ORA-06550: line 1, column 7:
      PL/SQL: Statement ignored

      ---------------------------------------------------------------------------------------------------------------------------------

      Actually error in line no.10 "Execute Immediate" command line;

      Pl guide me how to use Execute Immediate with Grant option .

      Regards.
        • 1. Re: Error in "Execute Immediate with Grant" command.
          Manik
          Can you check this.. untested.
          CREATE OR REPLACE PROCEDURE grnt2
          AS
             CURSOR tmp
             IS
                SELECT object_name FROM tmp_tab2;
          
             vobj   VARCHAR2 (100);
          BEGIN
             OPEN tmp;
          
             LOOP
                FETCH tmp INTO vobj;
          
                EXIT WHEN tmp%NOTFOUND;
          
                EXECUTE IMMEDIATE
                   'grant all on ' || vobj || ' to sasnew2 with grant option';
             END LOOP;
          
             CLOSE tmp;
          END;
          • 2. Re: Error in "Execute Immediate with Grant" command.
            Frank Kulash
            Hi.
            982164 wrote:
            Hi!

            I have one procedure, which should GRANT ALL privileges on all TABLES,
            but it shows errors as below;

            ------------------------------------------------------------------------------------------------------------------------------------------
            SQL> ed
            Wrote file afiedt.buf

            1 create or replace procedure grnt2
            2 as
            3 Cursor tmp is select object_name from tmp_tab2
            4 vobj varchar2(100);
            5 begin
            6 open tmp;
            7 loop
            8 fetch tmp into vobj;
            9 exit when tmp%notfound;
            10 execute immediate 'grant all on '||vobj||' to sasnew2';
            11 end loop;
            12 close tmp;
            13* end;
            SQL> /

            Warning: Procedure created with compilation errors.

            SQL> exec grnt2;
            BEGIN grnt2; END;

            *
            ERROR at line 1:
            ORA-06550: line 1, column 7:
            PLS-00905: object SASNEW.GRNT2 is invalid
            ORA-06550: line 1, column 7:
            PL/SQL: Statement ignored

            ---------------------------------------------------------------------------------------------------------------------------------

            Actually error in line no.10 "Execute Immediate" command line;
            How did you determine that the error actually is on line 10?
            You're missing a semicolon at the end of line 4; I would suspect that was an error.

            Are you sure that the procedure above is what you're running?

            The error message looks like error you posted occured when you tried to run the procedure. Post the error messages that were caused when you compiled the procedure.

            Ehenever you develop dynamic SQL, put the dynamic command into a variable. In the early stages of testing, don't actually execute the statment; just display it. For example:
            SET   SERVEROUTPUT   ON
            
            
            create or replace procedure grnt2
            as
                Cursor tmp is  select  object_name 
                                    from    tmp_tab2;
                vobj       varchar2 (100);
                sql_txt    varchar2 (200);     
            begin
                open tmp;
                loop
                    fetch tmp into vobj;
                 exit when tmp%notfound;
                 sql_txt := 'grant all on ' || vobj || ' to public'
                 dbms_output.put_line (   sql_txt
                                || ' = sql_txt in grnt2'
                                );
            --      execute immediate sql_txt;
                 end loop;
                 close tmp;
            end;
            /
            SHOW ERRORS
            If the output looks good, then you can un-comment the EXECUTE IMMEDIATE line. Comment out the call to put_line when you're finished with testing.


            Edited by: Frank Kulash on Mar 5, 2013 9:02 AM
            • 3. Re: Error in "Execute Immediate with Grant" command.
              bhatt t
              Hi Frank!

              Same error again.

              Can you have any other idea for 'Granting All privileges on All tables' ?

              Thanks & Regards.
              • 4. Re: Error in "Execute Immediate with Grant" command.
                bhatt t
                Hi Manik!

                Same error again.

                Can you have any other idea for 'Granting All privileges on All tables' ?

                Thanks & Regards.
                • 5. Re: Error in "Execute Immediate with Grant" command.
                  Manik
                  who owns this table: SASNEW.GRNT2?

                  And using which user are you creating the procedure which you posted?

                  Check if the procedure owner has proper privilege over SASNEW.GRNT2 to grant it to sasnew2.

                  Cheers,
                  Manik.
                  • 6. Re: Error in "Execute Immediate with Grant" command.
                    bhatt t
                    Hi Sir,

                    "SASNEW" is the owner, from which procedure is to be executed.

                    Want to give grant to other user "SASNEW2".

                    Both the users have DBA,CONNECT,RESOURCE privlileges, granted by "SYSTEM" user.

                    Is there any other privileges required ?

                    Thanks.
                    • 7. Re: Error in "Execute Immediate with Grant" command.
                      Manik
                      can you try this and see if it is successful:
                      grant all on GRNT2 to SASNEW2 with grant option;
                      Cheers;
                      Manik.
                      • 8. Re: Error in "Execute Immediate with Grant" command.
                        bhatt t
                        Sir,

                        GRNT2 procedure itself is invalid and not compiled successfully,
                        so problem is its invalidity.

                        Anyway thanks of your advice.
                        • 9. Re: Error in "Execute Immediate with Grant" command.
                          Frank Kulash
                          Hi,
                          982164 wrote:
                          Hi Frank!

                          Same error again.
                          So you changed the procedure, but it still doesn't compile?

                          Post the new version of your procedure. I can't say what you're doing wrong when I don't know what you're doing.