Create READ ONLY + AWR access on database targets in Oracle Enterprise Manager

A critical application is recently having a creepy behaviour in production, so its developers are willing to understand what is going on in the database and troubleshoot in an effective way. Let’s give them access to all the databases related to this application throught Oracle Enterprise Manager

The following procedure is mainly relevant with this version, as Oracle Enterprise Manager 13c has a new set of privileges that should be more appropriate.

Administrator creation

Connected as a super administrator in Oracle Enterprise Manager, let’s create a new administrator in Setup > Security > Administrators by clicking the “Create” button :


After clicking “Next”, the roles page is displayed. As I am not sure what privileges I want to give this user for the moment, I’ll use the principle of least privilege and give her … nothing.


The next page is dedicated to target privileges. I am being really careful, I don’t affect any targets for the moment and click “Next”. Likewise, I leave the “Resource Privileges” as is and click “Next”. Finally, when we review the user before creation, we are sure she cannot do any harm. For the moment, at least.


Now, her co-worker would like to have access to the same targets through Enterprise Manager as well. The easiest way to make sure they always have the same privileges is to create a role and grant it to both users.

Role creation

In Setup > Security > Roles, let’s click “Create”. The first thing to do is to grant the EM_USER role to allow users to access Enterprise Manager Application.


After clicking “Next”, the “Target privileges” page offers many choices, but the only thing we want is for our users to connect to a few relevant databases only. So let’s tick “Connect to any viewable target” :


At the bottom of this page, I can finally select the targets I want the users to access. In this case, I select databases related to this dysfunctional application :


Next, we have the “Resource Privileges” page, but let’s be cautious and skip this page, as we don’t need anything particular. Finally we can grant this new role to users.

Good ! Wednesday wants to check the Top Activity page on fester database. So she logs in, she goes to Performance > Top Activity, and she is asked to enter a username and password :


Ouch … She needs a user to connect to the database. And we do not want to give her any password.

Database user creation

Now let’s create the same dedicated user on all the databases selected before (you could use emcli execute_sql for example ๐Ÿ™‚ ). And of course, we still want to apply the principle of least privilege :


The documentation suggests to grant SELECT ANY DICTIONARY privilege but I’d rather grant the SELECT_CATALOG_ROLE as it is more restrictive. Please check this interesting blog post by Arup Nanda about the differences between SELECT ANY DICTIONARY and SELECT_CATALOG_ROLE.

But still … we do not want to share the password. What solution do we have ?

Named credentials creation

With Setup > Security > Named Credentials > Create Credential, I create a named credential for OEM_ROAWR_ACCESS on the database targets.


So now, to allow Wednesday and her co-worker to connect to the databases without knowing the password of OEM_ROAWR_ACCESS, we just have to grant them access to OEM_ROAWR_CREDENTIAL. Let’s go to Setup > Security > Named credentials, select OEM_ROAWR_CREDENTIAL, click “Manage Access” and “Add Grant” :


And now they can connect easily to the different databases …


… and have access to a spooky “Top Activity” page for example :


Great ! Now Wednesday wants an AWR report to check if some scary phenomenon happened around midnight :


Looks like she can’t. Why ?

AWR access

According to the official documentation, OEM_ROAWR_ACCESS must have execution privilege on DBMS_WORKLOAD_REPOSITORY to generate an AWR report :


The report can now be generated. But it also means she can, for example, modify the AWR snapshot settings …



I am far from being a security expert so I would be glad if you have suggestions to reinforce security regarding this usage of Oracle Enterprise Manager.

3 thoughts on “Create READ ONLY + AWR access on database targets in Oracle Enterprise Manager

  1. Pingback: Create READ ONLY + AWR access on database targets in Oracle Enterprise Manager – EM CLI edition – Floo Bar

  2. laurentschneider

    If you give them access to em, they will start complainong when it is down, with any upgrade and so on…

    By far I prefer instructing my users to use AWR in SQL developer ๐Ÿ˜Š



    1. florab Post author

      Hi Laurent !
      Haha, I promise they are trained to use OEM in a “healthy” way ๐Ÿ˜‰ In fact, they are really eager to know what really happens on the database side. It increased their awareness (and decreased the number of request towards DBA team ๐Ÿ™‚
      Using AWR in SQL Developer is a great idea, especially because many developers are used to this tool already. Thank you for the tip, I’ll think of it next time.

      Liked by 1 person


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 )

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