How do I allow users to see grants, view definitions, PL/SQL, etc in a database without granting them access to change those objects

Question

As a developer, I need to be able to access the design of the database/database objects from production so I can ensure I'm working with the latest and greatest version of the objects I'm developing changes for (helps to avoid unforeseen problems at deployment).

Also as a developer, I should not have access to modify, execute, update, delete, etc those objects - that's the job of the production deployment team.

As a DBA, how can I provide access to the design of the database so developers can validate the changes they are working on without also granting them carte blanche access to the database objects?

As a specific example, our DBA's have sufficiently locked down the production environment so that, within TOAD, I can't see the PL/SQL body of a package, can't see all of the grants providing access to an object, etc. The user account being used just has limited access to use some of the objects but can't view the extended details a developer would need to develop changes.

I'd love to have things set up so we have dictionary view access that allows us to see all the grants, the view scripts, the PL/SQL package bodies, table definitions, etc without also giving us more access than we should have in production (ie, we shouldn't be able to change any of those things)

Is there a role that provides this access, a privilege or series of privileges we would need? If there isn't one, what's a good alternative method for getting this kind of information?

Answer 1

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.

Answer 2

As a slight aside.. I wanted my sql*plus script to choose the "best" views for a query.

ie dba_xxx if available or all_xxx if not

So i did somethign like this at the top of my script (or in a subscript if you prefer)

column get_access_type  NEW_VALUE access_type

SELECT MAX(access_type) get_access_type
FROM ( --11g
SELECT 'DBA' access_type FROM user_sys_privs WHERE privilege= 'SELECT ANY DICTIONARY'
UNION --before 11g
SELECT 'DBA' access_type FROM user_role_privs WHERE granted_role = 'SELECT_CATALOG_ROLE'
UNION --default
SELECT 'ALL' access_type FROM dual)
;

and then I used it as follows.

prompt &access_type._OBJECTS
SELECT object_name
      ,subobject_name
      ,owner
      ,object_type
      ,status
FROM &access_type._objects  ao
WHERE object_name LIKER UPPER('%&object_like%')
ORDER BY 1,3;

maybe that might be useful for someone..