Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL In Stored Procedures
Dynamic SQL In Stored Procedures [message #241978] Thu, 31 May 2007 05:02 Go to next message
murali_gvn
Messages: 2
Registered: May 2007
Junior Member
Hi Expert,

I faced a strange experience.

I have two schema in my database SchemaA and SchemaB. I am currently working on SchemaA. I wrote a stored procedure in SchemaA to access a table of SchemaB. When I tried to execute my Stored Procedure, I am getting an error "Insufficient Privileges".

I tried with the same code in a PL/SQL block, and I am able to get the details of the table of SchemaB.

I found in some of the websites that this can be achieved by creating Public Synonyms and creating a reference to the tables that I need. Of-course, I could arrive at the solution, but, I think this will be a burden for Database Server.

Can anyone provide me a feasible method to solve my problem?

Thanks,
Muralidhar.
Re: Dynamic SQL In Stored Procedures [message #241980 is a reply to message #241978] Thu, 31 May 2007 05:07 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
It's because your access is granted via a role. You cannot created objects (e.g. views, stored procs) based on something if your access comes through a role. You will need to be granted access to the objects explicitly using "grant ... on xx to user".

[Updated on: Thu, 31 May 2007 05:07]

Report message to a moderator

Re: Dynamic SQL In Stored Procedures [message #241982 is a reply to message #241978] Thu, 31 May 2007 05:12 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
It may also make a difference whether you are creating the procedure using "authid definer" or "authid current_user". If current_user, you would only have your own access rights to tables in the other user's schema, which may not be enough. If "authid definer", you would get the other user's access rights and it may work better.
Previous Topic: SQL SELECT DOUBT ORA 9i
Next Topic: Selecting duplicate values
Goto Forum:
  


Current Time: Fri Dec 09 06:18:05 CST 2016

Total time taken to generate the page: 0.08096 seconds