RE: dbms_metadata.get

From: <Joel.Patterson_at_crowley.com>
Date: Mon, 15 Oct 2007 15:31:17 -0400
Message-ID: <0684DA55864E404F8AD2E2EBDFD557DA28C427@JAXMSG01.crowley.com>

I did not look at this because I just want to ask first if you have granted execute on dbms_metadata to the <user>... explicitly? Actually typed it out.

Maybe I was just browsing and missed why this wouldn't work. Apologies in advance.

Joel Patterson
Database Administrator
joel.patterson_at_crowley.com
x72546
904 727-2546

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of A Ebadi Sent: Monday, October 15, 2007 2:53 PM
To: mdemenko_at_gmail.com
Cc: Ghassan Salem; oracle-l_at_freelists.org Subject: Re: dbms_metadata.get_ddl Error

Cannot execute as sysdba or owner as the procedure is generic - i.e. want to be able to drop any index in the DB via this procedure, but don't want to have to give sysdba privs to everyone executing this procedure!

Tried to give select_catalog_role with authid current_user, but doesn't work as need lots of privs for the executing user like drop any index, select any table, etc.

What we are looking for is a simple procedure that allows users to drop an index, but saves the index re-create ddl before dropping it.

Any other suggestions?

Thanks!

Maxim Demenko <mdemenko_at_gmail.com> wrote:

	A Ebadi schrieb:


	> Here is the exact call and the user calling this procedure has

DBA as 


	> well as the procedure owner:

	> 

	> select

dbms_metadata.get_ddl('INDEX','MY_TAB_IDX5','OPS$ORACLE') into 


	> v_index_info from dual;

	> 

	> The ironic thing is a similar select works just fine from

SQLPLUS, but 


	> inside the procedure it fails with ORA-31603.

	> 

	> Thanks,

	> Abdul

	>

	I believe, it is mentioned somewhere in metalink,
	however, you could read this reference:
	http://sql.ru/forum/actualthread.aspx?tid=354978&hl=ora+31603 (
it is in 
	russian, but you can try to translate it via google or similar
web service),
	shortly, the issue is caused by this snippet in all
sys.ku$_%_view for 
	relevant objects :
	
	AND (SYS_CONTEXT('USERENV','CURRENT_USERID') IN (o.owner_num, 0)
OR
	EXISTS ( SELECT * FROM session_roles
	WHERE role='SELECT_CATALOG_ROLE' ))
	
	So,you have basically following choices
	1) execute it as owner
	2) execute it as sysdba
	3) have SELECT_CATALOG_ROLE and procedure with authid

current_user

Briefly it mentioned also here
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_me tada.htm#sthref4208

Best regards

Maxim

	--
	http://www.freelists.org/webpage/oracle-l
	
	

 

  

________________________________

Building a website is a piece of cake.
Yahoo! Small Business gives you all the tools to get online. <http://us.rd.yahoo.com/evt=48251/*http:/smallbusiness.yahoo.com/webhost ing/?p=PASSPORTPLUS>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Oct 15 2007 - 14:31:17 CDT

RE: dbms_metadata.get_ddl Error