In earlier posts I described the process of building OEM report views in another schema and doing grants to the report schema JIFFY one at a time. As I developed more and more reports I got tired of wasting my time with individual grants.
All of the SYSMAN views I’ve used in my custom reports are based on MGMT$ views, so we can simply do bulk grants as SYSMAN using good old dynamic SQL:
SET ECHO OFFSET HEADING OFF LINES 120 PAGES 999 FEEDBACK OFF SPOOL grant_select_to_jiffy.sql SELECT 'GRANT SELECT ON sysman.' || LOWER ( view_name ) || ' TO jiffy WITH GRANT OPTION;' AS commands FROM user_views WHERE view_name LIKE 'MGMT$%' OR view_name LIKE 'CM$MGMT%' ORDER BY view_name; @grant_select_to_jiffy.sql