Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: grant in trigger

Re: grant in trigger

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 29 Jun 2007 12:54:08 -0700
Message-ID: <1183146771.317295@bubbleator.drizzle.com>


peter 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

This is a case where two things should happen:

  1. Your manager should be terminated for incompetence.
  2. Your organization should hire a new manager who understands how to manage a development project.

There is no technical solution for bad management.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Fri Jun 29 2007 - 14:54:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US