Home » SQL & PL/SQL » SQL & PL/SQL » Failing to access the objects in other schema thru procedures
Failing to access the objects in other schema thru procedures [message #418449] Sat, 15 August 2009 10:56 Go to next message
athene
Messages: 11
Registered: January 2009
Junior Member
Hi All,

We are creating a stored procedure (in userA) which tries to access a table in a different user (say B).The user A has select privilege on user B tables and we are able to run select queries as well.

But when trying to access the same in the procedue it throws an error PLS-00201: identifier '<USER B>.<TABLE_NAME> must be declared.

Can you please help us.It is very urgent.

Thanks in Advance.

Athene.
Re: Failing to access the objects in other schema thru procedures [message #418450 is a reply to message #418449] Sat, 15 August 2009 11:06 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

It would be most helpful if you used sqlplus along with CUT & PASTE so we can see exactly what you did & how Oracle responded.


>It is very urgent.
Please elaborate on why it is urgent for ME to solve this problem for you.


Privileges acquired via ROLE do not apply within PL/SQL procedures.
Re: Failing to access the objects in other schema thru procedures [message #418453 is a reply to message #418449] Sat, 15 August 2009 11:23 Go to previous messageGo to next message
bishtoo
Messages: 20
Registered: August 2009
Junior Member
pls try to do following:

1- check whether privileges are granted via role or explicit grant...
2- try to use invoker's rights [AUTHID CURRENT_USER]- (doing so provieges will be checked at runtime)

try and let us know your results..
Re: Failing to access the objects in other schema thru procedures [message #418877 is a reply to message #418453] Wed, 19 August 2009 00:19 Go to previous messageGo to next message
athene
Messages: 11
Registered: January 2009
Junior Member
Thank you so much!! .It is working now Smile
Re: Failing to access the objects in other schema thru procedures [message #418881 is a reply to message #418877] Wed, 19 August 2009 00:26 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Does it work in your development environment, or on a test-environment that is configured like your production environment?
If you "solved" it using AUTHID CURRENT_USER, you run (quite a big) risk that the users that will call your SP have no rights on user B's objects.
Previous Topic: to know on which platform my database is running using select statement (merged 3)
Next Topic: SQL query with parallel hint running very slow
Goto Forum:
  


Current Time: Sun Dec 04 08:57:59 CST 2016

Total time taken to generate the page: 0.07223 seconds