Re: Grant select on all tables of other user

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Sat, 02 Apr 2005 20:05:01 GMT
Message-ID: <NVC3e.783$uw2.233@twister.nyroc.rr.com>

Rene Nyffenegger wrote:

>>>OK, but this is not what I would like to do. This way you have to specify 
>>>grants for each table. If a new table is added, a new SELECT grant has to be 
>>>given. Would it be possible to give this grants automatically each time a 
>>>new table is added ? 
>>>
>>>
>>
>>You can do this using a schema trigger.
>>If you want to see an example code of schema triggers ... try:
>>http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1380003556171
>>
>>Anurag

>
>
>
> Anurag,
>
> That was my first thought as well. But when I tried it, I got an error:
>
>
>
> create user other_user
> identified by other_user
> default tablespace data
> temporary tablespace temp;
>
> grant create session to other_user;
>
>
> create or replace trigger trg_grant after
> create on schema
> begin
> if ora_dict_obj_type = 'TABLE' then
> execute immediate 'grant select on ' ||
> ora_dict_obj_owner || '.' || ora_dict_obj_name ||
> ' to other_user';
> grant_read(ora_dict_obj_name, ora_dict_obj_owner);
> end if;
> end;
> /
>
> create table zebra(fisch number);
>
> ERROR at line 1:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-30511: invalid DDL operation in system triggers
> ORA-06512: at line 3
>
>
>
> So, did you have something else in mind?
>
> Rene
>
>

Rene,

Something like this might work (as tested briefly by myself). I hope it does not wrap:

---

drop trigger trg_grant;
drop table crtab;

CREATE OR REPLACE package grantpkg
AS

    procedure sel (in_obj in varchar2, in_user in varchar2); end grantpkg;
/
show errors

CREATE OR REPLACE package body grantpkg
AS

    procedure sel (in_obj in varchar2, in_user in varchar2)     is
    begin

       execute immediate 'grant select on '||in_obj||' to '||in_user;     end sel;
end grantpkg;
/
show errors

create or replace trigger trg_grant
after create on schema
declare

    l_sysevent varchar(25);
    v_jobno number;
begin

    select ora_sysevent into l_sysevent from dual;     if ( l_sysevent = 'CREATE' )
    then

       dbms_job.submit(v_jobno, 'begin grantpkg.sel( '''||
          ora_dict_obj_name ||''', ''TEMPUSER''); end;',sysdate);

    end if;
end;
/
show errors

create table crtab (a number);

insert into crtab values (1);

commit;

---

Thanks,
Anurag Received on Sat Apr 02 2005 - 14:05:01 CST