Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: grant in trigger
On Jun 27, 8:31 pm, DA Morgan <damor..._at_psoug.org> wrote:
> peter wrote:
> > how to give execute grant command in trigger as execute immediate is
> > not working in it.
>
> > SQL>CREATE OR REPLACE TRIGGER Try
> > 2 AFTER CREATE
> > 3 ON database
> > 4 BEGIN
> > 5 execute immediate 'grant select on scott.chk to hr';
> > 6 end;
> > 7 /
>
> > Trigger created.
>
> > Elapsed: 00:00:00.00
> > SQL>create table scott.b(b number);
> > create table scott.b(b 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 2
>
> What is the business case for writing this abomination?
Actually problem is this that so many developers are working on this
database and making tables in different schemas.
Now we a common user through which they can insert in these tables,
whenever I create a table in one of 5 main schemas, I have to grant
select,insert privs to that user. So that all can see it and insert
values in it.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -
Would you pls give me any suggestion about it, it would be really helpful?
Thanks Received on Thu Jun 28 2007 - 00:15:51 CDT