One of the key features for Database administration in Enterprise Manager 12c is the Performance pages utilizing the Diagnostic and Tuning packs. The DBAs are very familiar with these. Many customers are starting to ask how to let their Developers access the performance data without allowing full access to production.

Database Authentication

Even if a user has Super Admin permissions in EM, it doesn’t grant them access to the database target at all. With View Target permission on a database target, a user can get to the main target screen, as seen below:

As soon as they try to dig further into the SQL or Schema, they will be presented with a login box.

The EM user must have Connect/Connect Target Read-only privilege to get a login prompt to a target.   You can either use a Named credential, or create a New credential.  If the EM user has access to Named credentials, they will be able to save the credential and set that as a Preferred credential for this target.  If the Preferred credential is set, the login will be automatic from now on.

From here, what the user can do is completely dependent on what that login was.   Do you have sys as sysdba logins?  If so, you can do just about everything.   Or do you have a restricted read-only database account that limits your actions?   Whatever database account permissions were set are still enforced through EM.   So the question is… what permissions do you need?

Database Authorization

As with any user account in an Oracle database, you have to specify explicit privileges or roles to be able to access certain pieces. Just as you would have to grant select on X table, you have to grant access to specific performance views and functions.

Performance Views

For basic view permissions on the Performance pages, the user needs at a minimum create session and select any dictionary.  If you were able to login, then you already have create session.   So let’s grant select any dictionary.

SQL> grant select any dictionary to scott;

Now when we go to the Performance Home page, we will see the data as below:

AWR

That works great for the Performance pages, but  let’s say you want to run an AWR report, can we do that?

Nope.  As you can see, we get an error that execute privilege on DBMS_WORKLOAD_REPOSITORY is required for this function.  So let’s grant that:

SQL> grant execute on dbms_workload_repository to scott;

Now you can successfully run ASH and AWR reports from EM.

SQL Access Advisor

SQL Access Advisor has additional permissions at the database level that are required.  SQL Access Advisor also submits a DBMS Scheduler job on the database, so we also need access to create jobs.  Without this persmision you’ll get an error as follows:

SQL> grant oem_advisor to scott;
SQL> grant create job to scott;

Now when we submit the SQL Access job, we get a Confirmation message that links to the Scheduler Job :

 

 SQL Tuning Advisor

For SQL Tuning Advisor, you’ll need to create a SQL Tuning Set and have permissions to dbms_workload_repository and administer the SQL Tuning sets.

SQL> grant execute on dbms_workload_repository to scott;
SQL> grant administer sql tuning set to scott;

Once you have these additional permissions, you’ll be able to successfully create a SQL Tuning Advisor job and receive the results.

 

These are some of the more common performance tuning privileges required to use the features through EM, however there are a lot of other features that could be accessed.   Depending on what functions the user requires, you may need additional permissions at the database level.    For additional privileges required, see Where to Find Information About Performance Related Features (Doc ID 1361401.1).

Hopefully this short blog will help you start to think about what roles and permissions your database users might need in order to make full use out of the Oracle Enterprise Manager Performance pages!