Creating EM Users for Performance Tuning

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:

dbuser1

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

dbuser2

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:

dbuser6

AWR

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

dbuser3

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;

dbuser4

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:

dbuser5

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 :

dbuser7

 

 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.

dbuser9

 

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!

ECO 2014 – Zero to Manageability

I had the pleasure of presenting to the East Coast Oracle Users Group conference in Raleigh, NC on November 4th.  I had a great crowd with lots of interest and great discussions! Thanks to everyone who attended today! Slides can be downloaded here.

**Sorry for the delay, WordPress wouldn’t let me post links or images, and finally had time to sort it out! **

Notifications for Expiring DBSNMP Passwords

Most user accounts these days have a password profile on them that automatically expires the password after a set number of days.   Depending on your company’s security requirements, this may be as little as 30 days or as long as 365 days, although typically it falls between 60-90 days. For a normal user, this can cause a small interruption in your day as you have to go get your password reset by an admin. When this happens to privileged accounts, such as the DBSNMP account that is responsible for monitoring database availability, it can cause bigger problems.

In Oracle Enterprise Manager 12c you may notice the error message “ORA-28002: the password will expire within 5 days” when you connect to a target, or worse you may get “ORA-28001: the password has expired”. If you wait too long, your monitoring will fail because the password is locked out. Wouldn’t it be nice if we could get an alert 10 days before our DBSNMP password expired? Thanks to Oracle Enterprise Manager 12c Metric Extensions (ME), you can! See the Oracle Enterprise Manager Cloud Control Administrator’s Guide for more information on Metric Extensions.

Read more here