Home » SQL & PL/SQL » SQL & PL/SQL » Accessing DBA% tables in a SP
icon9.gif  Accessing DBA% tables in a SP [message #276204] Wed, 24 October 2007 06:34 Go to next message
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..


Re: Accessing DBA% tables in a SP [message #276205 is a reply to message #276204] Wed, 24 October 2007 06:37 Go to previous messageGo to next message
ThomasG
Messages: 3189
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.
Re: Accessing DBA% tables in a SP [message #276206 is a reply to message #276204] Wed, 24 October 2007 06:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
Re: Accessing DBA% tables in a SP [message #276207 is a reply to message #276204] Wed, 24 October 2007 06:38 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: Accessing DBA% tables in a SP [message #276209 is a reply to message #276204] Wed, 24 October 2007 06:42 Go to previous messageGo to next message
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?
Re: Accessing DBA% tables in a SP [message #276214 is a reply to message #276209] Wed, 24 October 2007 06:51 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Accessing DBA% tables in a SP [message #276218 is a reply to message #276217] Wed, 24 October 2007 07:00 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: Accessing DBA% tables in a SP [message #276271 is a reply to message #276218] Wed, 24 October 2007 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
Re: Accessing DBA% tables in a SP [message #276276 is a reply to message #276204] Wed, 24 October 2007 10:40 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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 Go to previous messageGo to next message
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.
Re: Accessing DBA% tables in a SP [message #276380 is a reply to message #276324] Thu, 25 October 2007 01:17 Go to previous messageGo to next message
Maaher
Messages: 7062
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

khresmoi,
if granting privileges directly is a problem, you should look into the advice of ebrian.

MHE

[Updated on: Thu, 25 October 2007 01:18]

Report message to a moderator

Re: Accessing DBA% tables in a SP [message #276484 is a reply to message #276380] Thu, 25 October 2007 04:59 Go to previous messageGo to next message
khresmoi
Messages: 24
Registered: October 2007
Junior Member
I dont think getting the grants will be problem.
Thanks guys!
Re: Accessing DBA% tables in a SP [message #276505 is a reply to message #276484] Thu, 25 October 2007 06:25 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you think is the problem?

Regards
Michel
Previous Topic: avoiding null results
Next Topic: Export Query Result to text file
Goto Forum:
  


Current Time: Thu Dec 08 23:54:10 CST 2016

Total time taken to generate the page: 0.14693 seconds