Error rendering element. Exception: ORA-01031: insufficient privileges ORA-02063: preceding line

Today one of my 12c database OEM report Failed with below error:

Error rendering element. Exception: ORA-01031: insufficient privileges ORA-02063: preceding line from MYPROD

sql executing from OEM report is ........

SELECT distinct du.username, du.created, du.lock_date, du.account_status, du.expiry_date, u.ptime PW_LAST_CHANGE_DATE, dr.granted_role

FROM dba_users@MYPROD du, sys.user$@MYPROD u, sys.dba_role_privs@MYPROD dr

WHERE du.username=u.name and dr.grantee=du.username order by username

Went to OEM repo database and tried to test below sql

Check the DBLINK DDL:

CREATE PUBLIC DATABASE LINK MYPROD  CONNECT TO SYSTEM  IDENTIFIED BY <Password>  USING 'myprod';

Login to sqlplus and check the same views using db links

OMSSERVER: REPODB:/home/oracle $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 1 17:25:30 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select count(*) from dba_users@MYPROD;   --> Worked....

  COUNT(*)

----------

        20

SQL> select count(*) from  sys.dba_role_privs@MYPROD;  --> Worked....

  COUNT(*)

----------

        99

                               

SQL> select count(*) from sys.user$@MYPROD;   --> Didnt Work, so here is the issue

select count(*) from sys.user$@MYPROD

                           *

ERROR at line 1:

ORA-01031: insufficient privileges

ORA-02063: preceding line from MYPROD

SQL> select count(*) from sys.user$@MYPROD;

  COUNT(*)

----------

        72

                               

Reason:

From 12c, "SELECT ANY DICTIONARY" Privilege No Longer Accesses Some SYS Data Dictionary Tables

Oracle Doc says

“For better security, the SELECT ANY DICTIONARY system privilege no longer permits you to query the SYS schema system tables such as DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, and XS$VERIFIERS. Only user SYS has access to these tables, but user SYS can grant object privileges (such as GRANT SELECT ON USER$ TO sec_admin) to other users. “

Solution:

grant select on user$ to SYSTEM;

Now run the report from OEM and it Works !!!

Reference:

EM 12c : Reports Metric Evaluation Errors "ORA-01031: insufficient privileges" (Doc ID 2159658.1)

https://docs.oracle.com/database/121/DBSEG/release_changes.htm#DBSEG000