Add a DIAGNOSTIC user to Enterprise Manager for performance insights and query tuning only

When you want a user, probably a developer, to have access via Enterprise Manager to show query performance, but *ONLY* to specific databases and it’s performance stats, you need a ‘restricted’ EM and database user to do this. Next is tested on EM 13.3 and an 18c container database.

Short list of things that need to be done:

  1. Create a new Enterprise Manager Role with performance monitor privileges for specific targets
  2. Create new Enterprise Manager Administrators and add them to the previously create role
  3. Create a new common user in the target databases for monitoring within the target databases
  4. Create a new Enterprise Manager Named Credential for these targets

So that’s a short list, now the (written) instructions per step:

Create a new Enterprise Manager Role

From the main EM screen, go through these menus:
(Setup -> Security -> Roles)

  • No ‘EM_’ role selected
  • Target Privileges: no ‘applicable to all’, but select all the DB targets (bottom of screen), select “Database Instance” and “Pluggable Database”
  • Just above the populated list, click ‘Grant to All’ (or Grant to Selected), a new Target Privileges screen will open
  • In the privilege selection, search for ‘performance’ and select ‘View Database Performance Home Page’, ‘View Database Performance Privilege Group’ and ‘View Database SQL Performance Analyzer’ (press Continue)
  • The column ‘Manage Target Privilege Grants’ will show now the privileges
  • No ‘EM Resource Privileges’ selected
  • You can select existing admins to get this role

Create a new Enterprise Manager Administrator

From the main EM screen, go through these menus:
(Setup -> Security -> Administrators)

  • No Super Administrator selected
  • Select and add the previously created monitoring role
  • Select nothing else, it should all be included in the role

Create a new common user in the (container) database

Run the next statements on container level:

  • SQL> create user c##diagnostic identified by “LetMeIn!” [container=all];
  • SQL> grant create session, select_catalog_role to c## diagnostic [container=all];

Create a new Enterprise Manager Named Credential

This way, users do not have to know the password, as you save it as a named credential.

From the main EM screen, go through these menus:
(Setup -> Security -> Named Credential)

  • Authenticating Target Type: Database Instance
  • Credential Type: Database Credentials
  • Scope: Global
  • Username / Password: c##diagnostic / LetMeIn!
  • Grant Access Control of this credential
  • Add Grant View Credential (is default) to users now or later via ‘Manage Access’

Happy tuning!

Tagged , . Bookmark the permalink.

Leave a Reply

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