Re: GRANT SELECT to sys.dba_*

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: 1998/06/13
Message-ID: <6lsqhr$nvt@bgtnsc03.worldnet.att.net>#1/1

On Fri, 12 Jun 1998 21:38:20 +1000, Dominic Lancaster <landom_at_bimberi.com.au> wrote:

>Can someone help me with the syntax to allow me to set
>what is essentially read access only for one user to the sys.dba_*
>tables, I was thinking of:
>GRANT SELECT ON TABLE like 'sys.dba%' TO USER;
>
>would this work

Not exactly. But you could select the grant statements, spool them to a file, and then execute that file. Take a look at the following:

SQL> set pagesize 0
SQL> set heading off
SQL> spool c:\a\grant.sql
SQL> l
  1  select 'grant select on '
  2         || view_name || ' to XXX;'

  3* from sys.dba_views where view_name like 'DBA%' SQL> /

grant select on DBA_2PC_NEIGHBORS to XXX;
grant select on DBA_2PC_PENDING to XXX;
grant select on DBA_ALL_TABLES to XXX;
grant select on DBA_ANALYZE_OBJECTS to XXX;
grant select on DBA_AUDIT_EXISTS to XXX;
...

...
...
SQL>spool off Received on Sat Jun 13 1998 - 00:00:00 CDT