grant select on sys tables

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.


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' ;
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;

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.

By Laurent Schneider

Oracle Certified Master

Leave a comment

Your email address will not be published.