I prefer to use a powerful named user with dba rather than sys. It is more conform to the security policies in place regarding accounting of administrator operations.
Very occasionaly, my user get ORA-1031 insufficient privileges even if I have the dba role.
Amoung others, I have “PURGE DBA_RECYCLEBIN” and DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE
update, 2012-07-24
For purge dba_recyclebin, you probably should purge tables individually
exec for f in(select*from dba_recyclebin where owner!='SYS' and type='TABLE')loop execute immediate 'purge table "'||f.owner||'"."'||f.object_name||'"';end loop;
For DBMS_STREAMS_AUTH, what I am actually missing, is the GRANT OPTION on some documented dba views and dbms package. So I could safely grant the grant option to my user for all sys objects that have been granted to DBA, PUBLIC and any other roles.
Kind of
create table scott.t as
select distinct owner,table_name,privilege
from dba_tab_privs t
where privilege not in ('USE','DEQUEUE') and owner='SYS' ;
begin
for f in(select * from scott.t) loop
execute immediate
'grant '||f.privilege||' on "'||f.owner||'"."'
||f.table_name||'" to scott with grant option';
end loop;
end;
/
It is better to not select from dba_tab_privs directly, as executing immediate while opening the cursor may have unexpected side effects.
This may help you to increase your security by reducing your connections as sys.