Oracle’s Recycle Bin: Security Considerations

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!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive

The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!