Oracle Recyclebin
Versions 10.1 - 19.3

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;

Related Topics
System Privileges