Re: Grant on all in user's schema

From: <mlanda_at_vnet.ibm.com>
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:

  1. Log on as USER
  2. 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

Original text of this message