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

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 :

create_admin2

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.

create_admin3

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.

create_admin4

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.

create_role_1

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” :

create_role_2

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 :

create_role_3

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 :

login_db

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.

create_credential

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” :

manage_cred_access

And now they can connect easily to the different databases …

login_cred_access

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

top_activity

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

err_awr_package

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 …

edit_awr.png

 

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 12.1.0.5

  1. 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 ๐Ÿ˜Š

    YMMV

    Like

    1. 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:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google 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