- Enterprise Manager 12c (EM) installed and agents rolled out to database servers
- Access to EM offered to development teams with the primary purpose of allowing them to investigate application related database performance issues
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:
- SELECT ANY DICTIONARY
- CREATE SESSION
- EXECUTE on DBMS_WORKLOAD_REPOSITORY
It interests me that OEM_MONITOR is is not mentioned.
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.
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.”
Quite reasonably a user with only CREATE SESSION hits the following when attempting to access a database target:
Creating an exact copy of SELECT_CATALOG_ROLE, named EM_SELECT_CATALOG_ROLE, and granting this to a user results in the same error. This points to the role rather than the underlying privileges being checked.
The following test was carried out:
- Create a guaranteed restore point
- Drop the SELECT_CATALOG_ROLE role
- Create an empty SELECT_CATALOG_ROLE role
- 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.
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:
- select sign(count(*)) from session_privs where privilege = ‘SELECT ANY DICTIONARY’
- select sign(count(*)) from session_privs where privilege = ‘SELECT ANY TABLE’
- 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.
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?
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 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.
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.