May 16

Howto create a readonly user on 12c Cloud Control

This post is also available in: Português

In this post, we are demonstrating how to create a user that has view only permissions in Oracle Enterprise Manager 12c. The aim is to give it to any team or user that can check all monitorable objects, but can not make any changes to it. The focus is on performance analysis, monitor and availability.

So let's start.

To create the user, open the main screen of the Oracle EM 12c and go on "Setup-> Security -> Administrators".

On the screen that opens click the "Create" button. There will come a new screen, fill the desired data.

Here are suggested one of two security options:

  1. Create a password and expire it, so the user will have to change it on the first access.
  2. Create a random password, long and complex and prevent it from being changed.

On the next screen, leave only permissions in roles "PUBLIC" and "EM_USER".

On the next screen, if you need the new user to access on all objects, check the "View any Target." It is interesting to also check the option "Connect to any viewable target" whether to allow the user to run performance queries on database (if it has a user to connect to the database).

You can also set individual targets rather than allowing any target.

On the next screen, there is no need to modify anything and just proceed.

Finally, check the information and click "Finish".

Ok, the new user is ready to view their targets.

 

Have you enjoyed? Please leave a comment or give a 👍!

32 comments

Skip to comment form

    • Jatin on August 4, 2014 at 21:45
    • #
    • Reply

    that is quite easy to set up but the trivial part is how would you prevent this user to have database login only without access to host?

    1. I think that is still impossible. If a user has privilege to view a Database Instance, EM 12c automatically gives access to it's host. =/
      I'm researching any way to avoid that and will give you feedback if I find!

        • Andrea on April 14, 2016 at 12:15
        • #
        • Reply

        Do you ever find the solution? I have to do the same too. I need to create a user that can see cluster db and database instances but not the host.. And I'm not finding the solution..

        1. SIR,
          my issue is oracle 11g 64 bit, not able to creating em Console(enterprise management console) in server 2016 operating systems

    • robin upcott on September 25, 2014 at 13:19
    • #
    • Reply

    My issue is slightly harder - I need this read only user to also be able to blackout any target (including ones just discovered as I dont want to be constantly updating any lists)

    • Abhi on September 10, 2015 at 23:50
    • #
    • Reply

    Hello Rodgiro,

    Thanks for the wonderful presentation but I would like to know one thing in the step 2,the password that is being provided.What password do we need to provide?If we give random password it says password is invalid:(

    Could you please help on these...

    1. Hi Abhi,

      When you create a user in EM, the user is actually created on the DB server where OEM metadata is stored. So, it must obey the password function rules of this database. Try putting upper, lower, punctuation and numbers.

      Regards,
      Rodrigo

    • Sathish Kumar D on October 7, 2015 at 14:29
    • #
    • Reply

    Hello,

    I want to create a user id in oem 12c for my unix administrator .

    We are monitoring exadata databases and what privileges are required to be granted to him.
    I dont want to give super admin privs.

    Could you let me know what are the privs are required by a unix admin to monitor exadata appliance.

    1. Hi Sathish,

      Grant only the privilege to view your exadata host target, nothing else.

      Regards,
      Rodrigo Jorge

        • Sathish Kumar D on October 8, 2015 at 11:32
        • #
        • Reply

        Hello RJ,

        Thank you.

        I can see view any target privilege.
        How can I restrict to view exa host alone ?

        1. Hi Sathish,

          In the "Target Privileges" tab while creating a new user, down in that page you have the option to add specific targets. Add your host there and give only the "view" option to the user. Do not select "view any target privilege" or any other privilege that is applicable to all targets.

          Regards,
          RJ

            • Sathish Kumar D on October 9, 2015 at 12:32
            • #

            Hello RJ,

            Thank you 🙂

    • Surya on March 8, 2016 at 13:07
    • #
    • Reply

    Hi,

    I want to create a user in OEM 12c, who can login to any database target but can have the privilege of only altering user passwords. Is it possible?

    Thanks,
    Surya

    1. Yes, create a user in EM with connect target privileges (let's name it EMUSER). Then, create a user in each database that this EMUSER user needs to connect (let's name it DBUSER).
      This DBUSER will be restricted to create session privilege. There is no specific grant in a database that only allows altering some user password, so you will need to grant "ALTER USER" (much more powerful) to DBUSER or create a procedure in some DBA schema that will receive some schema_name, user password, and grant this procedure to DBUSER.

      Regards,
      Rodrigo J

    • Edwardter on August 24, 2016 at 19:25
    • #
    • Reply

    Hi, I have installed Oracle cloud control 13c on RHEL 6.3. And I have creates some read only administrators by grating the default roles(EM_USER & Public). Now if they need to access the options under Performance tab (like SQL monitoring, AWR report, SQL Tuning sets..)what privilege do I need to grant these users?

    Fyi.. I have enabled the management packs and created user account(xyz) to access database targets without using sysdba, and granted this user the following privileges

    create user xyz identified by ******;

    grant select any dictionary to xyz;

    grant create session to xyz;

    grant execute on dbms_workload_repository to xyz;

    grant oem_advisor to xyz;

    Please share your thoughts and let me know if you need any more information help me with this issue.

    thanks

    1. Hi Edwardter,

      I usually create a DB user that I call "EM_CONSULT_USER" and only give "create session" and "select any dictionary" perms.
      I also do not share the user password with the EM account who is logged in. I create a named credential inside EM and give perms to "View Credential" to the EM accounts that need to do so.

      Regards,
      Rodrigo

        • Vladimir on April 11, 2017 at 18:31
        • #
        • Reply

        Hi Rodrigo,
        I need a DB user who can access Performance Page only.
        select any dictionary seems to be too powerful. As well as select_catalog_role.
        Do you know specific object privileges(v_$active_session_history, etc.) that this DB user needs?
        Thank you
        Vladimir

        1. Hi Vladimir,
          Oracle does not provide that privilege list, but you can try to track it and it's easy.
          Create a temporary user with select_catalog_role and enable audit select on all objects to that user.
          Then use this account to login and just open Performance Page sections you would like your user to have access.
          Finally take a note of the list of internal tables and views that this temporary user accessed and drop him later.
          Now you have the privileges necessary to access the Performance Page.
          Regards,
          RJ

            • Vladimir on April 12, 2017 at 11:37
            • #

            All genius is simple! Muito obrigado!

    • Brian B on September 26, 2016 at 15:55
    • #
    • Reply

    Hi Rodrigo,

    How can i give an OEM 13c user specific privileges just to create new OEM administrators? This user shouldn't be able to modify OEM roles or perform any other function in OEM. Thanks, Brian

    1. Hey Brian,

      Well, only "super administrator" accounts are able to create roles and administrators for your organization and this cannot be divided. I do agree that EM privileges and roles should be better fine-grained to allow that level of division.

    • adam hu on August 22, 2017 at 12:18
    • #
    • Reply

    I created the read only user in OEM however i can't run sql tuning advisor without giving the user SELECT ANY TABLE on the target database. Do you know a way around giving this permission on the target db?

    1. Hi Adam,
      Usually all SQL Tuning Advisor tasks are done via DBMS_SQLTUNE API. To use the APIs, the user needs at least the ADVISOR privilege.
      Try giving only that privilege to the user and see if that works.
      Thanks,
      RJ

    • Lancerique on December 15, 2017 at 20:00
    • #
    • Reply

    Hi RJ,

    Thanks for the article.

    I need to create a user for one of the application folks so that they can login and perform switchover/failover automatically without DBA intervention. What privileges do I need to provide.

    1. Hi Lancerique, sorry for late response.

      You need a very powerful privilege that only sysdg (if you are in 12c) or sysdba (11g or less) would provide.
      If you don't care giving them that privilege, create a db user with it and start with "Configure target" OEM privilege and test. If it doesn't work, go to "Full Target" priv on OEM.

      As sysdg and sysdba are extremely powerful grants, I would recommend creating a shell script or pl/sql API and grant your app folks privilege to execute that script (eg, with sudo, grant execute, etc). This would be a much more secure approach.

      Thanks,
      Rodrigo

    • subbu on April 29, 2018 at 08:48
    • #
    • Reply

    HI I want to create a user and should be able to view only specified target. example. i have added microsoft sql server. THen i will create a new user and that user login into em console should see only sql server target types..

  11. Hi Rodrigo!

    Are you still working with EM?
    We installed Em 13c.
    I try to convert my 12c tools to 13c.
    In 12c I could create the readonly user and create some nice views for access of some external applications.
    With 13c the sysman user got "editional" and all you get is ORA-38818.
    At the moment Oracle support does not help and only states:

    Cause
    ORA-38818: illegal reference to editioned object SYSMAN.MGMT$TARGET_PROPERTIES

    38818, 00000, "illegal reference to editioned object %s.%s"
    // *Cause: An attempt was made to violate the rule "A noneditioned object
    // may not depend on an editioned object."
    // *Action: Either make this object editioned; or do not make the illegal reference.

    Solution
    Workaround is to Re-create the referenced objects in a schema where editioning is not turned on

    Have you got a solution for this behaviour?
    I do not think it is reasonable to create sysmans objects again 🙂

    Regards
    Andreas

  12. Solution for my question is sooooooo easy if you know.
    Just enabele Editions for the read-user.
    And by the way, you can create the user with SQL-only methods.

    Here is my user creation as an example:

    -- Create Oracle Enterprise Manager Cloud Control 13c (EM 13c) Read Only User

    CREATE USER "OEMREAD"
    IDENTIFIED BY xxxxxxxxx
    DEFAULT TABLESPACE "MGMT_TABLESPACE"
    TEMPORARY TABLESPACE "TEMP"
    QUOTA 512 M ON "MGMT_TABLESPACE"
    ACCOUNT UNLOCK
    /

    -- allow logon
    GRANT "CONNECT" TO "OEMREAD";

    -- ignore Fine Grained Access Control
    GRANT EXEMPT ACCESS POLICY to oemread;

    -- EM 13c has editions enabled
    -- Do the same to avoid
    -- ORA-38818: illegal reference to editioned object SYSMAN.MGMT$...
    ALTER USER OEMREAD ENABLE EDITIONS;

    -- Grants to create needed objects
    grant create view to oemread;
    -- grant table to oemread;
    -- grant index to oemread;

    -- Direct object grants for view creation
    grant select on SYSMAN.MGMT$... to oemread;
    grant select on ...

    • Sahar on March 26, 2024 at 12:20
    • #
    • Reply

    Hi thank you for the article i want to know what the equivalent SQL Queries we need to run to get the same results if we want only 'View any Target' .

    Tried the following:
    GRANT SELECT ON SYSMAN.MGMT$HW_CPU_DETAILS TO SAHAR;
    GRANT SELECT ON SYSMAN.MGMT$METRIC_DETAILS TO SAHAR;
    GRANT SELECT ON SYSMAN.MGMT$METRIC_HOURLY TO SAHAR;
    GRANT SELECT ON SYSMAN.MGMT$METRIC_DAILY TO SAHAR;
    GRANT SELECT ON SYSMAN.MGMT$ALERT_HISTORY TO SAHAR;
    GRANT SELECT ON SYSMAN.MGMT$TARGET_PROPERTIES TO SAHAR;
    GRANT SELECT ON SYSMAN.MGMT$DB_SGA TO SAHAR;
    GRANT SELECT ON SYSMAN.MGMT$DB_INIT_PARAMS TO SAHAR;
    GRANT SELECT ON SYSMAN.MGMT$TARGET TO SAHAR;
    GRANT SELECT ON SYSMAN.MGMT$TARGET_ASSOCIATIONS TO SAHAR;
    GRANT SELECT ON SYSMAN.MGMT$OS_HW_SUMMARY TO SAHAR;
    GRANT SELECT ON SYSMAN.MGMT$TEMPLATE_METRICCOLLECTION TO SAHAR;
    GRANT SELECT ON SYSMAN.CM$MGMT_ASM_DISKGROUP_ECM TO SAHAR;

    but my results are empty

Leave a Reply

Your email address will not be published.