RE: What DB privs are required to read an EM13r2 SYSMAN view in 12.2?
Date: Mon, 11 Dec 2017 07:48:33 +0000
Message-ID: <20412_1512978523_5A2E385B_20412_405_1_ECDEF0CC6716EC4596FCBC871F48292AB1920D13_at_ZRH-S231>
Use “exempt access policy” to bypass the VPD.
Nenad
Twitter:_at_NenadNoveljic
Home page: http://nenadnoveljic.com/blog/
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich J
Sent: Freitag, 8. Dezember 2017 17:32
To: Oracle L
Subject: What DB privs are required to read an EM13r2 SYSMAN view in 12.2?
Hey all,
I'm migrating my APEX EM reporting app from EM12r5 with a v12.1.0.2 repository DB to EM13r2 with a v12.2.0.1 repository. The APEX app reads views in the SYSMAN schema of the v12.2 repository DB via database link. The DB link uses an account I created called APEX_REPORTING. Since upgrading to EM13r2/v12.2, that account no longer returns rows from the SYSMAN views.
I've traced it down to the SYSMAN.EM_MANAGEABLE_ENTITIES view. This view's only dependency is table SYSMAN.EM_MANAGEABLE_ENTITIES_E. In troubleshooting this, I've granted the APEX_REPORTING account SELECT ANY TABLE (also tried SELECT ANY DICTIONARY and the new READ ANY TABLE) as well as explicit SELECT on the SYSMAN view and table above:
GRANT MGMT_USER TO APEX_REPORTING;
ALTER USER APEX_REPORTING DEFAULT ROLE ALL;
GRANT SELECT ANY TABLE TO APEX_REPORTING;
GRANT SELECT ON SYSMAN.EM_MANAGEABLE_ENTITIES TO APEX_REPORTING;
GRANT SELECT ON SYSMAN.EM_MANAGEABLE_ENTITIES_E TO APEX_REPORTING;
A "SELECT *" on the view returns no rows, while 163 rows are returned from the table. If I grant DBA to APEX_REPORTING, all 163 rows are returned from the view. I'd prefer to not grant DBA to this account, but I'm struggling with why the above doesn't work. Some new security feature in v12.2 that I have yet to learn?
Thoughts?
Thanks,
Rich
Please consider the environment before printing this e-mail. Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">p { font-family: Arial;font-size:9pt }</style>
</head>
<body>
<p>
<br>Important Notice</br>
<br>This message is intended only for the individual named. It may contain confidential or privileged information. If you are not the named addressee you should in particular not disseminate, distribute, modify or copy this e-mail. Please notify the sender immediately by e-mail, if you have received this message by mistake and delete it from your system.</br>
<br>E-mail transmission may not be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also processing of incoming e-mails cannot be guaranteed. All liability of the Vontobel Group and its affiliates for any damages resulting from e-mail use is excluded. You are advised that urgent and time sensitive messages should not be sent by e-mail and if verification is required please request a printed version.<br/>
</p>
</body>
</html>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Dec 11 2017 - 08:48:33 CET