1. Why does PA DB user on Monitored DB need SELECT ANY TABLE privilege?
2. There is a "SELECT ANY TABLE" in the quest_mon_privs.sql
Is it ok to remove this statement?
3. Cannot grant 'SELECT ANY TABLE' permission to PA for security reasons
4. How the lacks of 'SELECT ANY TABLE' affect PA?
This privilege is used By Change Tracking component of PA Repository process for obtaining the execution plans of collected SQL statements. Every day once (by default midnight), PA Repository process submits all the collected SQLs (of last 24 hours) to Change Tracking and gets the execution plan for each SQL statement by running Explain Plan. This method (Explain Plan) will not succeed if SELECT ANY TABLE privilege is not granted to PA User on Monitored DB. Hence this privilege is required. If you choose to revoke this privilege, PA Change Tracking process will not be able to get execution plans for the collected SQL statements.
But if you want to revoke this privilege, it is ok as long as you understand the impact on Change Tracking. If you choose the option of “do not run Explain Plan* and instead get the execution plan from v$SQL_PLAN , you can revoke SELECT ANY TABLE privilege.
To retrieve Explain Plan from V$SQL_PLAN, go to Agent Admin > Performance Repository > Change Tracking and Retrieve plan only from V$SQL_PLAN.