Home » SQL & PL/SQL » SQL & PL/SQL » Across Schema Stored Procedure Access (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production)
Across Schema Stored Procedure Access [message #563649] Thu, 16 August 2012 05:48 Go to next message
arijit77
Messages: 6
Registered: February 2010
Location: Gurgaon
Junior Member
Hi Experts

I have two schemas.

a) Data_Schema
b) Access_Schema

I have created a Stored Procedure in Data_Schema to run DDL and given execute right to Access_Schema. I can do following activities from Access_Schema without any complain.

1. call Data_Schema.sp_LTO_DDL ('TRUNCATE TABLE LTO'); --Running Successfully
2. call Data_Schema.sp_LTO_DDL ('DROP TABLE LTO'); --Running Successfully

But it is failing for below operation.
3. call Data_Schema.SP_LTO_DDL ('CREATE TABLE LTO(VAL1 NUMBER)');

ORA-01031: insufficient privileges
ORA-06512: at "Data_Schema.SP_LTO_DDL", line 4

I am not sure what is missing at my end. Please guide me.
Thanks for your support.

Regards
Arijit
Re: Across Schema Stored Procedure Access [message #563653 is a reply to message #563649] Thu, 16 August 2012 06:20 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
Posting the code of the procedure would be a good start.
Re: Across Schema Stored Procedure Access [message #563654 is a reply to message #563649] Thu, 16 August 2012 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because Data_Schema has not CREATE TABLE granted directly to it.
In addition, we have not your code but I bet with Access_Schema I can destroy everything in Data_Schema or modify all the dara as I want, that is do all what Access_Schema can do without any restriction.

Regards
Michel
Re: Across Schema Stored Procedure Access [message #563766 is a reply to message #563654] Thu, 16 August 2012 21:33 Go to previous messageGo to next message
arijit77
Messages: 6
Registered: February 2010
Location: Gurgaon
Junior Member
Thanks for your reply..Code is straight forward, plese refer below.

create procedure SP_LTO_DDL (stat varchar2) 
as
begin
execute immediate (stat);
end;


Please guide me for my next course of actions.

Thanks again for your valuable suggestion.

Regards
Arijit
Re: Across Schema Stored Procedure Access [message #563767 is a reply to message #563766] Thu, 16 August 2012 22:37 Go to previous messageGo to next message
BlackSwan
Messages: 22525
Registered: January 2009
Senior Member
GRANT CREATE TABLE TO DATA_SCHEMA;
Re: Across Schema Stored Procedure Access [message #563776 is a reply to message #563766] Fri, 17 August 2012 01:27 Go to previous message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 16 August 2012 13:22
Because Data_Schema has not CREATE TABLE granted directly to it.
In addition, we have not your code but I bet with Access_Schema I can destroy everything in Data_Schema or modify all the dara as I want, that is do all what Access_Schema can do without any restriction.

Regards
Michel


Previous Topic: Searching for a string in the whole database (splitted from "How does DBMS_SQL differ from Native Dynamic Code")
Next Topic: Handling exceptions in bulk collect
Goto Forum:
  


Current Time: Tue Jul 29 18:58:22 CDT 2014

Total time taken to generate the page: 0.09435 seconds