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):
create user DBPERFUSER identified by mysuperscreetpassword;
grant create session to DBPERFUSER;
grant select any dictionary to DBPERFUSER;
grant execute on dbms_workload_repository to DBPERFUSER;
grant oem_advisor to DBPERFUSER;
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).
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.
I need to add the target database for granting target privileges. I clicked the pen icon to go the target privileges page.
In the target privileges page, I granted “Manage Database Performance Privilege Group” to the EM administrator for the target database.
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).