Oracle Tips and Techniques

Normally users who do not have DBA roles can not access Oracle 11g Performance related information using OEM 11g Grid Control.

Normally only Oracle 11g DBAs are allowed to view these tabs and they use them to do performance tuning or to identify problematic SQL or session etc.

However there is a way to grant access to OEM 11g Grid Control performance tab to users who do not have DBA roles. In this case the user means the users in the target database. This is helpful when you as a Oracle 11g DBA wants to grant access to the performance tab to management users who would like to have access to these data.

This is done by assigning OEM_MONITOR role to those users.

Caution: Please not that OEM_MONITOR role comes with a number of powerful privileges. So really give a thought if you need to assign OEM_MONITOR to not so technical users.

A way to avoid that is to create another role and assign OEM_MONITOR role like privileges to that role.

Here is a list of the privileges that OEM_MONITOR role has in Oracle 11g database:

SQL> select ROLE, TABLE_NAME, PRIVILEGE from ROLE_TAB_PRIVS where ROLE=’OEM_MONITOR’ order by 3,2;
ROLE                           TABLE_NAME                     PRIVILEGE
—————————— —————————— —————————————-
OEM_MONITOR                    ALERT_QUE                      DEQUEUE
OEM_MONITOR                    BSLN                           EXECUTE
OEM_MONITOR                    BSLN_INTERNAL                  EXECUTE
OEM_MONITOR                    BSLN_METRIC_SET                EXECUTE
OEM_MONITOR                    BSLN_METRIC_T                  EXECUTE
OEM_MONITOR                    BSLN_OBSERVATION_SET           EXECUTE
OEM_MONITOR                    BSLN_OBSERVATION_T             EXECUTE
OEM_MONITOR                    BSLN_STATISTICS_SET            EXECUTE
OEM_MONITOR                    BSLN_STATISTICS_T              EXECUTE
OEM_MONITOR                    BSLN_VARIANCE_SET              EXECUTE
OEM_MONITOR                    BSLN_VARIANCE_T                EXECUTE
OEM_MONITOR                    DBMS_AQ                        EXECUTE
OEM_MONITOR                    DBMS_AQADM                     EXECUTE
OEM_MONITOR                    DBMS_DRS                       EXECUTE
OEM_MONITOR                    DBMS_MONITOR                   EXECUTE
OEM_MONITOR                    DBMS_SERVER_ALERT              EXECUTE
OEM_MONITOR                    DBMS_SYSTEM                    EXECUTE
OEM_MONITOR                    DBMS_WORKLOAD_REPOSITORY       EXECUTE
OEM_MONITOR                    MGMT_RESPONSE                  EXECUTE
OEM_MONITOR                    MGMT_UPDATE_DB_FEATURE_LOG     EXECUTE
OEM_MONITOR                    BSLN_BASELINES                 SELECT
OEM_MONITOR                    BSLN_METRIC_DEFAULTS           SELECT
OEM_MONITOR                    BSLN_STATISTICS                SELECT
OEM_MONITOR                    BSLN_THRESHOLD_PARAMS          SELECT
OEM_MONITOR                    BSLN_TIMEGROUPS                SELECT
OEM_MONITOR                    MGMT_BASELINE                  SELECT
OEM_MONITOR                    MGMT_BASELINE_SQL              SELECT
OEM_MONITOR                    MGMT_BSLN_BASELINES            SELECT
OEM_MONITOR                    MGMT_BSLN_DATASOURCES          SELECT
OEM_MONITOR                    MGMT_BSLN_INTERVALS            SELECT
OEM_MONITOR                    MGMT_BSLN_METRICS              SELECT
OEM_MONITOR                    MGMT_BSLN_STATISTICS           SELECT
OEM_MONITOR                    MGMT_BSLN_THRESHOLD_PARMS      SELECT
OEM_MONITOR                    MGMT_HISTORY                   SELECT
OEM_MONITOR                    MGMT_HISTORY_SQL               SELECT
OEM_MONITOR                    MGMT_LATEST                    SELECT
OEM_MONITOR                    MGMT_LATEST_SQL                SELECT