Security considerations and challenges when querying OEM repository tables/views

June 22, 2012

OEM utilizes virtual private database (VPD) policies in addition to the standard database privileges and roles. That means that the DBA cannot just give privileges on OEM objects to a DB user, it has to take care of the VPD setting as well.

There are two ways to allow a DB user to access OEM table/views.

The first way is to configure the DB user as an OEM Administrator (Setup->Security->Administrators).Give the DB user/OEM admin at least EM_ALL_VIEWER role and the DB user is ready to access all OEM objects.

The second way is to create a regularly refreshed materialized view in the SYSMAN and grant that view to the DB user who needs the information.

This is the materialized view I use for the Forced Logging UDM in OEM 12c (Forced Logging method described in defail in my presentation and white paper) :

create materialized view "SYSMAN"."UDM_FORCED_LOGGING" tablespace "????????" pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K buffer_pool default) logging using no index refresh complete start with to_date('06-22-2012 14:39:25','MM-dd-yyyy hh24:mi:ss') next sysdate + 1/96
as
  select
       count( member_target_guid ) cnt
  from
       sysman.mgmt$group_derived_memberships o ,
       sysman.mgmt$target t ,
       sysman.mgmt$availability_current st
  where
       o.composite_target_name = 'PRODUCTION_SYSTEMS'
  and  o.member_target_type     in ('oracle_database', 'rac_database')
  and  ( t.target_type           ='rac_database'
         or (t.target_type            ='oracle_database'
             and t.type_qualifier3        != 'RACINST'))
  and  o. member_target_guid     = t.target_guid
  and  t.target_guid             = st.target_guid
  and  st.availability_status    = 'Target Up'
  and  t.target_name not        in ('????????')
  and  not exists
      (select
  *
       from
  sysman.mgmt$metric_current i
       where
  i.target_guid        = o.member_target_guid
       and  metric_name          = 'ME$ForcedLogging'
       and  column_label         = 'ForcedLogging'
       and  metric_column        = 'ForcedLogging'
       and  collection_timestamp > sysdate - 20/1440
       and  value                = 'YES'
    )