RE: dbms_metadata.get_ddl Error
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <ebadi01@xxxxxxxxx>, <mdemenko@xxxxxxxxx>
- Date: Mon, 15 Oct 2007 15:31:17 -0400
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@xxxxxxxxxxx
x72546
904 727-2546
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of A Ebadi
Sent: Monday, October 15, 2007 2:53 PM
To: mdemenko@xxxxxxxxx
Cc: Ghassan Salem; oracle-l@xxxxxxxxxxxxx
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@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_me
tada.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.
<http://us.rd.yahoo.com/evt=48251/*http:/smallbusiness.yahoo.com/webhost
ing/?p=PASSPORTPLUS>
Other related posts:
