SELECT a.owner,a.table_name,a.privilege,a.GRANTEE from dba_tab_privs a WHERE a.grantable='YES'
and a.owner not in ('ANONYMOUS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','FLOWS_FILES','LBACSYS','MDDATA','MDSYS','MGMT_VIEW','ODM','ODM_MTR','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','PERFSTAT','PUBLIC','QS','QS_ADM','QS_CB','QS_CBADM','QS_CS','QS_ES','QS_OS','QS_WS','RMAN','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SYSTEM','TSMSYS','WKPROXY','WKSYS','WMSYS','XDB','XS$NULL')
minus
SELECT a.owner,a.table_name,a.privilege,a.GRANTEE from dba_tab_privs a WHERE a.grantable='YES'
and a.owner like 'CUBEONE%'
and ( ( a.table_name in ('XX1','ODEC_CHAR_SEL','ODEC_CHAR_SEL_VAR','ODEC_VARCHAR2_SEL','ODEC_VARCHAR3_SEL','ODEC_VARCHAR2_SEL_VAR','ODEC_VARCHAR3_SEL_VAR','ODEC_NUMBER_SEL','ODEC_DATE_SEL','ODEC_FLOAT_SEL','ODEC_INTEGER_SEL','ODEC_LONG_SEL','ODEC_LONG_SEL2','ODEC_CLOB_SEL','ODEC_CLOB_SEL2','ODEC_BLOB_SEL') and a.privilege='EXECUTE')
or ( a.table_name in ('DIA_ACCESS_LOG','DIA_ACCESS_LOG_SIUD','DIA_ACCESS_LOG_SUCCESS_FLAG','DIA_ACCESS_RPT') and a.privilege='SELECT' )
or ( a.table_name in ('DIA_ACCESS_LOG') and a.privilege='INSERT')
);
and a.owner not in ('ANONYMOUS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','FLOWS_FILES','LBACSYS','MDDATA','MDSYS','MGMT_VIEW','ODM','ODM_MTR','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','PERFSTAT','PUBLIC','QS','QS_ADM','QS_CB','QS_CBADM','QS_CS','QS_ES','QS_OS','QS_WS','RMAN','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SYSTEM','TSMSYS','WKPROXY','WKSYS','WMSYS','XDB','XS$NULL')
minus
SELECT a.owner,a.table_name,a.privilege,a.GRANTEE from dba_tab_privs a WHERE a.grantable='YES'
and a.owner like 'CUBEONE%'
and ( ( a.table_name in ('XX1','ODEC_CHAR_SEL','ODEC_CHAR_SEL_VAR','ODEC_VARCHAR2_SEL','ODEC_VARCHAR3_SEL','ODEC_VARCHAR2_SEL_VAR','ODEC_VARCHAR3_SEL_VAR','ODEC_NUMBER_SEL','ODEC_DATE_SEL','ODEC_FLOAT_SEL','ODEC_INTEGER_SEL','ODEC_LONG_SEL','ODEC_LONG_SEL2','ODEC_CLOB_SEL','ODEC_CLOB_SEL2','ODEC_BLOB_SEL') and a.privilege='EXECUTE')
or ( a.table_name in ('DIA_ACCESS_LOG','DIA_ACCESS_LOG_SIUD','DIA_ACCESS_LOG_SUCCESS_FLAG','DIA_ACCESS_RPT') and a.privilege='SELECT' )
or ( a.table_name in ('DIA_ACCESS_LOG') and a.privilege='INSERT')
);
No comments:
Post a Comment