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 let’s be honest – how do you protect
yourself from you?
Application control
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 can’t escape the
development overhead.
Disable locks
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?
Let’s 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 we’re at least safe
from inadvertent drops (and this works for truncate as well). Does it work for
DML? Let’s 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');
Let’s 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 user’s 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.
Replication
If you’re 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.
Triggers
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? Let’s 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.
Materialized view
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. You’ll 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 record’s 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.
Use views
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.
In Closing
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.
