RE: How to purge dba_recyclebin from a procedure?

From: Yasin Baskan <yasin.baskan_at_yapikredi.com.tr>
Date: Fri, 7 Dec 2007 12:23:29 +0200
Message-ID: <083667B535F3464CA0DD0D1DAFA4E3760F2287AA@camexc1.kfs.local>

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.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich Jesse Sent: Thursday, December 06, 2007 11:09 PM To: oracle-l_at_freelists.org
Subject: How to purge dba_recyclebin from a procedure?

Hey all,

I'm looking to regularly purge the dba_recyclebin on a 10.1.0.5.0 DB on AIX.
 For various reasons, I'd like this in PL/SQL. But since the command requires SYSDBA, I'm not able to find any documentation on the availability
of that priv in a procedure (i.e. I know roles are not active in a procedure, but SYSDBA isn't a role). I've done something similar to this:

CONNECT / AS SYSDBA
GRANT SYSDBA TO my_dba;
CREATE OR REPLACE PROCEDURE my_dba.purge_dba_recycle_bin AS

        v_statement VARCHAR2(100) := 'PURGE DBA_RECYCLEBIN'; BEGIN
        EXECUTE IMMEDIATE v_statement;
END;
/

But when I try and execute the procedure as a non-SYSDBA user, I of course
get the ORA-1031 insufficient privs error. Also, the MY_DBA user was setup
specifically to not allow logins.

Thinking that the password file privs are only active for logins, I created
the proc under SYS (but don't tell anyone), and I get the same error on executing it. I'm unable to find confirmation on my theory via MetaLink,
Tahiti, nor Google.

I can live without the procedure, but the "why" is now bugging me to no end.

Thoughts anyone?
TIA!
Rich

--

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

Bu mesaj ve onunla iletilen tum ekler gonderildigi kisi ya da kuruma ozel ve Bankalar Kanunu geregince, gizlilik yukumlulugu tasiyor olabilir. Bu mesaj, hicbir sekilde, herhangi bir amac icin cogaltilamaz, yayinlanamaz ve para karsiligi satilamaz; mesajin yetkili alicisi veya alicisina iletmekten sorumlu kisi degilseniz, mesaj icerigini ya da eklerini kopyalamayiniz, yayinlamayiniz, baska kisilere yonlendirmeyiniz ve mesaji gonderen kisiyi derhal uyararak bu mesaji siliniz. Bu mesajin iceriginde ya da eklerinde yer alan bilgilerin dogrulugu, butunlugu ve guncelligi Bankamiz tarafindan garanti edilmemektedir ve bilinen viruslere karsi kontrolleri yapilmis olarak yollanan mesajin sisteminizde yaratabilecegi zararlardan Bankamiz sorumlu tutulamaz.

This message and the files attached to it are under the privacy liability in accordance with the Banking Law and confidential to the use of the individual or entity to whom they are addressed. This message cannot be copied, disclosed or sold monetary consideration for any purpose. If you are not the intended recipient of this message, you should not copy, distribute, disclose or forward the information that exists in the content and in the attachments of this message; please notify the sender immediately and delete all copies of this message. Our Bank does not warrant the accuracy, integrity and currency of the information transmitted with this message. This message has been detected for all known computer viruses thence our Bank is not liable for the occurrence of any system corruption caused by this message
--

http://www.freelists.org/webpage/oracle-l Received on Fri Dec 07 2007 - 04:23:29 CST