This entry is partly an aide memoire for me, partly to try to get something that has been keeping me awake for the past hour or so out of my brain so I can sleep and partly in the hope that someone can suggest a way forward.
A quick bit of background. Until April 06 most of our major systems were looked after by an external Faccilities Management company. In April 06 IT was kind of outsourced to a joint venture company, support of the systems transferred to that company and a couple of the DBAs transferred in as well under TUPE. A reccurring problem on one of the systems is that the users keep deleting records which, by law, they must not delete so we (actually one of the DBAs who transferred in who is responsible for that system) has to restore from backup to another machine and copy the deleted records over (she's tried using LogMiner but finds it too unwieldy). The core problem is that the application is faulty and lets the users delete the records when it shouldn't.
The application is closed source from an external vendor so we cannot change it to make it prevent the users from deleting the records. Due to the political environment we've got zero chance of getting the application changed to one that does stop them (the people with authority to replace the app don't have responsibility for the costs of continually putting right the problems it causes and vice versa).
The application logs into the database as the schema owner, individual user authentication is handled within the app, so we can't just revoke delete privilege from the users.
It occured to me just (this is what has been keeping me awake) that we might be able to fix it using triggers.
First I thought of a before delete...for each row trigger to archive to another table before it deletes them so at least restoring the rows is just a case of an insert statement. Then I thought thst times when we might legitimately need to delete a record are massively out numbered by the times we want to prevent a record being deleted so preventing deletion would be much better. Now I'm thinking we need and instead of delete trigger so if someone attempts a delete it won't let them. According to the documentation instead of triggers can only be applied to views so we might have to rename the table and replace it with an updateable view witht he original tablename, not sure how that would impact on the support/maintenence of the app, we might not able able to do it. Assuming we can work around that problem the next issue is what we do when the trigger fires. Do we do nothing, log that somone tried to delete a record or do we raise an exception?
Any thoughts and/or suggestions gratefully received.
Subscribe to:
Post Comments (Atom)
7 comments:
Hello Stephen,
I think that one of things that you can use is database trigger based on "BEFORE DDL" event. As far as I can recall since Oracle 9i it is possible to get SQL statement that is issued based on ora_sql_txt variable (...\appdev.920\a96590\adg14evt.htm).
Since you work with an application, it should (I think) generate the similar sqls every time when it does delete. So based on that you can ban specific statements and also you can use other trigger variables to strictly define terminal and application. At the same time, your sql statements would work since you can define them as you want.
HTH,
Andrey Goryunov
A simple BEFORE DELETE ON table
BEGIN
RAISE_APPLICATION_ERROR(-20001,
'Bad boy');
END
may do the trick.
But it depends on how your application will deal with errors.
Depending on your DB version/options, DBMS_RLS allows policies to be added for DELETE statements only.Add a 1=2 policy will ensure no-one can delete anything (or a USER = 'STEPHEN' so that only you can do deletes).
Roles and privileges like a "revoke delete on table_name from user_name;" may help I guess, best regards.
Tonguc,
Unfortunately that doesn't work as the application logs into the database as a schema owner. To the best of my knowledge, the owner of the table will always have full rights on the table, including being able to delete records.
Stephen
You could consider row level security: attach a DBMS_RLS policy to the relevant tables which will prevent anybody - even the table owner - from successfully issuing a DELETE statement.
Cheers, APC
How will anything really help as the application can just do whatever it wants when you allow it to log on with schema owner?
Removing delete logic from the application is probably just as effective.
An alternative is to have the application log in to an application user instead so you can control access.
MD,
Unfortunately this is a bought in product, not one we've developed ourselves or have the source code for.
I've found this disturbingly often, here and at previous employers. We need changes to an application but the vendor either won't make changes, will make the changes but wants more money than management are prepared to spend or have gone out of business and no-one knows where the source code is. I don't know which is the case here.
I understand that the application DBA has now found a work around (it's not an area that I work on so I've not been closely involved).
Post a Comment