Protecting Data from Users
October 14, 2009
What is the best way to prevent unintended updates or deletes in a table? The small changes may not be so bad or hard to recover from, but what if it was every record in the table that underwent a change? The global change situation can occur when a user submits an update or delete without a where clause, potentially causing a global reaction. As a DBA, how can you protect users from themselves? And lets be honest how do you protect yourself from you?
Methods that can be used to prevent global change range from the sophisticated to those of brute force, high cost to no cost, and may or may not be purely Oracle-driven. One approach is to control or manage changes (all data manipulation language statements) via an application interface. Oracle Forms is an excellent example of how to manage change. With presented data typically being shown in a master-detail or parent-child type of relationship, chances are most changes are going to be one or few at a time. But then there is the overhead of licensing a version of Forms & Reports, hardware costs for running Application Server, and development time. Even with a rapid application development tool such as APEX, you still cant escape the development overhead.
On a simpler, less elegant level, one way to prevent global changes is to disable locks on your table(s) of interest.
SQL> alter table emp disable table lock; Table altered.
Problem solved no one can make any DML changes, right? Lets first test dropping the table.
SQL> drop table emp; drop table emp * ERROR at line 1: ORA-00069: cannot acquire lock -- table locks disabled for EMP
With the ORA-00069 error raised, we know were at least safe from inadvertent drops (and this works for truncate as well). Does it work for DML? Lets update SAL to 5000 for everyone by omitting a WHERE clause (because we only meant to update one or two records).
SQL> update emp set sal = 5000; 14 rows updated. SQL> commit; Commit complete.
With respect to our goal of preventing global changes, a status of DISABLED as seen in DBA/ALL/USER_TABLES (the TABLE_LOCK attribute) refers to DDL locks, not DML locks, so this is not a viable option. DDL on the table is more likely to be quite rare when compared to the frequency of DML.
Product user profile
Perhaps another option is to use the product user profile. With the profile in place, insert into the PRODUCT_PROFILE table the user (or users) whose abilities you want to limit.
INSERT INTO product_profile (product, userid, attribute, char_value) VALUES ('SQL*Plus','SCOTT','DELETE','DISABLED'); INSERT INTO product_profile (product, userid, attribute, char_value) VALUES ('SQL*Plus','SCOTT','UPDATE','DISABLED');
Lets apply the update restriction to Scott and then have him try to update the EMP table again.
SQL> INSERT INTO product_profile 2 (product, userid, attribute, char_value) 3 VALUES ('SQL*Plus','SCOTT','UPDATE','DISABLED'); 1 row created. SQL> commit; Commit complete. SQL> conn scott/tiger Connected. SQL> update emp set sal = 4000; SP2-0544: Command "update" disabled in Product User Profile
But, we forget how clever Scott is. With a marginal amount of PL/SQL knowledge at his disposal, he can still perform the update.
SQL> begin 2 update emp set sal = 4000; 3 commit; 4 end; 5 / PL/SQL procedure successfully completed. SQL> select count(*) from emp where sal = 4000; COUNT(*) ---------- 14
In this approach, you would have to disable PL/SQL along with the specific commands (DML in this case). The downside to this approach is that no updates can be performed, which is obviously too restrictive.
Even if there were a way to force (or check) a WHERE clause into the users statement via some auto-magical feature, with the assumption that a WHERE clause is going to filter the data set being manipulated, there exists a simple workaround to this as well. All a user has to do to validate the WHERE clause checking mechanism is to use a construct such as WHERE 1=1 or where the clause always evaluates to being true/valid.
If youre willing to throw money at prevention, standby database or Data Guard may be suitable, although they really are not preventative measures at all. What they do provide is a time delay. Nothing stops the user from issuing an incorrect DML statement, and once issued, it is a time bomb waiting to go off in the replicated database. Along this line of steps you can take is flashback technology, but again, you have to know about the situation in a timely manner because even with flashback, there is a time limit as to when recovery is no longer possible.
Speaking of recovery, yes, there is always that option, and the point in time to which you can recover is dependent upon your choice of archiving. But again, this does nothing to prevent the statement in the first place. Digging deeper into our bag of tricks, what about triggers? Lets try a stop delete trigger.
create or replace trigger stop_delete after delete on emp declare v_cnt number; v_rows number; begin dbms_output.put_line('Trigger fired'); select num_rows into v_rows from user_tables where table_name = 'EMP'; dbms_output.put_line('v_rows is '||v_rows); select count(*) into v_cnt from emp; dbms_output.put_line('v_cnt is '||v_cnt); if v_rows > v_cnt then RAISE_APPLICATION_ERROR (-20001,'Missing where clause'); rollback; end if; end; /
The trigger code compiles with no warnings, and here is what happens after issuing a DELETE statement without a WHERE clause.
SQL> delete from emp; Trigger fired v_rows is 14 v_cnt is 0 delete from emp * ERROR at line 1: ORA-20001: Missing where clause ORA-06512: at "SCOTT.STOP_DELETE", line 16 ORA-04088: error during execution of trigger 'SCOTT.STOP_DELETE'
There are a couple of things to look at before considering this for widespread usage in a system. First, there is a requirement to have statistics report the actual number of rows in a table. For a very large table, this means pretty much abandoning sampling.
The second and more interesting part of this trigger has to do with why a mutating table error was not raised. If you presume that a basic feature of triggers is that you cannot look at the table upon which a trigger is based, then why did the above DELETE attempt not raise the mutating error (ORA-04091)? If that is your presumption (which is similar to interchanging database and instance at times), then that needs to be clarified a bit. Does this error occur at the row level or statement level? Or was it not raised because when the SELECT statement against EMP was in scope, there were no changes being made to data at that time (i.e., the SELECT statement sees the table AFTER the changes have been made). In other words, nothing was mutating or changing when the count of EMP took place.
So with statistics, we at least have a viable approach to preventing mass deletes, but it raises the problem of keeping statistics in sync with the number of rows within the target table. Instead of statistics, use a materialized view. Youll have the overhead of maintaining the MV, and you may have to account for some sloppiness in terms of the number of records in the MV versus those in the table. One way to get around comparing exact numbers is to settle for a percentage change. If the DELETE statement impacts more than 50% of the rows (or whatever threshold value you want to use), then a ratio test using v_cnt/v_rows can be employed. Granted, this is not perfect, but you do at least avoid a 100% change.
What about the UPDATE scenario? This requires a little more sophistication where the counts of some convenient attribute (such as LAST_UPDATED_DATE) are compared. The chance that all records in a table would be updated in the same day (across multiple user sessions) is probably quite small, but to be sure, insert a sentinel record with a date so far off from what users would be looking for that if the sentinel records last updated date is not the set date, then you know that it was errantly updated, and that is what you could base the trigger on.
Views are another line of defense, especially if they are key-preserved, but also keep in mind that unless they are read only (and again, keep in mind we are trying to minimize the impact on users), even a simple view supports DML on a table. The EMP_V view which is simply SELECT * from EMP allows a global delete. (Note: the delete trigger from before has been dropped.)
SQL> create view emp_v as select * from emp; View created. SQL> delete from emp_v; 14 rows deleted.
Use an external data source
Finally, another option is to boot users out of Oracle and force them to use a connection from another source, and if the WHERE clause test passes in the other system, allow the DML to take place in Oracle. An example of the WHERE clause checks for UPDATE and DELETE are shown in this article from MSSQLTips. It would mean duplicating data, or at the least, refreshing data on the external RDBMS. But then again, these are somewhat invasive measures designed to prevent users from doing untold damage, albeit inadvertently.
If widespread, but unintended data changes are taking place due to frequent user error, the best line of defense is to keep users from being able to update or delete all rows in one fell swoop. Application control can be employed, thereby limiting changes to one, or somewhat more than one, record at a time. Bulk updates or deletes can be managed via verified script testing. Other options may exist for you, such as placing triggers on tables, but there may be a noticeable performance hit if you have to count records each and every time a larger table is having its data manipulated. Preventing global changes can be a complex problem. If you cannot implement any of the possibilities presented in this article, then be sure to stay on top of your backup and recovery skills because sooner or later, the unintended global change event is going to visit you.