Re: dbms_metadata.get_ddl Error

  • From: A Ebadi <ebadi01@xxxxxxxxx>
  • To: mdemenko@xxxxxxxxx
  • Date: Mon, 15 Oct 2007 11:53:28 -0700 (PDT)

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@xxxxxxxxx> 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_metada.htm#sthref4208

Best regards

Maxim


--
//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.

Other related posts: