An interesting followup is HOW do you grant access to the v$ views. SELECT_CATALOG_ROLE is too much, but being SYS objects you need to log in as SYSDBA to grant them individually (and it sets off flags to the DBA that it isn't something to do lightly).
PS. The main risk is latching. Shouldn't be a problem in DW or batch/reporting type systems but might be in a very high throughput transactional system.
I think it depends on the environment. If you have enough DBA's that are closely tied in with Development resources, you may be able to block access to Production. I doubt many organizations have this much bandwidth. Also, troubleshooting issues requires a lot of understanding of the Application. v$sql, for example, could help in solving an issue, as many times you do not have access to rerun jobs in Production, and it helps to track down the possible cause. And if you have a small amounts of DBA's too thinly stretched over too many applications, doing design and support, I don't see how they can have the bandwidth to do everything.
Maybe it’s different at other companies, but in my experience, especially in this time of layoffs and doubling responsibilities, Developers have to do more and more to support and analyze performance and production issues..
One other thing... Having a more up-to-date copy of Production would solve so many issues. Or, even better, replicating the data in real-time to allow duplication of most issues. But that seems to me to be a luxury these days... too much cost, supposedly. But you end up spending so much more time and resources on so much... troubleshooting, testing, loading data, etc. I have seen Dev and QA data be YEARS behind Production! Taking the time upfront to put the quality in QA will save money in the long run. Seems like so many approaches are penny wise, pound foolish.
An interesting followup is HOW do you grant access to the v$ views. SELECT_CATALOG_ROLE is too much, but being SYS objects you need to log in as SYSDBA to grant them individually (and it sets off flags to the DBA that it isn't something to do lightly).
ReplyDeletePS. The main risk is latching. Shouldn't be a problem in DW or batch/reporting type systems but might be in a very high throughput transactional system.
I think it depends on the environment. If you have enough DBA's that are closely tied in with Development resources, you may be able to block access to Production. I doubt many organizations have this much bandwidth. Also, troubleshooting issues requires a lot of understanding of the Application. v$sql, for example, could help in solving an issue, as many times you do not have access to rerun jobs in Production, and it helps to track down the possible cause. And if you have a small amounts of DBA's too thinly stretched over too many applications, doing design and support, I don't see how they can have the bandwidth to do everything.
ReplyDeleteMaybe it’s different at other companies, but in my experience, especially in this time of layoffs and doubling responsibilities, Developers have to do more and more to support and analyze performance and production issues..
One other thing... Having a more up-to-date copy of Production would solve so many issues. Or, even better, replicating the data in real-time to allow duplication of most issues. But that seems to me to be a luxury these days... too much cost, supposedly. But you end up spending so much more time and resources on so much... troubleshooting, testing, loading data, etc. I have seen Dev and QA data be YEARS behind Production! Taking the time upfront to put the quality in QA will save money in the long run. Seems like so many approaches are penny wise, pound foolish.
ReplyDelete@gary
ReplyDeleteI would assume it would be on the most granular level, i.e. grant select to specific views.
That was originally what I was looking for, why it might be bad. Latching. Interesting. Thanks as always!
chet
This still cracks me up. I wonder if the Twitter Machine turns into the Script Kiddy machine with certain questions.
ReplyDeletebtw, I hate the fonts on your comments (not this blogger comment page but the comments on oraclenerd).