I planned last week’s post to be the last one on the recycle bin. And then I got a nice surprise, Daniel Morgan, from morganslibrary.com, made a comment on my post, highlighting the security risks associated with keeping sensitive data in
the recycle bin.
It never occurred to me before, how important this could be, thus I decided to update all of you with this information. Thank you Daniel Morgan for pointing this out!
If you drop a table that contains sensitive information, such as credit card, social security number, health care number, you get the idea… without the PURGE option, that table will be placed into the recycle bin.
The dropped table can still be queried for the sensitive information.
This is a security risk. If your database has to be compliant with certain security standards, keeping sensitive information in the recycle bin, can make your database non compliant.
Here’s a demonstration of it:
|
|
create table hr.sensitive_information (
id number(10),
name varchar2(30),
sin varchar2(12));
insert into hr.sensitive_information values(1, 'robete','123-234-345');
commit;
select * from hr.sensitive_information;
ID NAME SIN
-- -------- ------------------------------ ------------
1 robete 123-234-345
drop table hr.sensitive_information;
select object_name, original_name from dba_recyclebin;
OBJECT_NAME ORIGINAL_NAME
------------------------------ ----------------------
BIN$jWBts6gvCeXgUwEAAH8Ijg==$0 SENSITIVE_INFORMATION
select * from hr."BIN$jWBts6gvCeXgUwEAAH8Ijg==$0";
ID NAME SIN
-- -------- ------------------------------ ------------
1 robete 123-234-345
|
At this point you might draw the conclusion that you should drop the table with the purge option always. And that would be wrong.
You should always analyse the options you have and determine the best solution for your needs.
Ask yourself, do I need to keep the table in the recycle bin? so I can recover the table quickly? If the answer is yes, then do not drop the table with the purge option. Think about other ways to purge the table. Maybe a few days later the table can be purged from the recycle bin, after it has been confirmed that the table is no longer needed.
Ask yourself, can I keep sensitive information in the recycle bin? If the answer is no, then you must drop the table with the purge option, to bypass the recycle bin. In this case you must prepare other mechanism to backup the table, if you want to recover the table quickly.
Ask yourself, am I in contravention with a certain compliance? If the answer is yes, then you must drop the table with the purge option, to bypass the recycle bin. In this case as well, you must come up with other means to backup the table.
Always determine the best option based on you database needs!
