Access to v$views

[st2000]

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

[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;

[st2000]

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

Thanks, ST2000

[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

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

[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

[SANJAY_G]

You can grant him/her SELECT_CATALOG_ROLE

Sanjay

[shandj]

grant select any table privilege to that user.

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

Thanks,

[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

[jmodic]

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!