Simple query to check objects which do not have some specified grants:
SELECT DISTINCT O.OBJECT_NAME FROM ALL_OBJECTS O WHERE O.OWNER = '...your_schema...' AND O.OBJECT_NAME LIKE '...some_mask...%' AND O.OBJECT_TYPE IN ('TABLE', 'VIEW', 'MATERIALIZED VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'PACKAGE BODY', 'TYPE') MINUS SELECT DISTINCT T.TABLE_NAME FROM ALL_TAB_PRIVS T WHERE T.TABLE_SCHEMA = '...your_schema...' AND T.TABLE_NAME LIKE '...some_mask...%' AND (T.GRANTEE NOT IN ( '...role_name...', '...role_name....') )
Leave a Reply