Security considerations and challenges when querying OEM repository tables/views

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
       count( member_target_guid ) cnt
       sysman.mgmt$group_derived_memberships o ,
       sysman.mgmt$target t ,
       sysman.mgmt$availability_current st
       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
  sysman.mgmt$metric_current i
  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'


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: