Extrair permissões de esquemas especificos

A dica de hoje é de como extrair os grants aplicados aos esquemas LOGIXEXP, GJAV, LOGIX e POLIBR.

set long 90000
set linesize 32000
set trimspool on
set heading off
set tab off
set feedback off
SET echo off
set pagesize 0
column TBL format a121 word_wrapped
SELECT * FROM (
SELECT 'GRANT '||PRIVILEGE||' TO '||GRANTEE||';' FROM DBA_SYS_PRIVS
WHERE GRANTEE IN ('LOGIXEXP','GJAV','LOGIX','POLIBR',)
UNION ALL
SELECT 'GRANT '||PRIVILEGE||' ON '||GRANTOR||'.'||TABLE_NAME||' TO '||GRANTEE||';' FROM DBA_TAB_PRIVS
WHERE GRANTEE IN ('LOGIXEXP','GJAV','LOGIX','POLIBR')
UNION ALL
SELECT 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE||';' FROM DBA_ROLE_PRIVS
WHERE GRANTEE IN ('LOGIXEXP','GJAV','LOGIX','POLIBR'));
SQL

Leave a Reply

Your email address will not be published. Required fields are marked *

search previous next tag category expand menu location phone mail time cart zoom edit close