Message-ID: <46142.12.17.117.251.1197040392.squirrel@12.17.117.251>
In-Reply-To: <083667B535F3464CA0DD0D1DAFA4E3760F2287AA@camexc1.kfs.local>
References: <51195.12.17.117.251.1196975360.squirrel@12.17.117.251>
    <083667B535F3464CA0DD0D1DAFA4E3760F2287AA@camexc1.kfs.local>
Date: Fri, 7 Dec 2007 09:13:12 -0600 (CST)
Subject: RE: How to purge dba_recyclebin from a procedure?
From: "Rich Jesse" <rjoralist@society.servebeer.com>
To: oracle-l@freelists.org
User-Agent: SquirrelMail/1.4.3a
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: rjoralist@society.servebeer.com

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