Re: Granting SELECT to all tables in a schema

From: Candido Dessanti <termy_at_blunet.it>
Date: Tue, 29 Oct 2002 11:41:45 +0100
Message-ID: <pan.2002.10.29.11.41.41.988968.1464@blunet.it>

On Tue, 29 Oct 2002 00:07:29 +0100, ivan vasquez wrote:

> Is there a better way to GRANT user1 to SELECT from any table (or
> object) in user2's schema? I could create a role with all tables, but
> then I'd have to update it every time I add a new table.

I dont know, but you can always track the creations of new tables with system triggers
create or replace trigger create_table after create on schema begin

   IF sys.dictionary_obj_type = 'TABLE' THEN

        [here you could place on insert or other things like the schedule of a job

         with dbms_job package with the name of the new table created
         located in the attribute sys.dictionary_obj_name. Unluckly you
         cant perform the grant from a system trigger].

   END IF;
 END; hope it helps

ciao Received on Tue Oct 29 2002 - 04:41:45 CST