7 Replies Latest reply: Jun 9, 2011 8:47 AM by wolfeet RSS

    grant execute on stored procedure

    wolfeet
      I'm trying to allow the "help desk" people to connect via a special database account and execute a procedure to reset or unlock a users's password or account. I've created two stored procedures as user1 and have granted EXECUTE on user1.unlockaccount. Within the procedure I have an IF statement that basically says if the username is IN the following ('SYS','SYSTEM', ect.) then dbms_output.put_line ('not_allowed'). The ELSE is 'execute immediate 'alter user '||pUserName||' identified by '||pPassWord||' account unlock';
      Then an exception when others statement to output the 'error: '||sqlerrm
      The procedure compiles fine and I can execute it without error as 'user1' but when I GRANT EXECUTE ON USER1.UNLOCKACCOUNT TO USER2 and try to 'execute user1.unlockaccout ('username'); I get insufficient privileges.
      I've also switched to SYS and "GRANT EXECUTE ON USER1.UNLOCKACCOUNT TO USER2;" but I still get the same insufficient privs error. If I don't 'set serveroutput on;' I don't see the error message, but it still fails.

      Actually, one correction. I'm getting insufficient privs as user1 as well. user1 can unlock a user's account and reset a password outside of the procedure but I'm getting insufficient privs when executing the procedure.

      Edited by: wolfeet on Jun 8, 2011 8:49 PM
        • 1. Re: grant execute on stored procedure
          Solomon Yakobson
          PL/SQL 101: stored objects with definer rights (default) ignore roles. Based on "user1 can unlock a user's account and reset a password outside of the procedure but I'm getting insufficient privs when executing the procedure", user1 is granted ALTER USER privilege not directly but via role. You must grant ALTER USER privilege to user1 directly.

          SY.
          • 2. Re: grant execute on stored procedure
            Peter Gjelstrup
            If I don't 'set serveroutput on;' I don't see the error message, but it still fails.
            There you got it. Number one reason for not implemementing an exception handler, that does nothing.

            So remove your WHEN OTHERS

            Also, I suggest you remove your "Not allowed" output and replace it with a RAISE_APPLICAITON_ERROR.

            Just a side note.


            Regards
            Peter
            • 3. Re: grant execute on stored procedure
              wolfeet
              Granting ALTER USER directly to the user defeats the whole purpose. I want to allow this user to reset user passwords and unlock user passwords but I don't want the user to be able to reset passwords for dba accounts, sys, system, yada yada. I was hoping I could do this via GRANT EXECUTE on a stored procedure.
              • 4. Re: grant execute on stored procedure
                William Robertson
                The owner of the procedure must have direct privileges to do whatever the procedure does. It should therefore be owned by a privileged owner (who can reset any password) and contain the conditional logic so users executing it can't do too much harm.

                btw DBMS_OUTPUT is a debug tool, it's not really something you should rely on in production code. Use RAISE_APPLICATION_ERROR, and don't concatenate SQLERRM, set the third parameter to TRUE.
                • 5. Re: grant execute on stored procedure
                  6363
                  wolfeet wrote:

                  Then an exception when others statement to output the 'error: '||sqlerrm
                  http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html
                  • 6. Re: grant execute on stored procedure
                    Frank Kulash
                    Hi,
                    wolfeet wrote:
                    Granting ALTER USER directly to the user defeats the whole purpose. I want to allow this user to reset user passwords and unlock user passwords but I don't want the user to be able to reset passwords for dba accounts, sys, system, yada yada. I was hoping I could do this via GRANT EXECUTE on a stored procedure.
                    Solomon said to grant ALTER USER directly to user1, the owner of the procedure, who already has this privilege via a role.
                    User2, who will run the procedure, needs only EXECUTE privileges on the procedures. User2 will not need the ALTER USER privilege.
                    • 7. Re: grant execute on stored procedure
                      wolfeet
                      Thanks all.
                      I had not granted alter user explicitly to the owner of the procedure. Once I did that, things started working. I also used raise_application_error for the message that this is not permitted. Thanks for your help, much appreciated.