Granting Rights on Stored Procedure to another user of Oracle

Question

I am a student of Undergraduate studies   and I am facing little problem in granting rights of ownership to a user A to a stored procedure being owned by user B in database Oracle 10g mode  xe   Please help me in writing sql commands for granting rights of ownership on stored procedure xyz to another user A

User · Accepted Answer

I m not sure that I understand what you mean by  rights of ownership    If User B owns a stored procedure  User B can grant User A permission to run the stored procedure  GRANT EXECUTE ON b procedure name TO a   User A would then call the procedure using the fully qualified name  i e   BEGIN   b procedure name   lt  lt list of parameters gt  gt     END    Alternately  User A can create a synonym in order to avoid having to use the fully qualified procedure name   CREATE SYNONYM procedure name FOR b procedure name   BEGIN   procedure name   lt  lt list of parameters gt  gt     END

User · Answer

You can t do what I think you re asking to do   The only privileges you can grant on procedures are EXECUTE and DEBUG   If you want to allow user B to create a procedure in user A schema  then user B must have the CREATE ANY PROCEDURE privilege  ALTER ANY PROCEDURE and DROP ANY PROCEDURE are the other applicable privileges required to alter or drop user A procedures for user B  All are wide ranging privileges  as it doesn t restrict user B to any particular schema  User B should be highly trusted if granted these privileges   EDIT    As Justin mentioned  the way to give execution rights to A for a procedure owned by B   GRANT EXECUTE ON b procedure name TO a

User · Answer

On your DBA account  give USERB the right to create a procedure using grant grant create any procedure to USERB  The procedure will look   CREATE OR REPLACE PROCEDURE USERB USERB PROCEDURE --Must add the line below AUTHID CURRENT USER AS   BEGIN   --DO SOMETHING HERE   END END   GRANT EXECUTE ON USERB USERB PROCEDURE TO USERA  I know this is a very old question but I am hoping I could chip it a bit

User · Answer

SQL gt  grant create any procedure to testdb    This is a command when we want to give create privilege to  testdb  user

User · Answer

Packages and stored procedures in Oracle execute by default using the rights of the package procedure OWNER  not the currently logged on user   So if you call a package that creates a user for example  its the package owner  not the calling user that needs create user privilege  The caller just needs to have execute permission on the package   If you would prefer that the package should be run using the calling user s permissions  then when creating the package you need to specify AUTHID CURRENT USER  Oracle documentation  Invoker Rights vs Definer Rights  has more information http   docs oracle com cd A97630 01 appdev 920 a96624 08 subs htm 18575   Hope this helps

[oracle] Granting Rights on Stored Procedure to another user of Oracle

Examples related to oracle

Examples related to stored-procedures

Examples related to grant

Examples related to owner

Examples related to rights-management