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_at_yahoo.com> 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_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_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_at_trivadis.com
http://www.trivadis.com
OCP SCSA SCNA
=========================
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Oct 15 2007 - 15:35:59 CDT
