On 12/2/2010 10:18 AM, samcarleton wrote:

> We have DB data issue where data is duplicated in two tables.
> When the first table is updated, a trigger updates the second table.
>
> Ideally we would like to lock down the database so that the
> second table cannot be updated by the user/application.
> Can that be done, if so, now?

Have the trigger invoke a procedure and grant access to the
procedure, not the user. Grant access to the procedure to
whatever users will invoke it.

>
> It is my understanding that some DBA's like to lock users/applications
> out of the tables and do that with stored procs. Is there some way to
> setup the security such that triggers and stored procs can modify the
> table but the user/application cannot?

I don't think there's a way to do that with triggers (though I could
be wrong) but triggers that invoke procedures can certainly do things
that the original user cannot do. There are entire applications that
run through procedures with no user having direct access to tables.

Good luck,

Ann