Who Needs to be Super?

In the days past, everybody in EM used to end up with Super Administrator privileges due to lack of granularity in permissions.  Not any more!  Now we have more permissions than you know what to do with, but that’s another blog topic all together!

Here’s a quick list of activities that one might still need Super Administrator for – note these are all considered EM administration activities and most are accessed through the Setup menu:

  • Create Administrators – can get around this by using LDAP integration and auto-provisioning
  • Access EM Audit
  • Access Security Console
  • License Management
  • Connector Setup
  • Data Exchange
  • Add/Edit Registration Password
  • Configure Notification Mail Server
  • Configure Notification Mail Customization
  • Configure SNMP
  • Configure Global Repeat Notifications
  • Setup Privilege Delegation Templates (sudo/pbrun)
  • Decommission Agent (bug fix 19430853 will allow users with Full privileges to perform again)

If your user is not responsible for users and security, and doesn’t get paged when EM stops working, then they have no business with Super Administrator privileges or SYSMAN.

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!