Define Your Own Role for Database Target Access in EM12c

Scenario

  1. Enterprise Manager 12c (EM) installed and agents rolled out to database servers
  2. Access to EM offered to development teams with the primary purpose of allowing them to investigate application related database performance issues

Documentation

The EM documentation covers a selection of privileges you might want to grant to users in database targets in order to allow them to be used for accessing EM functionality. The privileges mentioned are:

  1. SELECT ANY DICTIONARY
  2. CREATE SESSION
  3. EXECUTE on DBMS_WORKLOAD_REPOSITORY
  4. OEM_ADVISOR

It interests me that OEM_MONITOR is is not mentioned.

Target Credentials

The development team have access to the EM console (via Active Directory credentials) and in order to access the Performance Pages for a particular database they need to either know the username and password of a user that has sufficient privileges in the database or have access to a Named Credential that has sufficient privileges in the database[1].

Testing

Past experience has shown that CREATE SESSION and SELECT_CATALOG_ROLE allow access to the Performance Pages. Unfortunately the mention of granting SELECT_CATALOG_ROLE caused the security folk to say, “No, no, no.”[2]

Quite reasonably a user with only CREATE SESSION hits the following when attempting to access a database target:

EM - DB Target - Denied

Creating an exact copy of SELECT_CATALOG_ROLE, named EM_SELECT_CATALOG_ROLE, and granting this to a user results in the same error.[3] This points to the role rather than the underlying privileges being checked.

The following test was carried out:

  1. Create a guaranteed restore point
  2. Drop the SELECT_CATALOG_ROLE role
  3. Create an empty SELECT_CATALOG_ROLE role
  4. Grant SELECT_CATALOG_ROLE to test user

Connecting to the database target worked. Once connected there is a lot of functionality that is not available because the underlying object privileges are not granted to the user. This is something that can be addressed in order to enable the functionality that a given user is permitted to access.

Investigation

Enabling SQL Trace for a session connecting to a database target from within EM in order to verify the above testing shows the following SQL statements hit the database target:

  1. select sign(count(*)) from session_privs where privilege = ‘SELECT ANY DICTIONARY’
  2. select sign(count(*)) from session_privs where privilege = ‘SELECT ANY TABLE’
  3. select role from session_roles

The SQL alone doesn’t make it explicit what privilege validation is performed, but we can take a good guess based on the documentation and earlier test results.

Problem Summary

Connecting to a database target from within EM checks for a number of privileges, the least contentious of which for the security team is SELECT_CATALOG_ROLE, however, this is not permitted for developers. The problem remains: How do we move forward and get the development teams the access they will find so valuable?

Oracle Support

At the request of the client an SR was raised, which has turned into an enhancement request:

Ability to use an appropriately privileged custom role for access to a database target via Enterprise Manager 12c

The Hack

The enhancement request may not get very far and even if it does then it will take some time to reach this environment, so other options were considered…

The privilege check can easily be tricked by creating a view owned by the database target user:

create view <EM USER>.session_roles as
  (select role
     from sys.session_roles
    union
   select 'SELECT_CATALOG_ROLE'
     from dual);

In case it isn’t obvious, <EM USER> will now always appear to have SELECT_CATALOG_ROLE, at least to itself. This allows the EM privilege checks to be passed and the connection to the database target to be successful. Without additional privileges the user will get a lot of blank screens, however, if the user has the required privileges for a given page then EM works.

Footnotes

1 – Named Credentials are a great feature, however, be mindful that a user can execute ad-hoc SQL in EM via the SQL Worksheet screen.
2 – Whether or not granting SELECT_CATALOG_ROLE to developers is a reasonable thing to do or not is beyond the scope of the post.
3 – The intention was that privileges would be removed once there had been opportunity to discuss with the security team exactly which of the privileges SELECT_CATALOG_ROLE has that they did not want developers to have.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>