1 2 Previous Next 21 Replies Latest reply: May 10, 2013 4:55 PM by rp0428 Go to original post RSS
      • 15. Re: Can't execute a Grant with Dynamic SQL?
        jack m
        ok.This is the actual code. Don't get confused with vRole as this has nothing to do with oracle's roles...


        88: FOR rec_object IN all_objects LOOP
        89: vStatement_Command := 'GRANT INSERT ON ICHIPSYS_' || vEnvironment || '.' || rec_object.Name || ' TO "ICHIP_' || vEnvironment || '-' || vRole || '"';
        90: DBMS_OUTPUT.PUT_LINE (vStatement_Command);
        91: EXECUTE IMMEDIATE vStatement_Command;
        92: END LOOP;

        DBMS_OUPUT is:
        GRANT INSERT ON ICHIPSYS_DEV.CH_DEL_ADDRESS TO "ICHIP_DEV-SADMIN"
        • 16. Re: Can't execute a Grant with Dynamic SQL?
          jack m
          ok. Changed to varchar2 for now... legacy code...

          anyways, how come it works for you? I don't understand...

          I need to study this a little more, I guess.

          Thx for your help. At least I know it is not a waste of time and that there is light at the end of the tunnel.

          Much appreciated.
          • 17. Re: Can't execute a Grant with Dynamic SQL?
            rp0428
            >
            I need to study this a little more, I guess.
            >
            Yes you will.

            We can't help you when you post code like this:
            grant select, insert, delete, update on mytable to user_Dba with grant option; 
            But the code you are using using different objects altogether
            GRANT INSERT ON ICHIPSYS_DEV.CH_DEL_ADDRESS TO "ICHIP_DEV-SADMIN"
            We have no way to know what user is actually executing the procedure, what user owns the table, what grants have actually been made or how those grants were made.
            • 18. Re: Can't execute a Grant with Dynamic SQL?
              jack m
              Here it comes:

              Users - ICHIPSYS_DEV - This is the owner of the table CH_DEL_ADDRESS, which we want to assing privileges to
              ICHIPSYS_PRIVILEGE - Owner of the Privilege Schema, where the stored proc is (and the rest of the privilege system)
              ADMINDB - DBA - the user executing the stored proc
              ICHIP_DEV-SADMIN - taget user that we want to assign INSERT privileges on CH_DEL_ADDRESS

              I can't make it work. All the direct privileges are there. Not even the owner can assign privileges


              TEST1

              As ICHIPSYS_DEV:
              grant select, insert, delete, update on ichipsys_dev.ch_del_address to admindb with grant option;

              -- CHECK THAT PRIVILEGES ARE REALLY THERE
              SET LINESIZE 255
              SELECT * FROM SYS.DBA_TAB_PRIVS
              WHERE GRANTEE LIKE '%ADMINDB%'
              AND OWNER = 'ICHIPSYS_DEV'
              ORDER BY 1,2,3;

              GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
              ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --------- ---------
              ADMINDB ICHIPSYS_DEV CH_DEL_ADDRESS ICHIPSYS_DEV DELETE YES NO
              ADMINDB ICHIPSYS_DEV CH_DEL_ADDRESS ICHIPSYS_DEV UPDATE YES NO
              ADMINDB ICHIPSYS_DEV CH_DEL_ADDRESS ICHIPSYS_DEV SELECT YES NO
              ADMINDB ICHIPSYS_DEV CH_DEL_ADDRESS ICHIPSYS_DEV INSERT YES NO

              4 rows selected.


              AS ICHIPSYS_PRIVILEGE
              CREATE OR REPLACE PROCEDURE Add_Audit AS

              vStatement_Command VARCHAR2(4000); -- more than 4000 characters on the insert statement

              BEGIN
              vStatement_Command := 'GRANT INSERT ON ICHIPSYS_DEV.CH_DEL_ADDRESS TO "ICHIP_DEV-SADMIN"';
              DBMS_OUTPUT.PUT_LINE (vStatement_Command);
              EXECUTE IMMEDIATE vStatement_Command;
              END Add_Audit;
              /


              AS ADMINDB
              exec ichipsys_privilege.Add_Audit;

              GRANT INSERT ON ICHIPSYS_DEV.CH_DEL_ADDRESS TO "ICHIP_DEV-SADMIN"

              BEGIN ichipsys_privilege.Add_Audit; END;
              Error at line 1
              ORA-00942: table or view does not exist
              ORA-06512: at "ICHIPSYS_PRIVILEGE.ADD_AUDIT", line 8
              ORA-06512: at line 1

              Script Terminated on line 1.

              TEST 2
              But if I directly do
              GRANT INSERT ON ICHIPSYS_DEV.CH_DEL_ADDRESS TO "ICHIP_DEV-SADMIN"
              Grant complete.

              TEST 3

              AS ADMINDB
              grant execute on ichipsys_privilege.Add_Audit to ichipsys_dev;

              AS ICHIPSYS_DEV
              exec ichipsys_privilege.Add_Audit;
              GRANT INSERT ON ICHIPSYS_DEV.CH_DEL_ADDRESS TO "ICHIP_DEV-SADMIN"
              BEGIN ichipsys_privilege.Add_Audit; END;
              Error at line 1
              ORA-00942: table or view does not exist
              ORA-06512: at "ICHIPSYS_PRIVILEGE.ADD_AUDIT", line 8
              ORA-06512: at line 1
              • 19. Re: Can't execute a Grant with Dynamic SQL?
                jack m
                Just found out that if I install the stored proc as admindb it works...

                I'm completely puzzled...

                So the user executing the stored proc needs to be it's owner? Why is that?
                • 20. Re: Can't execute a Grant with Dynamic SQL?
                  rp0428
                  >
                  So the user executing the stored proc needs to be it's owner? Why is that?
                  >
                  No - that isn't true.

                  As I said above you can create the proc/function using AUTHID CURRENT_USER and it will execute with the privileges of the user that calls it.
                  For those procedures/functions roles are enabled and work as you would expect. The default is AUTHID DEFINER which is probably what you are using.

                  See the 'invoker_rights_clause' in the doc
                  http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/create_procedure.htm#i2065815
                  >
                  AUTHID CURRENT_USER

                  Specify CURRENT_USER to indicate that the procedure executes with the privileges of CURRENT_USER. This clause creates an invoker's rights procedure.

                  This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of CURRENT_USER. External names in all other statements resolve in the schema in which the procedure resides.

                  AUTHID DEFINER

                  Specify DEFINER to indicate that the procedure executes with the privileges of the owner of the schema in which the procedure resides, and that external names resolve in the schema where the procedure resides. This is the default and creates a definer's rights procedure.
                  • 21. Re: Can't execute a Grant with Dynamic SQL?
                    jack m
                    ok. Thanks again.

                    It's half working at least. Just need to get the SP to the right schema.

                    I'll check AUTHID CURRENT_USER

                    Many thanks for sharing your knowledge. Much appreciated.
                    1 2 Previous Next