Re: dbms_metadata.get_ddl Error

create the proc as authid definer in the schema owner, then grant
execute to the desired user on the procedure?
Stefan

On 10/15/07, A Ebadi <ebadi01@xxxxxxxxx> wrote:
> 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
>
>
> --
> 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.


-- 
=========================

Stefan P Knecht
Consultant
Infrastructure Managed Services

Trivadis AG
Europa-Strasse 5
CH-8152 Glattbrugg

Phone +41-44-808 70 20
Fax +41-808 70 12
Mobile +41-79-571 36 27
stefan.knecht@xxxxxxxxxxxx
http://www.trivadis.com

OCP SCSA SCNA
=========================
--
http://www.freelists.org/webpage/oracle-l


Other related posts: