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)

• Click Create and give it a name and description
• No ‘EM_’ role selected
• Target Privileges: select no ‘applicable to all targets’ selected, but add all the DB targets the user needs access to, at the bottom of the screen; select “Database Instance” and/or “Pluggable Database” and/or “Cluster Database” for easy seraching
• 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 (next)
• 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!