Skip to content
Home » Oracle » EM_EXPRESS_ALL, How and Why

EM_EXPRESS_ALL, How and Why

EM_EXPRESS_ALL

Whereas EM_EXPRESS_BASIC provides some basic privileges to monitor properties through EM, EM_EXPRESS_ALL, also an Oracle predefined role, has some more advanced privileges which enable us to manage properties through EM.

To grant the role to an user who wants to manage properties via EM, we only need a granting like this:

SQL> grant em_express_all to sa;

Grant succeeded.

The grantee can also be a role. In this case, we granted the role to system administrators who are required to manage and maintain database servers.

Here we list privileges in EM_EXPRESS_ALL by the following categories.

  1. Roles
  2. System Privileges
  3. Object Privileges

Roles

We use the following SQL statement to check the roles in EM_EXPRESS_ALL.

SQL> set pagesize 1000;
SQL> select granted_role from dba_role_privs where grantee = 'EM_EXPRESS_ALL' order by 1;

There's only 1 role granted to EM_EXPRESS_ALL.

  • EM_EXPRESS_BASIC

As we expected, EM_EXPRESS_ALL inherits all privileges from EM_EXPRESS_BASIC.

System Privileges

We use the following SQL statement to check the system privileges in EM_EXPRESS_ALL.

SQL> select privilege from dba_sys_privs where grantee = 'EM_EXPRESS_ALL' order by 1;

There're 23 system privileges granted to EM_EXPRESS_ALL.

  • ADMINISTER ANY SQL TUNING SET
  • ADMINISTER RESOURCE MANAGER
  • ADMINISTER SQL MANAGEMENT OBJECT
  • ADMINISTER SQL TUNING SET
  • ADVISOR
  • ALTER ANY ROLE
  • ALTER PROFILE
  • ALTER SYSTEM
  • ALTER TABLESPACE
  • ALTER USER
  • CREATE JOB
  • CREATE PROFILE
  • CREATE ROLE
  • CREATE TABLESPACE
  • CREATE USER
  • DROP ANY ROLE
  • DROP PROFILE
  • DROP TABLESPACE
  • DROP USER
  • GRANT ANY OBJECT PRIVILEGE
  • GRANT ANY PRIVILEGE
  • GRANT ANY ROLE
  • SET CONTAINER

Object Privileges

We use the following SQL statement to check the object privileges in EM_EXPRESS_ALL.

SQL> select privilege || ' on ' || owner || '.' || table_name privilege from dba_tab_privs where grantee = 'EM_EXPRESS_ALL' order by owner, table_name, privilege;

There're 2 object privileges granted to EM_EXPRESS_ALL.

  • EXECUTE on SYS.DBMS_AUTO_SQLTUNE
  • EXECUTE on SYS.DBMS_AUTO_TASK_ADMIN

To have a full picture of EM_EXPRESS_ALL, you should additionally check EM_EXPRESS_BASIC for more details.

Leave a Reply

Your email address will not be published. Required fields are marked *