Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 8 down vote accepted

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.

share|improve this answer
    
+1 Good points. Alas, the DBA's here have an aversion to granting "ANY" privs to devs. I'm not sure I understand the drawback of granting access to dictionary views but I'm sure they have their reasons. 8) To your point about GUIs - I'm not sure what system views TOAD or PL/SQL Developer use to get the meta-data. I wonder if Oracle's SQL Developers uses the same... –  ScottCher Feb 17 '11 at 16:59
    
If the DBAs have an aversion to SELECT ANY DICTIONARY, I'd ask for the older SELECT_CATALOG_ROLE. If that too is problematic, you can ask for specific grants on the various DBA_* tables that you're actually likely to use. But that obviously has scalability problems when you find that you need access to "just one more" view on a regular basis. –  Justin Cave Feb 17 '11 at 17:03
6  
SELECT ANY DICTIONARY and SELECT_CATALOG_ROLE, prior to 11g, had the ability to see the hashed passwords of users. If people don't use complex passwords, it makes them vulnerable to brute force attacks. Understandable that DBAs are reluctant to grant them. –  Gary Feb 17 '11 at 22:06

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..

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.