Read Only Users and Database Performance

One of the questions we see being asked reasonably frequently is how to allow read only access to someone who wants to view database performance issues, using tools such as Real Time ADDM, ASH Analytics and so on. This is generally asked for someone like an application developer, who can use this information to identify performance issues in Production and then fix the root cause in their Development environments.

Well, that’s a reasonable question to ask, and indeed if you search the internet you can find different ways of doing something like this. For EM12c, one of the best write-ups I’ve seen done on this was of course by one of my colleagues, Courtney Llamas. That write-up is available here.

So much for EM12c – what about EM13c? Well, you’ll be happy to know it’s even easier in EM13c, because that release introduced new functionality in the database plugin to deal with this exact issue. The functionality is called “Flexible Database Access Control”. This provides a privilege control model for database pages in Enterprise Manager Cloud Control 13c, allowing you to create different roles with specific privileges for different levels of database access such as:

  • Application DBA Access
  • Application Developer Access
  • Database Monitoring User Access
  • Database Administrator Access

So what exactly would these different roles do? You can see them defined in the documentation as:

  • Application DBA – An application DBA is a restricted database administrator who manages application schemas, application objects, and application performance in the database. An application DBA should be able to find and fix application performance issues in the database. An application DBA is responsible for keeping the application up and running and in good performance.
  • Application Developer – An application developer is a person who develops an application. Application developers capture requirements from customers and develop the application according to customer requirements. Application developers use Oracle Enterprise Manager to tune SQL in their application modules for optimal performance in production environments. Application developers are responsible for the modules of the application in development, test, and production environments.
  • Database Monitoring User – The database monitoring user monitors the database for smooth functioning of the application in production environments. Monitoring users respond to alerts raised in the Enterprise Manager environment. Monitoring users can update the schedule of metrics and setup blackouts on the databases. Monitoring users are not allowed to make any changes to the production database. Monitoring users ensure that the application is up and running by responding to any issues reported and ensuring that the issues are assigned to the DBAs responsible for resolution.
  • Database Administrator – Database administrators performs full database lifecycle management including installation configuration, monitoring, backup, recovery, and performance tuning.

One of the neat things about Flexible Database Access Control is that it not only solves the issue we started talking about (setting up read only access for performance issues), but it also provides a way of implementing higher levels of security at the same time – twice the bang for your buck! For all the details on how to set these roles up and grant them to relevant users in EM13c, have a look at section 4.1 of the Enterprise Manager Cloud Control Security Guide.

Hope you find this short tip useful!

Pete

After 22 years of working at Oracle in just about every role except Marketing and Support, I am now working as a Senior Managed Services Consultant with Red Stack Tech, specializing in Oracle Database technology, High Availability and Disaster Recovery solutions.

I am also a member of the OakTable Network, and have presented at RMOUG Training Days, Hotsos Symposia, Oracle OpenWorld conferences, and other user group events. I have co-authored the Expert Oracle Enterprise Manager 12c and Practical Oracle Database Appliance books published by Apress, and am one of the authors of the Building Database Clouds in Oracle Database 12c book published by Addison Wesley.

8 Comments:

  1. Hello Pete,

    If you don’t mind I’d like to ask a question that has an indirect relation to this blog post.
    When I first started using Oracle 12c Multitenant Option together with OEM 12c Cloud Control, I was a little bit disappointed with the restriction documented in “Unable to View Performance Metrics Within OEM 12c Cloud Control at the PDB Level (Doc ID 2117216.1)” note.
    In short, I have to logon in a container database as a common user in case I want to view a PDB’s Performance Pages through OEM 12c Cloud Control.
    I’m still using OEM 12.1.0.5 Cloud Control now and I wondered whether this restriction is still present in OEM 13c Cloud Control.
    Is it possible to view a PDB’s Performance Pages, such as the Top Activity page, through OEM 13c Cloud Control when logged as a PDB user?

    • Mikhail

      I’ve asked my colleagues in the DB Management space to provide an answer on this as I don’t have a configuration that I can look at this exact issue with in 13.1. I suspect the original problem may be because 12.1.0.5 came out before Oracle Database 12c, so the database plugin that manages database functionality doesn’t have an understanding of multitenant. While I’m waiting for my colleagues to respond, can you verify what database plugin version you are using? If it’s not the latest version, please update it and see if it has the same issue. Of course, I could be completely wrong on that point. 🙂

      Pete

  2. Pete

    I can’t upgrade my 12.1.0.5 system right now but I decided to check if something has changed in 13.1.
    To do so I downloaded a VirtualBox template of 13.1 from edelivery, imported it, and added a new 12.1 Container database as a monitored target.
    I created a new user in a PDB and granted him OEM_MONITOR role.

    SQL> conn test/test@’localhost:1521/pdb1′
    Connected.
    SQL> select * from session_roles;

    ROLE
    ——————————————————————————–
    OEM_MONITOR
    SELECT_CATALOG_ROLE
    HS_ADMIN_SELECT_ROLE

    Then I proceeded to the Cloud Control web interface.
    I selected: Targets->Databases->selected a PDB->Performance->Top activity.
    After I clicked the “Top Activity” link, a login page appeared where the field with the name of the container database was grayed and I wasn’t able to change it in any way: “orcl (Container database)”.
    I forgot to mention – orcl – is my container database and PDB1 is a PDB in it.
    I wasn’t able to login using test/test as a username/password pair, but I performed successful login as a user within CDB$ROOT container.
    Then I decided to apply the latest plugin updates and patched my OMS applying Patch 24546113: OMS System Patch 13.1.0.0.160920.
    The issue wasn’t resolved even after I applied this patch.
    So it looks like Enhancement Request: “20796692 – NOT ABLE TO ACCESS PERFORMANCE PAGES AT THE PDB LEVEL”
    hasn’t been implemented yet or, at least, it still is not present in 13.1.

    • Thanks for checking Mikhail. I’ve been off dealing with a family medical issue, so I bounced your feedback across to the relevant PM today. He has been dealing with a lot of post OOW stuff so hopefully he will get a chance to look at it soon (maybe even in 13.2 which you may have noticed we announced today!)

Leave a Reply

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