Re: Grant on all in user's schema

Kirill Richine wrote:
>
> 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&

Have you tried the following?:

GRANT SELECT ANY TABLE TO role_name;

or

GRANT SELECT ANY TABLE TO user_name;

Perform one of these when connected as the granting user. FYI, if you have not specified synonyms you will need to preface the table name with the granting users' name (e.g. scott.emp). One other note... you may substitute "SELECT ANY TABLE" with "INSERT ANY TABLE", "UPDATE ANY TABLE" or "DELETE ANY TABLE".

Sincerely,

Paul Banas
Corporate Computing Services - Oracle & Network Solutions Michigan, USA
(517) 725-9794 phone
(517) 725-6383 fax
banasp_at_tir.com Received on Wed Jun 05 1996 - 00:00:00 CEST