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 -> How to prevent deletes when DELETE ANY TABLE granted?

How to prevent deletes when DELETE ANY TABLE granted?

From: John Verbil <jverbil_at_netmail.mnet.uswest.com>
Date: 1996/12/11
Message-ID: <32AEF16C.2625@netmail.mnet.uswest.com>#1/1

I have several users (developers, actually) who have been granted DELETE ANY TABLE because they need to be able to occasionally TRUNCATE tables. One table, though, I don't want _anyone_ (except the DBA) to be able to delete directly, because it is intended to be an exact replica of another table.

That other table has an after insert-update-delete trigger on it to keep the replica in sync, but because I cannot REVOKE delete access to a single table that has been granted via DELETE ALL TABLE, any one of these users could - unintentionally, of course :) - delete rows out of the replica and thus put the tables out of sync.

I tried checking the USERNAME in a before delete trigger on the replica table, hoping that, in situations where deletes were triggered from the master table, it would reflect something other than the originating user, such as maybe the owner of the table itself. If so, this would have allowed me to reject deletes from anyone but the replica table's owner, making it quite a bit more difficult for anyone to make a direct change to the replica. Alas, Oracle passes the user name down the line to all triggered procedures.

Any suggestions?

-- 
John Verbil
U S WEST Communications
Information Technologies
jverbil_at_uswest.com
(303) 896-0916
Received on Wed Dec 11 1996 - 00:00:00 CST

Original text of this message

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