Rem
Rem    NOME
Rem      grantofdba.sql 
Rem
Rem    DESCRIÇÃO   
Rem      Este script lista todos os usuários que possuem os privilégios existentes na 
Rem      role DBA, menos os privilegios existentes nas roles CONNECT e RESOURCE.
Rem   
Rem    UTILIZAÇÃO
Rem      grantofdba.sql
Rem
Rem    ATUALIZAÇÕES  (MM/DD/YY)
Rem      FERR@RI      01/06/08 - criação do script
Rem
Rem ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

col PRIVILEGE for a40
col GRANTEE for a15
col SCHEMA.OBJECT for a30
set feedback off
set verify off

PROMPT
PROMPT Este script lista todos os usuários que possuem os privilégios existentes na
PROMPT role DBA, menos os privilegios existentes nas roles CONNECT e RESOURCE.

PROMPT
PROMPT Roles atribuídas ao Usuário: 
select GRANTEE,        
       GRANTED_ROLE,
       ADMIN_OPTION,
       DEFAULT_ROLE,
       'revoke '||GRANTED_ROLE|| ' from ' ||GRANTEE|| ';' AS "GRANTs EM ROLES"
from dba_role_privs 
where GRANTEE not in ('SYS','SYSTEM','DBA','EXP_FULL_DATABASE','IMP_FULL_DATABASE')
 and GRANTED_ROLE in ('DBA','EXP_FULL_DATABASE','IMP_FULL_DATABASE','DELETE_CATALOG_ROLE','SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','GATHER_SYSTEM_STATISTICS')
/

PROMPT
PROMPT
PROMPT Privilegio de SYSDBA
col sysdba  for a10
col sysoper for a10
select USERNAME,
       SYSDBA,         
       SYSOPER         
from V$PWFILE_USERS
/ 


PROMPT
PROMPT
PROMPT Privilegios de Sistema: 
select GRANTEE,        
       PRIVILEGE,      
       ADMIN_OPTION,
       'revoke ' ||PRIVILEGE|| ' from ' ||GRANTEE|| ';' AS "GRANTs DE SISTEMA"
