Monday, March 9, 2015

Check users having grants with grant option in oracle database

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

No comments:

Post a Comment

Featured Post

Apply Patch 22191577 latest GI PSU to RAC and DB homes using Opatch auto or manual steps

Patch 22191577: GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.4.160119 (JAN2016) Unzip the patch 22191577 Unzip latest Opatch Version in or...