OraFAQ Forum: SQL & PL/SQL » grant select privelege

Home » SQL & PL/SQL » SQL & PL/SQL » grant select privelege (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

grant select privelege [message #418298]

Thu, 13 August 2009 23:45

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

Report message to a moderator

Re: grant select privelege [message #418299 is a reply to message #418298]

Thu, 13 August 2009 23:51

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: grant select privelege [message #418300 is a reply to message #418299]

Thu, 13 August 2009 23:53

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?

Report message to a moderator

Re: grant select privelege [message #418301 is a reply to message #418300]

Thu, 13 August 2009 23:56

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

He can see the package specification source in all_source.
He can't see the package body.

Regards
Michel

Report message to a moderator

Re: grant select privelege [message #418302 is a reply to message #418298]

Thu, 13 August 2009 23:57

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: grant select privelege [message #418307 is a reply to message #418302]

Fri, 14 August 2009 00:47

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.

Report message to a moderator

Re: grant select privelege [message #418320 is a reply to message #418307]

Fri, 14 August 2009 03:14

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')

Report message to a moderator

Re: grant select privelege [message #418322 is a reply to message #418320]

Fri, 14 August 2009 03:21

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator