Re: Grant on all in user's schema
Date: 1996/06/05
Message-ID: <4p48a2$reo_at_watnews1.watson.ibm.com>#1/1
In <4p27t7$877_at_scapa.cs.ualberta.ca>, kirill_at_cs.ualberta.ca (Kirill Richine) writes:
>Hi!
>
>I was wondering if there was a way to grant a privilege on all the
>tables in the user's schema to some other user.
>
>I am trying to do:
>
>create role ROLE
>/
>grant select on
> (select table_name from sys.dba_tables where owner = 'USER')
> to ROLE
>/
>
>but it does not work because (select... 'USER') is not a valid table
>name.
>
>Thank you.
>k&
>
Try this:
- Log on as USER
- Create/Run the following sql script
spool grntusr.sql
set heading off
set underline off
set pagesize 1000
set linesize 132
select distinct 'grant select on ', tname, ' to ROLE;' from tab;
spool off
set heading on
set underline on
_at_grntusr.sql
This will also grant select priviliges on USER's views to ROLE.
M.Landa Received on Wed Jun 05 1996 - 00:00:00 CEST