| Security Advisory |
Dropping a table has not guaranteed that the table, and its data, were gone since version 10gR1 when Oracle introduced the recycle bin.
The default configuration of all Oracle Databases installed using OUI and DBCA since that date has been for the recyclebin to be enabled and for tables, along with their data, indexes, constraints,
and triggers to be saved there under the assumption that the database owner might with to, at some later date, perform a FLASHBACK DROP.
DBSecWorx does not focus on basic Oracle Database functionality except where that functionality impacts system and data security so this monograph will focus on the risks associated both with disabling the recyclebin and not purging one that is enabled. |
| |
| Recommended Security Rules |
NEVER
- Drop a table with that you wouldn't want in the hands of an organized crime family or government organization into the recyclebin. If the data needs to be secure perform the DROP TABLE with the PURGE keyword.
WITH GREAT CARE
- Drop a table with confidential data into the recyclebin because you must remember to quickly either restore it or purge it from the recyclebin.
CAUTIONS
- Do not disable to recyclebin because either (A) you think that will improve security, it won't good processes and procedures improve security, or (B) you think the recyclebin will waste space or use a measurable amount of system resources: it won't.
The recyclebin should always be enabled because part of security is securing data against accidental loss which can happen during routine maintenance activities.
- Tables owned by SYS are not put into the recyclebin so "best practice" before dropping one is to verify that you have a current backup.
|
| |
| How Oracle Works |
| To create and maintain a secure environment you must have the recyclebin enabled and you must manage it correctly |
SQL> conn sys@pdbdev as sysdba
Connected.
SQL> SELECT owner, object_name, object_type
2 FROM dba_objects
3 WHERE object_name LIKE '%RECYCLE%'
4* ORDER BY 1,3,2;
OWNER OBJECT_NAME OBJECT_TYPE
-------- ----------------- ------------
PUBLIC CDB_RECYCLEBIN SYNONYM
PUBLIC DBA_RECYCLEBIN SYNONYM
PUBLIC RECYCLEBIN SYNONYM
PUBLIC USER_RECYCLEBIN SYNONYM
SYS RECYCLEBIN$_OBJ INDEX
SYS RECYCLEBIN$_OWNER INDEX
SYS RECYCLEBIN$_TS INDEX
SYS RECYCLEBIN$ TABLE
SYS CDB_RECYCLEBIN VIEW
SYS DBA_RECYCLEBIN VIEW
SYS USER_RECYCLEBIN VIEW
SQL> show parameter recyclebin
NAME TYPE VALUE
----------- ------- ------
recyclebin string on
SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
0
SQL> SELECT COUNT(*) FROM dba_recyclebin;
COUNT(*)
----------
0
SQL> CREATE TABLE ordsys.secure_data(
2 credit_card_no VARCHAR2(19),
3* social_security_no VARCHAR2(11));
Table created.
SQL> ALTER TABLE ordsys.secure_data
2 ADD CONSTRAINT pk_secure_data
3 PRIMARY KEY (credit_card_no, social_security_no);
Table altered.
SQL> INSERT INTO ordsys.secure_data
2 (credit_card_no, social_security_no)
3 VALUES
4 ('1111-2222-3333-4444', '333-22-4444');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM ordsys.secure_data;
CREDIT_CARD_NO SOCIAL_SECU
------------------- -----------
1111-2222-3333-4444 333-22-4444
SQL> DROP TABLE ordsys.secure_data;
Table dropped.
SQL> SELECT COUNT(*)
2 FROM cdb_objects_ae
3 WHERE object_name = 'SECURE_DATA';
COUNT(*)
----------
0
-- "recyclebin" is owned by each schema
-- as SYS you cannot see what is in a schema recyclebin directly
SQL> SELECT COUNT(*)
2 FROM recyclebin;
COUNT(*)
----------
0
-- "dba_recyclebin" is container-wide and cdb_recyclebin all schemas in all containers
SQL> col original_name format a30
SQL> SELECT owner, object_name, original_name, type, droptime, can_undrop
2 FROM dba_recyclebin;
OWNER OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME
-------- ------------------------------ --------------- ------ --------------------
UWCLASS BIN$nUaTtM1FTq+8gnnLHe/JkQ==$0 PK_SECURE_DATA INDEX 2019-07-04:11:46:18
UWCLASS BIN$MTRCBrtyTMmHppvi7Lu5ag==$0 SECURE_DATA TABLE 2019-07-04:11:46:18
-- you can make a risk more obvious than the following SQL statement.
SQL> SELECT * FROM "ORDSYS"."BIN$MTRCBrtyTMmHppvi7Lu5ag==$0";
CREDIT_CARD_NO SOCIAL_SECU
------------------- -----------
1111-2222-3333-4444 333-22-4444
-- the data can also be access by recovering the table and its data from the recyclebin.
SQL> FLASHBACK TABLE "UWCLASS"."SECURE_DATA" TO BEFORE DROP;
Flashback complete.
SQL> SELECT * FROM ordsys.secure_data;
CREDIT_CARD_NO SOCIAL_SECU
------------------- -----------
1111-2222-3333-4444 333-22-4444
-- DROP TABLE ... PURGE drops the table and does not put it and its data into the recyclebin
SQL> DROP TABLE ordsys.secure_data PURGE;
Table dropped.
SQL> SELECT COUNT(*)
2 FROM cdb_objects_ae
3 WHERE object_name = 'SECURE_DATA';
COUNT(*)
----------
0
SQL> SELECT owner, object_name, original_name, type, droptime, can_undrop
2 FROM dba_recyclebin;
no rows selected |
| |
| RECYCLEBIN Information |
| Dependencies |
| CDB_RECYCLEBIN |
RECYCLEBIN |
RECYCLEBIN$ |
| DBA_RECYCLEBIN |
|
|
|
| Documented |
Yes |
| First Available |
10.1 |
| Security Model |
Each schema can query the RECYCLEBIN object for tables and associated objects from that schema that were dropped and not purged
Each legacy database or container architecture container can query the DBA_RECYCEBIN for tables and associated objects from all schemas within that database/container that were dropped and not purged.
In a container database CDB_RECYCLEBIN can be queried for tables and associated objects from all containers and all schemas that were dropped and not purged.
Recyclebin objects are not part of an export or import but are backed up by RMAN and are replicated by Data Guard. |
| |
| Syntax |
| Empty the schema recyclebin |
PURGE RECYCLEBIN |
SQL> PURGE RECYCLEBIN; |
| Empty the database/container recyclebin |
PURGE DBA_RECYCLEBIN |
SQL> PURGE
DBA_RECYCLEBIN; |
| Recover a recyclebin object |
FLASH TABLE <[SCHEMA_NAME.]<original_table_name> TO BEFORE DROP
[RENAME TO <new_table_name>]; |
SQL> FLASHBACK TABLE "UWCLASS"."SECURE_DATA" TO BEFORE DROP; |