Tags

,

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