RE: How to purge dba_recyclebin from a procedure?

Right, this is demonstrating what I had said. But does anyone else see the inconsistency here? It's as though the procedure is using a combination of definer and invoker rights, since the caller of the procedure must have SYSDBA and not the owner of the procedure itself. Perhaps it's just semantics, given the special nature of SYSDBA, which I'm beginning to despise as being a requirement for such a trivial statement.

Also, since this is a 10gR1 DB, I don't think I'll be setting the hidden parameter, but thanks for reminding me that it's there.

Rich

> Because purging dba_recyclebin is only permitted with the SYSDBA
> privilege you are not able to do it even if you create the procedure in
> SYS and grant execute privilege on it to another user. Even if you grant
> SYSDBA, that user will not be able to run it unless connected as SYSDBA.
>
> You can use the "recyclebin" init parameter in 10G R2 to turn recyclebin
> off completely if you want that (in 10G R1 it is a hidden parameter).
>
> SQL> CREATE OR REPLACE PROCEDURE purge_dba_recycle_bin AS
> 2 v_statement VARCHAR2(100) := 'PURGE
> DBA_RECYCLEBIN';
> 3 BEGIN
> 4 EXECUTE IMMEDIATE v_statement;
> 5 END;
> 6 /
>
> Procedure created.
>
> SQL> grant execute on purge_dba_recycle_bin to test;
>
> Grant succeeded.
>
> SQL> grant sysdba to test;
>
> Grant succeeded.
>
> SQL> conn test
> Enter password:
> Connected.
> SQL> exec sys.purge_dba_recycle_bin;
> BEGIN sys.purge_dba_recycle_bin; END;
>
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
> ORA-06512: at "SYS.PURGE_DBA_RECYCLE_BIN", line 4
> ORA-06512: at line 1
>
>
> SQL> conn test as sysdba
> Enter password:
> Connected.
> SQL> exec sys.purge_dba_recycle_bin;
>
> PL/SQL procedure successfully completed.

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