The SELECT ANY DICTIONARY privilege (or, in earlier versions the SELECT_CATALOG_ROLE role) gives a user privileges to select from any data dictionary table.
The SELECT ANY DICTIONARY privilege would give a developer privileges to write whatever queries they'd like against DBA_SOURCE to see the source for any object, DBA_VIEWS to see view definitions, etc. But there is no guarantee that a particular front-end would actually leverage those privileges correctly. Far too many GUIs simply select from the ALL_* data dictionary tables to display the objects that a user has object privileges on rather than recognizing that the user has permission to select from the DBA_* data dictionary tables. Personally, I'm more than happy to query the DBA_SOURCE view (or use the DBMS_METADATA package) when I want to look at object source in a production database but lots of folks get antsy without the GUI schema browser.
Benoit, you actually had a partial answer, everything you said was correct, but after next reboot, the account locked. After several hours of more research, the following issue was discovered, with the appropriate resolution.
Enterprise Manager had the default SYSMAN password stored for its credentials, and was trying to lock in with that password in rapid succession (300 times a minute or so according to the Event Log). Hence, after several rapid tries with the same wrong password, Oracle locks the account.
Setting a new password in SQL*PLUS or SQL Developer doesn't reset the password used to login to Enterprise Manager, to reset the password in enterprise manager, you need to open a command prompt and do the following:
emctl config oms sso -remove
emctl stop oms
emctl start oms
During the above commands, it'll prompt you for the current SYSMAN password, which you can enter, and it should remove the SYSMAN password used by your Enterprise Manager Console, replacing it with the one you just entered.
Best Answer
You allow a non-admin user access to the Performance area of OEM by granting them the
OEM_MONITOR
role. However, be aware that this role does have a few additional priviliges that you may not want to provide. In that case I would create a similar role toOEM_MONITOR
but with a reduced set of privileges.