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?
Best Answer
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.