Wednesday, September 23, 2009

Differences between SELECT ANY DICTIONARY and SELECT_CATALOG_ROLE

SELECT ANY DICTIONARY is a system privilege, but SELECT_CATALOG_ROLE is a role, with pre-granted access to views under sys schema. (SELECT_CATALOG_ROLE has 2197 granted object privileges in 11.2.0.1)

If "O7_DICTIONARY_ACCESSIBILITY" is true, then "SELECT ANY TABLE" privilege can select any tables/views, from sys and non-sys schemas.

If "O7_DICTIONARY_ACCESSIBILITY" is false, and SELECT_CATALOG_ROLE granted, user can select these pre-granted views from sys schema.

If "SELECT ANY DICTIONARY" granted, it can access sys schema tables/views.

If you want to create a super read-only user, grant it with "CREATE SESSION", "SELECT ANY TABLE", "SELECT ANY DICTIONARY" system privileges.