Read-Only Users in Oracle Enterprise Manager

2015-12-01 Off By Markus Flechtner

Oracle Enterprise Manager is really powerful tool for database administration (and much more), which can be used by endusers which are not 100% familiar with the the Oracle architecture and the data ditionary. But if these users have too many privileges, they can cause damage to the database. How to restrict the access to Enterprise Manager for theses users so that they cannot do any harm?

Oracle Enterprise Manager 12c Cloud Control

We need two steps for EM 12c Cloud Control to grant read-only-access to databases:

1. Create a user in Enterprise Manager
You must create a user in EM („Setup -> Security -> Administrators“) and grant the EM privilege „VIEW ANY TARGET“ bekommt. Alternatively you can grant access to individual targets for these users.

After that the user can logon to EM

2. Create database users

But the user needs a corresponding database user so that he can connect to the database and can view information from inside the database. There’s a predefined role “OEM_MONITOR” for these read-only-users. This role includes the following privileges:

SQL> column owner format a10
 SQL> column table_name format a30
 SQL> column privilege format a30
 SQL> set linesize 200
 SQL> set pagesize 100
 SQL> select privilege from dba_sys_privs where grantee='OEM_MONITOR';
PRIVILEGE
 ------------------------------
 CREATE JOB
 ANALYZE ANY
 ANALYZE ANY DICTIONARY
 MANAGE ANY QUEUE
 ADVISOR
 CREATE SESSION
 SELECT ANY DICTIONARY

7 rows selected.

SQL> select granted_role from dba_role_privs where grantee='OEM_MONITOR';

GRANTED_ROLE
 ------------------------------------------
 SELECT_CATALOG_ROLE

SQL> select owner,table_name,privilege from dba_tab_privs where grantee='OEM_MONITOR';

OWNER TABLE_NAME PRIVILEGE
 ---------- ------------------------------ -----------------------
 SYS DBMS_DRS EXECUTE
 SYS DBMS_AQ EXECUTE
 SYS DBMS_AQADM EXECUTE
 SYS DBMS_SERVER_ALERT EXECUTE
 SYS DBMS_MONITOR EXECUTE
 SYS DBMS_WORKLOAD_REPOSITORY EXECUTE
 SYS DBMS_SYSTEM EXECUTE
 DBSNMP MGMT_BASELINE SELECT
 DBSNMP MGMT_BASELINE_SQL SELECT
 DBSNMP MGMT_LATEST SELECT
 DBSNMP MGMT_LATEST_SQL SELECT
 DBSNMP MGMT_HISTORY SELECT
 DBSNMP MGMT_HISTORY_SQL SELECT
 DBSNMP MGMT_RESPONSE EXECUTE
 DBSNMP BSLN_METRIC_T EXECUTE
 DBSNMP BSLN_METRIC_SET EXECUTE
 DBSNMP BSLN_VARIANCE_T EXECUTE
 DBSNMP BSLN_VARIANCE_SET EXECUTE
 DBSNMP BSLN_OBSERVATION_T EXECUTE
 DBSNMP BSLN_OBSERVATION_SET EXECUTE
 DBSNMP BSLN_STATISTICS_T EXECUTE
 DBSNMP BSLN_STATISTICS_SET EXECUTE
 DBSNMP BSLN_METRIC_DEFAULTS SELECT
 DBSNMP BSLN_TIMEGROUPS SELECT
 DBSNMP BSLN_BASELINES SELECT
 DBSNMP BSLN_STATISTICS SELECT
 DBSNMP BSLN_THRESHOLD_PARAMS SELECT
 DBSNMP BSLN EXECUTE
 DBSNMP BSLN_INTERNAL EXECUTE
 DBSNMP MGMT_BSLN_METRICS SELECT
 DBSNMP MGMT_BSLN_DATASOURCES SELECT
 DBSNMP MGMT_BSLN_BASELINES SELECT
 DBSNMP MGMT_BSLN_INTERVALS SELECT
 DBSNMP MGMT_BSLN_THRESHOLD_PARMS SELECT
 DBSNMP MGMT_BSLN_STATISTICS SELECT
 DBSNMP MGMT_UPDATE_DB_FEATURE_LOG EXECUTE
 SYS ALERT_QUE DEQUEUE

So the steps are:

Create a database user (e.g. OEM_READONLY)
Grant the role OEM_MONITOR to the user

Further information:

MOS-Note: „Grid Control User Model:Steps to Create Administrator Accounts (Super Admin, Admin, View Only) in Grid Control“ (Doc ID 377310.1)

Oracle Enterprise Manager 12c Database Express

Granting read-only-access to Enterprise Manager 12c DB Express, the „little brother“, is easier. As EM 12c DB Express is a tool on database level, the only thing you need is a database user. There’s a predefined database role for read-only users: EM_EXPRESS_BASIC. This role includes the following privileges:

SQL> column owner format a10
 SQL> column table_name format a30
 SQL> column privilege format a30
 SQL> set linesize 200
 SQL> set pagesize 100
 SQL> select privilege from dba_sys_privs where grantee='EM_EXPRESS_BASIC';

PRIVILEGE
 ------------------------------
 EM EXPRESS CONNECT
 CREATE SESSION

SQL> select granted_role from dba_role_privs where grantee='EM_EXPRESS_BASIC';

GRANTED_ROLE
 ----------------------------------------
 SELECT_CATALOG_ROLE

SQL> select owner,table_name,privilege from dba_tab_privs where grantee='EM_EXPRESS_BASIC';

OWNER TABLE_NAME PRIVILEGE
 ---------- ------------------------------ ----------------------
 SYS DBMS_PERF EXECUTE
 SYS PRVTEMX_ADMIN EXECUTE
 SYS V_$DIAG_INCIDENT SELECT

SQL> create user EM_READONLY identified by EM_READONLY
 2 default tablespace USERS temporary tablespace TEMP;
User created.

SQL> grant EM_EXPRESS_BASIC to EM_READONLY;
Grant succeeded.

A database user with these privileges can connect to EM DB Express 12c: