Objects without grants

Sometimes (usualy during tests 🙂 ) it is useful to check if there are some objects which do not have any grants – you can avoid many problems this way.


SELECT DISTINCT O.OBJECT_NAME FROM ALL_OBJECTS O WHERE O.OWNER = '...schema_name...' 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 = '...schema_name...'
--AND T.GRANTEE <> 'PUBLIC'  --uncomment this if you want to ignore grants for public

Leave a Reply

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