EM13c: Performance Tuning Access to Read Only Administrators

About six months ago, when I was in the OUGN Spring Conference, someone asked me how they can let developers access to performance tuning pages on Enterprise Manager Cloud Control 13c. He said that they granted full administrator privileges to the developers so they could access the performance page but it’s not acceptable. He also mentioned that they didn’t need to grant full administration privilege to the developers’ EM users while they were using EM12c.

Unfortunately I forgot to examine the situation until I saw a similar question on OTN (Oracle Community) forum on Friday. The forum reader has exactly the same problem. The database user was already granted required privileges but the EM administrator see the menu items related with “performance tools” are grayed out.

Before going into further details, let me explain the difference between the EM administrator (user) and the database user. You login to the EM13c web console using an EM administrator. EM administrators are defined in the EM environment. When you want to manage (or monitor performance page) of a database target through Enterprise Manager, it will ask you to enter the credentials of a database user. So although you need only one EM administrator account, you need to have one database user for each database target. The named credentials feature can be used to store and share these database credentials in the EM repository.

Okay, so we want our non-administrative/read-only EM administrators (i.e. developers) access performance pages and tools of a database and we want to do it according to the principle of least privilege.

First, let’s create a database user on the target database. Following SQL script can create a database user (DBPERFUSER) who can access “Database Performance Page”, “AWR/ADDM” and Enterprise Manager advisors (such as SQL Access advisor, SQL Tuning advisor etc):

userproperties

After I created a database user on the target database, I login to EM13c web console, go to “security > administrator” page and create an EM administrator (user).

userroles

I grant only the default roles (EM_USER and PUBLIC). For now, this user can login to Enterprise Manager console but they can not monitor or manage any target.

addtarget

I need to add the target database for granting target privileges. I clicked the pen icon to go the target privileges page.

targetpriv

In the target privileges page, I granted “Manage Database Performance Privilege Group” to the EM administrator for the target database.

review

I review and create the EM administrator. The newly created administrator will be able to access the performance pages and tools for the specified database target if they login with the database user (DBPERFUSER).

Please share
  • 1
  •  
  •  
  •  
  •  
  •  

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

9 Comments

  1. Max

    Thank you for article. It is very helpful. Generally speaking it works but I have a little bit different situation. I created a group with 5 databases and I am trying to do the same configuration as You. It looks fine until I am checkng on user account Performance tabs. None of them works. I mean all are grey. When I configured single DB it looks fine. Do You know why? Where did I mistake? Only difference I see is that  “Is Privilege Propagation Enabled” is marked YES for single DB (in Target Privileges ).

  2. Merlin

    Finally, I’ve been trying to figure this out and looking for days for instructions.. Thank you for this excellent post..

  3. Roberto

    Thanks a lot, exactly matching what I was trying to achieve, really helpful.

  4. adam hu

    I keep getting “ORA-00942: table or view does not exist” after executing sql tuning advisor, unless i grant “select any table” in the target database which i prefer not to.

    Any suggestions?

     

    • Chida

      Hi Adam, were you able to resolve the error: ORA-00942: table or view does not exist while running the sql tuning advisor in EM 13c?

  5. foxescross

    Hi Gokhan Atil,

    Another helpful article thanks, however, how does this work with Multitenant Databases. I am creating the DBPERFUSER in the the PDB, but when I go to Performance Home in OEM it is asking me for credentials to the CDB…

    Hope you can help – thanks.

  6. Sunita

    Hi,

    We have a user in our CDB as C##xxx user. We have given all the privileges to run sql tuning advisor. But user is not able to run sql tuning advisor through OEM. Could you please suggest. We are getting the below error.

    Unexpected error occurred. Please contact Oracle Support with these files.
    /user_projects/domains//servers//logs/access.log
    /user_projects/domains//servers//logs/-diagnostic.log
    /em//sysman/log/emoms.trc

    Thanks,

  7. sri

    Hi,
    That was an wonderful article, but when i have created the read only user the user can create an incident rules is there any possibility that i can grey out that option so that he cannot create any incident rules

Leave a Reply to Max Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.