FAQ
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

Search Discussions

  • Yasin Baskan at Dec 7, 2007 at 10:23 am
    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
    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
  • Rich Jesse at Dec 7, 2007 at 3:13 pm
    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
  • Rich Jesse at Dec 7, 2007 at 6:50 pm
    Jason, I think you're on to something here. According to the docs, only the
    DBA_RECYCLEBIN option of PURGE requires SYSDBA, so if I build a list of
    objects in there first and purge them one at a time, I only need SELECT on
    the DBA_RECYCLEBIN view and DROP ANY for objects outside the definer's
    schema.

    It's annoying to have to code workarounds to Oracle "security".
    It's like being back in 8i all over again.

    Thanks, Jason!!!
    Rich
    I created a procedure once that queried dba_recyclebin and purged all of the
    table objects older than a specified age. I don't recall having the
    problems you described, probably because the script purged the individual
    objects rather than the whole recyclebin. You may want to consider that
    approach as a workaround to your problem.
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 6, '07 at 9:09p
activeDec 7, '07 at 6:50p
posts4
users2
websiteoracle.com

2 users in discussion

Rich Jesse: 3 posts Yasin Baskan: 1 post

People

Translate

site design / logo © 2022 Grokbase