Re: dbms_metadata.get_ddl Error

Use the same basic principle. See below, creating user FOO (procedure owner), that has the necessary privileges. Creating user FOOEXEC that has execute rights on the procedure in question.

Note, this won't work for SYS owned-objects, but all the others in a "normal" database installation (i.e. not using DB VAult or some other fancy stuff).
You could work around this by having the procedure in SYS schema, but I wouldn't recommend this. SYS shouldn't be touched anyway.

sys_at_CENTRAL> conn system/forget
Connected.
system_at_CENTRAL> create table t (x int);

Table created.

system_at_CENTRAL> create index i on t (x);

Index created.

system_at_CENTRAL>
system_at_CENTRAL> create user foo identified by bar   2 /

User created.

system_at_CENTRAL>
system_at_CENTRAL> grant create session, alter any index, drop any index, create procedure
  2 to foo
  3 /

Grant succeeded.

system_at_CENTRAL>
system_at_CENTRAL> conn foo/bar
Connected.
foo_at_CENTRAL> create procedure drop_idx (owner in varchar2,index_name in varchar2)
  2 authid definer
  3 as
  4 begin
  5 execute immediate 'drop index ' || owner || '.' || index_name;   6 end;
  7 /

Procedure created.

foo_at_CENTRAL>
foo_at_CENTRAL> create procedure alter_idx (owner in varchar2, index_name in varchar2)
  2 authid definer
  3 as
  4 begin
  5 execute immediate 'alter index ' || owner || '.' || index_name || ' rebuild';
  6 end;
  7 /

Procedure created.

foo_at_CENTRAL>
foo_at_CENTRAL> conn / as sysdba
Connected.
sys_at_CENTRAL>
sys_at_CENTRAL> revoke create session, create procedure from foo;

Revoke succeeded.

sys_at_CENTRAL>
sys_at_CENTRAL> create user fooexec identified by bar;

User created.

sys_at_CENTRAL> grant create session to fooexec;

Grant succeeded.

sys_at_CENTRAL> grant execute on foo.drop_idx to fooexec;

Grant succeeded.

sys_at_CENTRAL> grant execute on foo.alter_idx to fooexec;

Grant succeeded.

sys_at_CENTRAL>
sys_at_CENTRAL> conn fooexec/bar
Connected.
fooexec_at_CENTRAL> execute foo.alter_idx ('SYSTEM','I');

PL/SQL procedure successfully completed.

fooexec_at_CENTRAL> execute foo.drop_idx ('SYSTEM','I');

PL/SQL procedure successfully completed.

fooexec_at_CENTRAL>
fooexec_at_CENTRAL> conn system/forget
Connected.
system_at_CENTRAL> select index_name from dba_indexes where owner='SYSTEM' and table_name='T';

no rows selected

Stefan

On 10/15/07, A Ebadi <ebadi01_at_yahoo.com> wrote:
>
> Even running the procedure as SYSDBA or granting user SELECT_CATALOG_ROLE
> won't work - same error.
>
> *Jared Still <jkstill_at_gmail.com>* wrote:
>
>
> On 10/15/07, A Ebadi <ebadi01_at_yahoo.com> wrote:
> >
> >
> > 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.
> >
>
> If using the DBA role didn't work, granting SELECT_CATALOG_ROLE isn't
> going to help.
>
> 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.
> >
>
> Just tested you exact scenario.
>
> ORA-31603 will occur unless the user running the procedure has explicitly
> granted
> SELECT on the table in question.
>
> Not sure why Oracle support has not gotten back to you.
> It takes < 5 minutes to setup a user and test this.
>
>
> Any other suggestions?
> >
>
> Yes, use Perl.
>
> Specifically, Perl with the Oracle::DDL module.
>
> Or just use dbms_metadata from a SQL script, and generate and run the SQL.
>
> Or run the procedure as sysdba.
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>
>
> ------------------------------
> Tonight's top picks. What will you watch tonight? Preview the hottest
> shows<http://us.rd.yahoo.com/tv/mail/tagline/tonightspicks/evt=48220/*http://tv.yahoo.com/+%0A>on Yahoo! TV.
>
>

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

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_at_trivadis.com
http://www.trivadis.com

OCP SCSA SCNA
=========================

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