Show objects without necessary grants
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