Home » SQL & PL/SQL » SQL & PL/SQL » Granting ownership on any object to other user
Granting ownership on any object to other user [message #223651] Sat, 10 March 2007 01:36 Go to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Hello All,

I got a small doubt...

Let say there are 2 users scott1 and scott2.
The user scott1 has created a procedure with name say 'proc1' which is accesiing tables table1 and table2 which are again under the ownership of user scott1.

So can the user scott1 grant the ownership on procedure 'proc1' to the other user 'scott2'?

Also Is there any way that we can make both the users share the ownership of the procedure 'proc1'? something like both the users can access the procedure 'proc1' using 'scott1.proc1' and 'scott2.proc1'?

Please let me know it?
I need the answer for it because I am left with a dilemma in my project as I have to implement that scenario.

Thanks in advance.

prashas_d.


Re: Granting ownership on any object to other user [message #223652 is a reply to message #223651] Sat, 10 March 2007 01:50 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


you can grant execute privl. on the procedure to the other user. in you case its scott2.

so it could be something like :

grant execute on procedure_name to scott2

and then scott2 can execute this as

exec scott1.procedure_name(parm1....)



regards,
Re: Granting ownership on any object to other user [message #223655 is a reply to message #223651] Sat, 10 March 2007 02:27 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
So can the user scott1 grant the ownership on procedure 'proc1' to the other user 'scott2'?

Also Is there any way that we can make both the users share the ownership of the procedure 'proc1'? something like both the users can access the procedure 'proc1' using 'scott1.proc1' and 'scott2.proc1'?


1. I do not know, what you mean by ownership, however scott1 may grant EXECUTE privilege on proc1 to scott2.
2. You may achieve this by creating a synonym proc1 in scott2 for scott1.proc1.
Re: Granting ownership on any object to other user [message #223669 is a reply to message #223655] Sat, 10 March 2007 03:54 Go to previous messageGo to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Flyboy,

Thanks for responding to my query.

1)What I mean by Ownership is: if the user scott1 grants the ownership to user scott2, then user scott2 can not only execute that procedure, scott2 can also grant permissions on this object to different user say scott3. scott2 can also drop the procedure proc1. I mean the complete rights on that object.

2)If the user scott1 create public synonym to proc1 as

create public synonym proc1 for scott1.proc1;

Then the user scott2 can directly access that object by using 'call proc1(<Arg>)' instead of 'call scott1.proc1(<Arg>)' in sqlplus.

But I think this would not acheive me the results what I expected.

Probably I am using the wrong terminoligy by using the term 'ownership'.

So Is there any way to acheive what I need?

Thanks,
prashas_d.
Re: Granting ownership on any object to other user [message #223674 is a reply to message #223669] Sat, 10 March 2007 04:10 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


for scott2 to grant the privl. to another user scott3 he has to have WITH GRANT OPTION granted by user scott1.and for the second part ie user scott2 to drop the procedure he needs to have DROP ANY PROCEDURE privl.refer the doc. for more information.


regards,
Re: Granting ownership on any object to other user [message #223681 is a reply to message #223669] Sat, 10 March 2007 05:01 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Follow the links I gave you, everything is there
1. see the section WITH GRANT OPTION to allow grant to other user, try grant ALL (lazy to search what everything is covered under it).
however if you want to DROP proc1 as scott2, seems to me like very very bad design.
2. if you would have issued (as I recommended you) as user scott2
CREATE SYNONYM proc1 FOR scott1.proc1;
you could access the proc1 as scott2.proc1.
Previous Topic: Creating Procedures
Next Topic: Peformance when using rowlevel trigger and column trigger
Goto Forum:
  


Current Time: Thu Dec 08 23:55:07 CST 2016

Total time taken to generate the page: 0.19863 seconds