, , , ,

JIFFY Schema

In my shop we have a local schema inside our OEM repository named JIFFY (for this posting).

JIFFY tables contain information that OEM doesn’t contain like a list of the SOX databases in our environment.  When we started exploring the canned reports in OEM 12c we identified a couple places where it would be handy to filter or even supplement reports with data from JIFFY.

Two huge problems popped up:

  1. JIFFY couldn’t create his own views based on the SYSMAN views
  2. Information Publisher couldn’t present JIFFY’s views no matter which SELECT grants I made on the objects to SYSMAN

Those problems were compounded by this warning appears when you create a new report definition:

The Information Publisher reports feature is being replaced by Oracle Business Intelligence (BI) Publisher reports feature. Oracle strongly recommends migrating your reports to BI Publisher as the Information Publisher reports feature will no longer be enhanced and is deprecated as of Enterprise Manager release 12.

Was I wasting my time?

So I installed BI Publisher and, presto, the canned reports for that product were so limited that I’d starting from scratch for everything.  There’s more: creating a new report with BIP requires working knowledge of the OBI report creation tool.  What’s more, OEM and database security doesn’t work for BIP.  I’d need to work with either the Weblogic scripting tool or through the Admin Console to make the grants I needed for my peers and customers to create reports.

The deprecation warning sounds pretty final, so am I wasting my time with Info Publisher?  Read the warning again.  It was suppoed to go away with OEM 12c!  I’ve been running 12c since it came out so maybe The End of Info Publisher isn’t that close.

Now that psychological hurdle has passed, it’s time to turn back into a DBA and figure out how to make those grants work.

Allowing other database accounts to read to SYSMAN objects

JIFFY could see SYSMAN’s database objects but couldn’t read them.  I ran through the typical set of grants as SYSMAN (GRANT SELECT ON sysman.mgmt$targets TO jiffy WITH ADMIN OPTION, for instance).  Nothing.

SYSMAN objects are obviously protected by security of a different type.

Existing grants on the objects were limited to SELECT privilege to MGMT_VIEW.  That must be it, right?  I’ll just grant JIFFY the MGMT_VIEW role.  MGMT_VIEW is a user, not a role but he has MGMT_ECM_VIEW and MGMT_USER roles, so that must be the key.  Of those two, MGMT_USER has SELECT on the views I need but not SELECT with ADMIN OPTION required to create views in JIFFY’s schema.

Try this instead (as SYSMAN user):

INSERT INTO mgmt_role_grants VALUES ('JIFFY','EM_ALL_VIEWER',0,0);

Now I can see the data as JIFFY.  Biggest hurdle seems to be passed.

Grant SELECT WITH ADMIN OPTION on the views you want to build in your schema.

So I built this view:

CREATE OR REPLACE FORCE VIEW jiffy.oem_host_inventory
SELECT host_name,
       ROUND ( mem / 1024, 1 ) gb_memory,
       logical_cpu_count cpu_count, os_summary
FROM sysman.mgmt$os_hw_summary
ORDER BY host_name;

Halfway there!  Info Publisher could see JIFFY’s and apply view in a report but at run-time the report complains about a missing table or view.

A Secret Revealed

Info Publisher reports run as the MGMT_VIEW user, not as SYSMAN.

Quick GRANT SELECT to MGMT_VIEW and I should be in business.  Still can’t find it!

And Still Another Secret

Fully qualified objects names don’t work for Info Publisher, so it’s not simply  a matter of object grants, you also have to declare a PUBLIC SYNOMYM for each object.