Home » SQL & PL/SQL » SQL & PL/SQL » grant select privelege (10g)
grant select privelege [message #418298] Thu, 13 August 2009 23:45 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

Is there any specific privelege to grant a user to take a look at a particular package/procedure/function in oracle
Re: grant select privelege [message #418299 is a reply to message #418298] Thu, 13 August 2009 23:51 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>Is there any specific privelege to grant a user to take a look at a particular package/procedure/function in oracle

A user can always look at a his package/procedure/function in oracle
Re: grant select privelege [message #418300 is a reply to message #418299] Thu, 13 August 2009 23:53 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Lets say the object (package) was created by userA.

Can userA grant some sort of select privelege to userB to view the code?
Re: grant select privelege [message #418301 is a reply to message #418300] Thu, 13 August 2009 23:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
He can see the package specification source in all_source.
He can't see the package body.

Regards
Michel
Re: grant select privelege [message #418302 is a reply to message #418298] Thu, 13 August 2009 23:57 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>Can userA grant some sort of select privelege to userB to view the code?

My typical solution is for userA to own a procedure that can display procedure code (of specific procedures) and the GRANT EXECUTE on the procedure to userB.
Re: grant select privelege [message #418307 is a reply to message #418302] Fri, 14 August 2009 00:47 Go to previous messageGo to next message
bishtoo
Messages: 20
Registered: August 2009
Junior Member
using all_source once can see source code of objects of other's schema also, provided select privilege is given to you.
regarding other privileges, User A can give Execute privilege to user B for excution of procedure owned by him.
Re: grant select privelege [message #418320 is a reply to message #418307] Fri, 14 August 2009 03:14 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
thanks guys, both approaches rocks, i opt to go for the all_source table

select decode( type||'-'||to_char(line,'fm99999'),
               'PACKAGE BODY-1', '/'||chr(10),
                null) ||
       decode(line,1,'create or replace ', '' ) ||
       text text
  from dba_source
 where name = upper('&&1')
Re: grant select privelege [message #418322 is a reply to message #418320] Fri, 14 August 2009 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't query all_source but dba_source.
A user should not have privilege on dba_source (he then can see ALL source).
In all_source you have not the source of package body you don't own.

Regards
Michel
Re: grant select privelege [message #418352 is a reply to message #418322] Fri, 14 August 2009 05:44 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
thanks michel,
i will take note of the points below
Re: grant select privelege [message #418415 is a reply to message #418298] Fri, 14 August 2009 21:24 Go to previous message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Julia, is everythin ok, are u advertising something or thanking because you have learned somethin from this post
Previous Topic: SQL Generation
Next Topic: Difference between the Following
Goto Forum:
  


Current Time: Mon Dec 05 07:05:16 CST 2016

Total time taken to generate the page: 0.05986 seconds