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 12.1.0.5.

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 :

CREATE USER OEM_ROAWR_ACCESS IDENTIFIED BY ILikeHalloween;
GRANT CREATE SESSION TO OEM_ROAWR_ACCESS;
GRANT SELECT_CATALOG_ROLE TO OEM_ROAWR_ACCESS;
ALTER USER OEM_ROAWR_ACCESS DEFAULT ROLE ALL;

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 :

GRANT EXECUTE ON DBMS_WORKLOAD_REPOSITORY TO OEM_ROAWR_ACCESS;

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.