Developer Access Considerations

Interactive use of the OEM application encompasses two levels of access. Access to the OEM application and then access to each individual database when invoking features such as the Performance tab.

The OEM access is pretty self explanatory. You create users to control access to OEM and have pretty granular control over things they can see. If you want to create DBA level users, make them Super Administrators. If you want to create more restricted users, keep their access low and allow them access to only the targets you desire.

Basic Access

Our developers often want access to the Performance tab in OEM. This requires read only access to a lot of data dictionary and performance views.

Until I can take the time to find a nice safe way to grant exactly what they need I rely on granting their user accounts SELECT_CATALOG_ROLE. Yes, probably more far reaching than I would like but this is only on Dev and Test databases, never production (where they could see v$sql which may have sensitive information w/o binds).

CREATE USER ImaDeveloper IDENTIFIED BY welcome123;
GRANT SELECT_CATALOG_ROLE TO ImaDeveloper:

AWR Reports and DBMS_WORKLOAD_REPOSITORY access

If you want the user to be able to generate AWR reports they will need execute access to the SYS.DBMS_WORKLOAD_REPOSITORY package. But be aware they they can run other AWR related procedures in the package including procedures that tweak the Snapshot Retention Period and Scheduling Intervals -- this is bad. I haven't found an OEM friendly way to keep a user with Execute on DBMS_WORKLOAD_REPOSITORY from doing this yet so I only allow this in Dev/Test, not prod.

If changing AWR parameters is a concern then have your user request the report generation or provide a procedure to generate the AWR reports manually. Also consider creating a Definer's RIghts proc that has 1 function to call AWR_REPORT_TEXT or AWR_REPORT_HTML.

grant execute on DBMS_WORKLOAD_REPOSITORY to ImaDeveloper;

OEM_MONITOR or OEM_ADVISOR roles

Without a database login the OEM user can review information collected by the agent... General configuration information, host performance information, etc.

When the user clicks on the Performance Tab for a database they will be asked for a database login. This login must have access to system views in order for the real-time performance screens to work.

I don't typically give these out to non DBAs. Most developers don't even know about these features and if needed I would invoke while we were working on a tuning engagement, so no need to proactively give these privileges out. A little more detail about the privileges is below so you can decide for yourself.

Also the login must have permissions to at least read the objects involved in the query. If the login has OEM_ADVISOR but does not have permissions to read the objects in the SQL being analyzed you will get Permissions errors returned when a SQL Tuning Advisor job runs.

OEM_ADVISOR role

Required to run the Segment Advisor manually with Enterprise Manager.

-- Checked in 10.2.0.4 and 11.1.0.7
GRANT CREATE JOB TO OEM_ADVISOR;
GRANT ADMINISTER SQL TUNING SET TO OEM_ADVISOR;
GRANT ADVISOR TO OEM_ADVISOR;

OEM_MONITOR role

Provides privileges needed by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database.

-- Checked in 10.2.0.4 and 11.1.0.7
GRANT SELECT ON DBSNMP.MGMT_BASELINE TO OEM_MONITOR;
GRANT SELECT ON DBSNMP.MGMT_BASELINE_SQL TO OEM_MONITOR;
GRANT SELECT ON DBSNMP.MGMT_HISTORY TO OEM_MONITOR;
GRANT SELECT ON DBSNMP.MGMT_HISTORY_SQL TO OEM_MONITOR;
GRANT SELECT ON DBSNMP.MGMT_LATEST TO OEM_MONITOR;
GRANT SELECT ON DBSNMP.MGMT_LATEST_SQL TO OEM_MONITOR;
GRANT EXECUTE ON DBSNMP.MGMT_RESPONSE TO OEM_MONITOR;
GRANT EXECUTE ON SYS.DBMS_AQ TO OEM_MONITOR;
GRANT EXECUTE ON SYS.DBMS_AQADM TO OEM_MONITOR;
GRANT EXECUTE ON SYS.DBMS_DRS TO OEM_MONITOR;
GRANT EXECUTE ON SYS.DBMS_MONITOR TO OEM_MONITOR;
GRANT EXECUTE ON SYS.DBMS_SERVER_ALERT TO OEM_MONITOR;
GRANT EXECUTE ON SYS.DBMS_SYSTEM TO OEM_MONITOR;
GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO OEM_MONITOR;
GRANT ANALYZE ANY DICTIONARY TO OEM_MONITOR;
GRANT SELECT ANY DICTIONARY TO OEM_MONITOR;
GRANT ANALYZE ANY TO OEM_MONITOR;
GRANT CREATE SESSION TO OEM_MONITOR;
GRANT CREATE JOB TO OEM_MONITOR;
GRANT ADVISOR TO OEM_MONITOR;
BEGIN
SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
  PRIVILEGE    => 'MANAGE_ANY',
  GRANTEE      => 'OEM_MONITOR',
  ADMIN_OPTION => FALSE);
END;
/