Access to v$views

Printable View

10-24-2002, 01:06 PM
st2000

Access to v$views

Hello:

Have a user that's been given access to only connect and resource roles. Want to give access to v$views and stuff like dba_xxxxx and so on.. I am unable to recall the syntax and is going wrong..

Thanks, ST2000
10-24-2002, 02:37 PM
stecal

Depends on who you are and what you can do.

SQL> conn sys/oracle@db00
Connected.
SQL> grant select on v$database to scott;
grant select on v$database to scott
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

SQL> grant select on dba_data_files to scott;

Grant succeeded.

SQL> connect system/manager@db00
Connected.
SQL> grant select on dba_users to scott;
grant select on dba_users to scott
*
ERROR at line 1:
ORA-01031: insufficient privileges;
10-24-2002, 03:58 PM
st2000

Simply want to be able to select any v$views and dba_..._... views.

Thanks, ST2000
10-24-2002, 04:51 PM
denevge

Log in as the user sys, and grant select privileges to the v_$
eg.
If you want to have access to v$session

connect sys/MySecretSysPassword
grant select on v_$session to MyPersonalUser

HTH
Gert
10-24-2002, 05:05 PM
jmodic

(Since you don't mention your database release, I'll assume 9.*)

GRANT SELECT ANY DICTIONARY TO your_user;

And YOUR_USER has all V$ and DBA_* on his disposal, its as simple as that.
10-24-2002, 10:20 PM
st2000

Hello:

I am using 817.

I am aware of granting permission 1 table.. But I want for ALL v$views and all dba_..._... views in one statement..

Thanks, ST2000
10-24-2002, 11:44 PM
SANJAY_G

You can grant him/her SELECT_CATALOG_ROLE

Sanjay
10-25-2002, 04:29 AM
shandj

grant select any table privilege to that user.

The user can access v$tables and also dba_ tables.

Thanks,
10-25-2002, 04:41 AM
pnrdba

Shandj is correct,

SQL> grant select any table to dloader;

Grant succeeded.

SQL> select name from v$database;

NAME
---------
CMSRPT1

SQL> select count(*) from dba_tablespaces;

COUNT(*)
----------
20

SQL> select count(*) from dba_data_files;

COUNT(*)
----------
60

Its working for any v$tables and dba_tables....

Thanks
10-25-2002, 06:03 AM
jmodic

Quote:

Originally posted by shandj
grant select any table privilege to that user.

The user can access v$tables and also dba_ tables.

And while you at that, why not simpl grant him DBA privilege? And perhaps SYSDBA on top of that? Or, to make it even more simple, why not simply all your user know the SYS password and let them all allways connect as SYS? You know, as DBA you'll have far less troubles with user managenment this way! :D
10-25-2002, 06:30 AM
nagarjuna

grant SELECT_CATALOG_ROLE to user****;

and also set the folowing init.ora parameter to true.

O7_DICTIONARY_ACCESSIBILITY
10-25-2002, 07:23 AM
shandj

Quote:

And while you at that, why not simpl grant him DBA privilege? And perhaps SYSDBA on top of that? Or, to make it even more simple, why not simply all your user know the SYS password and let them all allways connect as SYS?

Granting select any table to a user doesn't mean one is giving all the DBA privileges to a user. One is giving just read only privilege on the v$tables.

For example:
Developer need to check how many cursors are opened by his application while running, one way is to access the corresponding v$tables to close the cursors that are open. Otherwise everytime he/she has to request the DBA to provide the respective information.

Its entirely depends upon the situation and request.

Thanks,
10-25-2002, 08:56 AM
Raminder

shandj, granting 'select any table' to all developers will greatly compromise security if you or your organisations have 'secure' tables, ie. database tables you don't want everyone to see. If you don't have that problem of security, go ahead. But also think of the future/ future DBAs in your organisation.
The very concept of privileges in Oracle is to provide 'granular' security, ie provide only those privileges which are absolutely essential for the user.
Hence, in this case, 'select catalog role' or 'select any dictionary' is the right choice.
10-25-2002, 09:00 AM
jmodic

Quote:

Originally posted by shandj
Granting select any table to a user doesn't mean one is giving all the DBA privileges to a user.

Obviously I wasn't sarcastic enough in my previous reply. Sorry about that. :rolleyes:

It is more than evident that your proposed solution gives much, much, much wider privileges to the end user than original poster was asking for. The original question was very simple: given the user's existing privileges, what do I have to do to enable him access to V$ and DBA_* family of views. In this aspect, your proposal was way to generous. By using your suggestion, the user will have select permition in any peace of data written into the database. I doubt this is what original poster was looking for.
10-25-2002, 09:44 AM
nagarjuna

Quote:

Originally posted by nagarjuna
grant SELECT_CATALOG_ROLE to user****;

and also set the folowing init.ora parameter to true.

O7_DICTIONARY_ACCESSIBILITY

Why nobody is considering my solution???:confused:
10-25-2002, 10:16 AM
gandolf989

Quote:

Originally posted by nagarjuna
Why nobody is considering my solution???:confused:

I think the reason that no one is considering your solution is that it turns off any new security added after 7.3.4. Another solution might be to have the users spend more time looking at the user views. If they can get what they want with a user view then they already have that privilege they need. If someone had a good reason to do a select on a v$ view then I would do an explicit grant on the object they need for that user. However, I don't know why a non-privileged user would have a need to look at any sys object.
10-25-2002, 10:18 AM
gandolf989

Quote:

Originally posted by jmodic
And while you at that, why not simpl grant him DBA privilege? And perhaps SYSDBA on top of that? Or, to make it even more simple, why not simply all your user know the SYS password and let them all allways connect as SYS? You know, as DBA you'll have far less troubles with user managenment this way! :D

If you really want to give users access to a database why don't you just grant sysdba to the public role.

:D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D :D
10-25-2002, 05:41 PM
jmodic

Quote:

Originally posted by nagarjuna
Why nobody is considering my solution???:confused:

Maybe because it was sanjaj_g's orriginal sollution, long before you proposed it? :rolleyes:

Quote:

Originally posted by SANJAY_G
You can grant him/her SELECT_CATALOG_ROLE

Sanjay
10-25-2002, 11:33 PM
nagarjuna

Quote:

Originally posted by nagarjuna
grant SELECT_CATALOG_ROLE to user****;

and also set the folowing init.ora parameter to true.

O7_DICTIONARY_ACCESSIBILITY

Stupid solution again :D

We dont need to set O7_DICTIONARY_ACCESSIBILITY to true when we give SELECT_CATALOG_ROLE to user to access v$ and dba_ views. We have to set O7_DICTIONARY_ACCESSIBILITY to true only when we give select privileges on v$ and dba_ views individually.. Stupid solution again :p