12

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.

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
Basmah
  • 829
  • 3
  • 13
  • 25

5 Answers5

39

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( <<list of parameters>> );
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( <<list of parameters>> );
END;
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
8

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;
DCookie
  • 42,630
  • 11
  • 83
  • 92
  • 1
    +1 A less drastic approach than CREATE ANY PROCEDURE, but also with pitfalls would be to give the application using user B the password to user A allowing them to login as that user. It sounds like you would like to grant schema level privileges, but those aren't available in Oracle. – Leigh Riffel Nov 29 '10 at 19:06
  • Okay Sir, plz tell me how to Grant Execution Rights to UserA to run stored procedure of user B. – Basmah Nov 29 '10 at 21:48
1

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.

Philip Johnson
  • 1,091
  • 10
  • 24
1

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.

JustinC
  • 21
  • 6
0
SQL> grant create any procedure to testdb;

This is a command when we want to give create privilege to "testdb" user.

Nathan Tuggy
  • 2,237
  • 27
  • 30
  • 38
Raj Sharma
  • 141
  • 1
  • 4