Re: grant in trigger

On 28 jun, 07:15, peter <hhh.datab..._at_gmail.com> wrote:
> 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

"Would you pls give me any suggestion about it, it would be really helpful? "

Good design, perhaps?

"making tables in different schemas."

Without any sense? Is there a 'design phase' or everybody creates and drops tables at their own will?

Again: D-E-S-I-G-N.

Cheers.

Carlos. Received on Thu Jun 28 2007 - 02:58:09 CDT