Re: Access to V$ views

Ed wrote:

> Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3CD8344A.F24B4382_at_exesolutions.com>...
>
> > Please don't take this the wrong way. But if I had someone claiming 10
> > years of Oracle beginning with version 5, that had to ask for help in
> > coming up with reasons why they need access to V$ views I would take away
> > their create table privilege too.
> >
> Ouch!! OK, I won't take it the wrong way. I did, in fact, come up
> with many of the same reasons myself. However, even with my vast
> experience [;-)], I don't consider myself perfect. I do look at
> things from a developer's rather than a DBA's point of view. If I'm
> in a meeting with my DBA and he says 'No way - it'll affect
> performance' (a fairly typical riposte with him), then I'd like to be
> as confident as possible in my position. I've never come across
> performance issues, but, I'm not so arrogant as to assume that because
> I haven't come across such issues, they don't neccessarily exist! I
> learn new things about Oracle every day.
>
> > The reason for denying it is paranoia ... a developer might figure out
> > that they don't know what they are doing.
> > They are insecure ... they think you might try to do what they perceive
> > to be their job.
> >
> This is exactly what I thought.
>
> > The reason for granting access is that no developer can get within 100
> > meters of competence without them. Likely you don't have the ability to
> > kill development sessions either. Something I consider a near criminal
> > act.
> >
> > Daniel Morgan
>
> Not a chance of that - I like being able to flush the shared pool too.
> Of course, I don't go round coding infinite loops in my PL/SQL very
> often, so killing sessions is not a number one priority :-)
>
> Regards,
>
> Ed

Thanks for not taking offense.

I too learn something new every day. And I've no doubt that anyone that can't say that isn't paying attention.

The reason to kill sessions is not just infinite loops. It is Cartesian joins. It is executing something and then thinking ... "this is going to run for an hour I think I'll stop it and try to get the answer another way." and sometimes it is just a manager saying "I don't need that any more" so why let the process continue to eat CPU time. Either way ... I consider a developer that can't kill their own session handicapped.

If you are going to argue for privileges what you need to do is demonstrate to management how it would make you more effective. One way I have used is to make access mandatory as part of the development requirement. Here's one way:

If you add a field to your tables to capture the ID of the person that inserted a record you can use:

REC_INS_BY VARCHAR2(30) DEFAULT USER and capture the schemaname but you can't capture the OSUSER and it won't help with updates. So a piece of code I insist in putting into every application is the following:

SELECT osuser
INTO x
FROM v$session
WHERE sid = (

   SELECT sid
   FROM v$mystat
   WHERE rownum = 1);

Not that there aren't other ways to get the information. Rather that it can be justified and gets past the blanket "you can't see V$ objects" objection.

Daniel Morgan Received on Wed May 08 2002 - 10:15:54 CDT