FROM dba_sys_privs
where GRANTEE not in ('SYS','DBA','EXP_FULL_DATABASE','IMP_FULL_DATABASE','AQ_ADMINISTRATOR_ROLE',
                      'DBSNMP','OEM_MONITOR','OUTLN','PERFSTAT','QUEST','HS_ADMIN_ROLE','AQ_USER_ROLE')           
 and PRIVILEGE in (
'AUDIT ANY',  
'DROP USER',  
'RESUMABLE',  
'ALTER USER',  
'ANALYZE ANY',  
'BECOME USER',  
'CREATE ROLE',  
'CREATE RULE',  
'CREATE USER',  
'ALTER SYSTEM',  
'AUDIT SYSTEM',  
'DROP PROFILE',  
'ALTER PROFILE',  
'DROP ANY ROLE',  
'DROP ANY RULE',  
'DROP ANY TYPE',  
'DROP ANY VIEW',  
'QUERY REWRITE',  
'ALTER ANY ROLE',  
'ALTER ANY RULE',  
'ALTER ANY TYPE',  
'ALTER DATABASE',  
'CREATE LIBRARY',  
'CREATE PROFILE',  
'DROP ANY INDEX',  
'DROP ANY TABLE',  
'GRANT ANY ROLE',  
'LOCK ANY TABLE',  
'UNDER ANY TYPE',  
'UNDER ANY VIEW',  
'ALTER ANY INDEX',  
'ALTER ANY TABLE',  
'CREATE ANY RULE',  
'CREATE ANY TYPE',  
'CREATE ANY VIEW',  
'CREATE RULE SET',  
'CREATE SNAPSHOT',  
'DROP TABLESPACE',  
'UNDER ANY TABLE',  
'ALTER TABLESPACE',  
'BACKUP ANY TABLE',  
'CREATE ANY INDEX',  
'CREATE ANY TABLE',  
'CREATE DIMENSION',  
'DELETE ANY TABLE',  
'DROP ANY CLUSTER',  
'DROP ANY CONTEXT',  
'DROP ANY LIBRARY',  
'DROP ANY OUTLINE',  
'DROP ANY SYNONYM',  
'DROP ANY TRIGGER',  
'EXECUTE ANY RULE',  
'EXECUTE ANY TYPE',  
'INSERT ANY TABLE',  
'MANAGE ANY QUEUE',  
'SELECT ANY TABLE',  
'UPDATE ANY TABLE',  
'ALTER ANY CLUSTER',  
'ALTER ANY LIBRARY',  
'ALTER ANY OUTLINE',  
'ALTER ANY TRIGGER',  
'COMMENT ANY TABLE',  
'CREATE TABLESPACE',  
'DEQUEUE ANY QUEUE',  
'DROP ANY OPERATOR',  
'DROP ANY RULE SET',  
'DROP ANY SEQUENCE',  
'DROP ANY SNAPSHOT',  
'ENQUEUE ANY QUEUE',  
'FORCE TRANSACTION',  
'MANAGE TABLESPACE',  
'ON COMMIT REFRESH',  
'ALTER ANY RULE SET',  
'ALTER ANY SEQUENCE',  
'ALTER ANY SNAPSHOT',  
'CREATE ANY CLUSTER',  
'CREATE ANY CONTEXT',  
'CREATE ANY LIBRARY',  
'CREATE ANY OUTLINE',  
'CREATE ANY SYNONYM',  
'CREATE ANY TRIGGER',  
'DROP ANY DIMENSION',  
'DROP ANY DIRECTORY',  
'DROP ANY INDEXTYPE',  
'DROP ANY PROCEDURE',  
'RESTRICTED SESSION',  
'ALTER ANY DIMENSION',  
'ALTER ANY INDEXTYPE',  
'ALTER ANY PROCEDURE',  
'ALTER RESOURCE COST',  
'CREATE ANY OPERATOR',  
'CREATE ANY RULE SET',  
'CREATE ANY SEQUENCE',  
'CREATE ANY SNAPSHOT',  
'DEBUG ANY PROCEDURE',  
'DROP PUBLIC SYNONYM',  
'EXECUTE ANY LIBRARY',  
'FLASHBACK ANY TABLE',  
'GRANT ANY PRIVILEGE',  
'SELECT ANY SEQUENCE',  
'CREATE ANY DIMENSION',  
'CREATE ANY DIRECTORY',  
'CREATE ANY INDEXTYPE',  
'CREATE ANY PROCEDURE',  
'EXECUTE ANY OPERATOR',  
'EXECUTE ANY RULE SET',  
'GLOBAL QUERY REWRITE',  
'CREATE PUBLIC SYNONYM',  
'DEBUG CONNECT SESSION',  
'DROP ROLLBACK SEGMENT',  
'EXECUTE ANY INDEXTYPE',  
'EXECUTE ANY PROCEDURE',  
'FORCE ANY TRANSACTION',  
'SELECT ANY DICTIONARY',  
'ALTER ROLLBACK SEGMENT',  
'CREATE ROLLBACK SEGMENT',  
'CREATE EVALUATION CONTEXT',  
'DROP PUBLIC DATABASE LINK',  
'GRANT ANY OBJECT PRIVILEGE',  
'ADMINISTER DATABASE TRIGGER',  
'ADMINISTER RESOURCE MANAGER',  
'CREATE PUBLIC DATABASE LINK',  
'DROP ANY EVALUATION CONTEXT',  
'ALTER ANY EVALUATION CONTEXT',  
'CREATE ANY EVALUATION CONTEXT',  
'EXECUTE ANY EVALUATION CONTEXT'
)   
order by GRANTEE, PRIVILEGE
/


PROMPT
PROMPT OBS: Para ver privilegios em objetos do SYS -> tecle ENTER!
PAUSE

col GRANTEE for a25
PROMPT
PROMPT Privilegios de Objeto:  
select GRANTEE, 
       PRIVILEGE,
       ' ON '  as "ON",        
       OWNER|| '.'||TABLE_NAME AS "SCHEMA.OBJECT",           
       GRANTABLE, 
       GRANTOR,
       'revoke ' ||PRIVILEGE|| ' on ' ||OWNER|| '.'||TABLE_NAME|| ' from ' ||GRANTEE|| ';' AS "GRANTs DE OBJETO"      
from dba_tab_privs 
where GRANTEE not in ('SYS','SYSTEM','DBA','PUBLIC','SELECT_CATALOG_ROLE','EXP_FULL_DATABASE','AQ_ADMINISTRATOR_ROLE',
                     'HP_DBSPI','PERFSTAT','QUEST','EXECUTE_CATALOG_ROLE','IMP_FULL_DATABASE','DELETE_CATALOG_ROLE',
                     'GATHER_SYSTEM_STATISTICS','HS_ADMIN_ROLE','OEM_MONITOR','OUTLN','AQ_USER_ROLE')
  and OWNER = 'SYS'
order by GRANTEE
/   

PROMPT

set feedback on
set verify on