Oracle security "Select Any Dictionary" privilege

Oracle security \"Select Any Dictionary\" privilege

The "psft_role" being the standard role created during the JDE installation was assigned with "Create Session", "Create Table", "Create View", "Select Any Dictionary" privileges.

What is the purpose of having "Select Any Dictionary" privilege granted to psft_role? Which of the JDE users require this privilege? psft or schema owners, or both?

Re: Oracle security \"Select Any Dictionary\" privilege

It seems The E1 middleware tries to be a little smart when dealing with an Oracle DB. Prior to 9i many of the "$" views were available for query by almost any DB user and this system privilege did not exist. The proxy user is making use of "SELECT ANY DICTIONARY" privilege to get at these views. What specifically it is looking at in the SYS V$ views I haven't evaluated.

I have a number of Oracle sites where we have locked down security pretty tightly for the proxy user. As a result I have spotted interesting errors in jde.logs of certain batch jobs. For instance, there is one in particular that tries to execute a COMPUTE STATISTICS on its driving table before each run! So while it has always been claimed that E1 is DB neutral it is clear that some native Oracle specific features are being used in some way. Even in the case of these otherwise secure instances SELECT ANY DICTIONARY has been left granted. We haven't had the time to test for an impact on performance or functionality when it is removed. If you decide to remove it I would be interested to hear if you have any issues. There may be no impact at all.

Re: Oracle security \"Select Any Dictionary\" privilege

JDE is looking for V$PARAMETER and V$SESSION views. If you revoke "Select Any Dictionary" privs, you should make sure you have at least 255 open cursors available. JDE sessions will default to 255 cursors if you restrict access to the views via "Select Any Dictionary".

Re: Oracle security \"Select Any Dictionary\" privilege

Oracle adviced not to change the "SELECT ANY DICTIONARY" privilege granted to psft_role. Their response didn't explained as details as yours.