OraFAQ Forum: SQL & PL/SQL » Accessing DBA% tables in a SP

Home » SQL & PL/SQL » SQL & PL/SQL » Accessing DBA% tables in a SP

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

Accessing DBA% tables in a SP [message #276204]

Wed, 24 October 2007 06:34

khresmoi
Messages: 24
Registered: October 2007

Junior Member

Do I need spacial permission to access DBA tables? i'm not able to compile an SP which selects a record from DBA_TAB_PARTITIONS. Error encountered is - PL/SQL: ORA-00942: table or view does not exist.
what I've is a DEV_ROLE. and no privileges.

help!

TIA..

Report message to a moderator

Re: Accessing DBA% tables in a SP [message #276205 is a reply to message #276204]

Wed, 24 October 2007 06:37

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Grants received via a role don't work in procedures and/or packages.

The user itself must have the necessary grants.

Report message to a moderator

Re: Accessing DBA% tables in a SP [message #276206 is a reply to message #276204]

Wed, 24 October 2007 06:38

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Roles are not enabled in SP.
Roles are not enabled in SP.
Roles are not enabled in SP.
Roles are not enabled in SP.
Roles are not enabled in SP.
Roles are not enabled in SP.
Roles are not enabled in SP.
Roles are not enabled in SP.
Roles are not enabled in SP.
Roles are not enabled in SP.
Roles are not enabled in SP.
Roles are not enabled in SP.
Roles are not enabled in SP.

Regards
Michel

Report message to a moderator

Re: Accessing DBA% tables in a SP [message #276207 is a reply to message #276204]

Wed, 24 October 2007 06:38

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Then you're out of luck: roles have no meaning for PL/SQL procedures. You need a direct grant.

Have a look here.

MHE

Report message to a moderator

Re: Accessing DBA% tables in a SP [message #276209 is a reply to message #276204]

Wed, 24 October 2007 06:42

khresmoi
Messages: 24
Registered: October 2007

Junior Member

Lemme add some more info..
i'm able to create and execute SPs in my schema. but this one throws an error at the line of code which uses DBA_TAB_PARTITIONS table in a join. this is the problem.

Which grant/privilege will I need to overcome this?

Report message to a moderator

Re: Accessing DBA% tables in a SP [message #276214 is a reply to message #276209]

Wed, 24 October 2007 06:51

dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai

Senior Member

hi,

you need to have select privs on objects which you don't own.

regards,

[Updated on: Wed, 24 October 2007 06:52]

Report message to a moderator

Re: Accessing DBA% tables in a SP [message #276217 is a reply to message #276204]

Wed, 24 October 2007 06:56

khresmoi
Messages: 24
Registered: October 2007

Junior Member

BUt i'm able to do a select on this table from the command prompt..

SELECT COUNT(*) FROM DBA_TAB_PARTITIONS
COUNT(*)
----------
1398

So i assumedn that i've read access of system tables. but thru SP i'm not able to. Sad

Report message to a moderator

Re: Accessing DBA% tables in a SP [message #276218 is a reply to message #276217]

Wed, 24 October 2007 07:00

Maaher
Messages: 7065
Registered: December 2001

Senior Member

That's what we keep on telling you: you need a direct grant. Have you read the link I posted?

MHE

Report message to a moderator

Re: Accessing DBA% tables in a SP [message #276271 is a reply to message #276218]

Wed, 24 October 2007 10:29

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Have you read the link I posted?

What does read mean? What is a link? Sad

Regards
Michel

Report message to a moderator

Re: Accessing DBA% tables in a SP [message #276276 is a reply to message #276204]

Wed, 24 October 2007 10:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>BUt i'm able to do a select on this table from the command prompt..
>SELECT COUNT(*) FROM DBA_TAB_PARTITIONS
Before doing the SELECT do the follwoing:
SQL> SET ROLE NONE
-- what does SELECT return now?
SQL>SELECT COUNT(*) FROM DBA_TAB_PARTITIONS;

[Updated on: Wed, 24 October 2007 10:41] by Moderator

Report message to a moderator

Re: Accessing DBA% tables in a SP [message #276324 is a reply to message #276204]

Wed, 24 October 2007 20:16

ebrian
Messages: 2794
Registered: April 2006

Senior Member

If you have the privileges granted to you thru a role, then you may consider creating the procedure with invokers rights.

Report message to a moderator

Re: Accessing DBA% tables in a SP [message #276380 is a reply to message #276324]

Thu, 25 October 2007 01:17

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Good point, Brian. I didn't bring it up (yet) because I think that the OP first has to understand where the origin of his problem lays before we go into "advanced mode". Very Happy