FAQ
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

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 3 of 4 | next ›
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