Delete before trigger stop delete no error 1

[CassidyHunt]

I have no control over the vendor that wrote the application with the bad code or the ability to make it right. The only fix I have is to make sure the problem code is stopped before it does damage. I can not send an error back to the application because it crashes it. I am trying trying to create a delete before trigger that will make sure that the data being deleted is theirs to delete. If it isn't I just need to reject the delete and not send any notification back. Here is what I have so far but the delete is still going through:

create or replace trigger DPOL
  after delete on clh_temp  
  for each row
declare
       uid varchar2(255);
       e_reject exception;
begin
select replace(user,'#',null) into uid from dual;
if :old.user_id <> uid then
   raise e_reject;
end if;

exception
         when e_reject then null;
end DPOL;

Thanks in advance for the help.

Cassidy

 

[Dagon]

The only way you can stop the delete going through is to raise an error. You don't appear to be doing that becuase you are catching the e_reject. The net result is that your trigger won't do anything.

For Oracle-related work, contact me through Linked-In.

 

[CassidyHunt]

I thought that might be the case. Is there a way to apply row level security to the table on a delete? Then append a where clause to the delete statement so it never sees the rows?

Thanks

Cassidy

 

[Dagon]

I haven't used it much, but there is a concept of a Virtual Private Database in Oracle. You use the DBMS_RLS package to set up database policies that restrict users only to be able to certain subsets of rows. If you do some searching, there are plenty of examples available on the internet.

For Oracle-related work, contact me through Linked-In.

 

[CassidyHunt]

Thank you.

Cassidy

 

[taupirho]

A methodology to check out is Fine Grain Access Control (FGAC). I haven't used it myself but reading about makes me think it could be exactly the type of solution you're looking for.


In order to understand recursion, you must first understand recursion.

 

[CassidyHunt]

It works like a champ. Unfortunately my problem exists for another reason. Ah well was worth a shot. Thanks for the direction.

Cassidy