Re: Access to v$ views

On Tue, 07 Dec 2004 19:22:53 -0800, n_kselva wrote:

> Hello,
>
> I am a startup DBA.I read in a tuning book that one should grant
> privileges on v$ views to non DBA users as needed and use caution.
> and it involves performance costs.I do not know which situation could
> demand a non DBA to access the v$ view.Also what are the other
> problems/concern associated with this grant of privilege. I do not find
> deeper discussion in books.I apologize if i have not correctly used
> this forum.
>

This is the right newsgroup to be asking this question.

As a general rule, I believe that most users should not need access to data other than that which fulfills their application needs. In other words, I do not consider it a good idea to give public access to 'anything and everything'.

So, unless the application actually requires access to data, such as that found in a V$ view, I suggest it should not be made available to the 'regular' user.

A developer, on the other hand, as well as others involved with specific areas of operation in the database will find SOME of the data very important. One way to handle this is to make it available on demand - give grants when asked, and only for a specified time.

Another way is to review the V$ views as written in the ORACLE Reference manual for the version you are running, and make a judgement call on that. (BTW - the manuals are at http://docs.oracle.com and  http://tahiti.oracle.com).

For example, I doubt very much that the typical developer needs to be aware of the contents of V$DATAFILE or V$LOGFILE, but many could be interested in V$SESSION. OTOH, handing out access to V$PARAMETER might open a can of worms that possibly should be postponed until you are comfortable with the meaning of the parameters (as you did say you are a startup DBA).

As an open discussion topic, I'd consider creating some 'roles' specifically to access some of these views based on job functions. I'm thinking roles like 'Backup Monitor, Operational DBA, Application DBA, General developer, Performance tuner, etc.' - based on functionality (eg: hat being worn), not HR role. Thoughts, anyone?

HTH
/Hans Received on Tue Dec 07 2004 - 22:05:12 CST