Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted May 27, 2014

Are There Safeguards to Prevent the DBA from Dropping a Table?

By David Fitzjarrell

A recent IT-related survey had in its list of questions one rather interesting offering:

"Are there any safeguards preventing the DBA from accidentally dropping a table?"

That is probably not a commonly considered aspeect of data security however it is an issue that should be voiced and addressed. How do you ensure that the DBA doesn't, or can't, drop a table accidentally? Oracle has at least two ways to ensure that a table cannot be accidentally dropped but there are some limitations to those methods. Let's look at those methods to see which one works for the DBA account.

For a DBA who's been in the 'trenches' for a while the first (and possibly most obvious) method is a schema-level trigger to catch the act and prevent it:


SQL> connect / as sysdba
Connected.
SQL> create or replace trigger drop_trigger
  2  before drop on bingnorfer.schema
  3  begin
  4      raise_application_error(
  5        num => -20999,
  6        msg => 'Objects cannot be dropped.');
  7  end;
  8  /

Trigger created.

SQL>

The trigger is in place, so let's test it first as the schema owner, then as the DBA:


SQL> connect bingnorfer/#@#@#@#@#@#@
Connected.
SQL> drop table empdrop;
drop table empdrop
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20999: Objects cannot be dropped.
ORA-06512: at line 2


SQL>

So far, so good, the table we wanted to drop can't be dropped by the schema owner because of the schema-level trigger. Can the DBA drop the table?


SQL> connect dfitzj/^%^%^%^%^%^%^%^%^%
Connected.
SQL> drop table bingnorfer.empdrop;

Table dropped.

SQL>

That's disconcerting, the trigger doesn't stop a DBA from dropping the table. But, if you read the documentation on such triggers, you'd know they only apply to the schema owner, and no one else. Having such a trigger does prevent accidentally dropping an object from a given schema if the owner is the one attempting to drop it, it just doesn't stop the DBA from doing so.

It's possible to create a modified DBA role, absent the DROP ANY privileges, which does stop the DBA from accidentally dropping anything except what he or she owns. In 11.2.0.3 the DBA role currently has the following list of DROP privileges:


ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
DBA                            DROP TABLESPACE                          YES
DBA                            DROP ROLLBACK SEGMENT                    YES
DBA                            DROP ANY INDEX                           YES
DBA                            DROP ANY INDEXTYPE                       YES
DBA                            DROP ANY EDITION                         YES
DBA                            DROP ANY MINING MODEL                    YES
DBA                            DROP ANY CUBE BUILD PROCESS              YES
DBA                            DROP ANY TRIGGER                         YES
DBA                            DROP ANY MATERIALIZED VIEW               YES
DBA                            DROP ANY MEASURE FOLDER                  YES
DBA                            DROP ANY VIEW                            YES
DBA                            DROP ANY LIBRARY                         YES
DBA                            DROP ANY DIMENSION                       YES
DBA                            DROP ANY EVALUATION CONTEXT              YES
DBA                            DROP ANY CUBE                            YES
DBA                            DROP ANY TABLE                           YES
DBA                            DROP PUBLIC DATABASE LINK                YES
DBA                            DROP ANY TYPE                            YES
DBA                            DROP ANY CONTEXT                         YES
DBA                            DROP ANY SYNONYM                         YES
DBA                            DROP PUBLIC SYNONYM                      YES
DBA                            DROP ANY DIRECTORY                       YES
DBA                            DROP ANY RULE SET                        YES
DBA                            DROP ANY CLUSTER                         YES
DBA                            DROP ANY SEQUENCE                        YES
DBA                            DROP ANY ROLE                            YES
DBA                            DROP ANY PROCEDURE                       YES
DBA                            DROP PROFILE                             YES
DBA                            DROP ANY OUTLINE                         YES
DBA                            DROP USER                                YES
DBA                            DROP ANY OPERATOR                        YES
DBA                            DROP ANY RULE                            YES
DBA                            DROP ANY SQL PROFILE                     YES
DBA                            DROP ANY ASSEMBLY                        YES
DBA                            DROP ANY CUBE DIMENSION                  YES

35 rows selected.

SQL>

Eliminating all of those DROP privileges by creating a modified DBA role could severely alter how a DBA would do his/her job, possibly causing him or her to connect using "/ as sysdba" to bypass the restrictions, which wouldn't be a good thing. Eliminating the DROP ANY privileges might be a better option. Creating the new, restricted DBA role:


SQL> create role dba_mod;

Role created.

SQL> grant CREATE SESSION to dba_mod;

Grant succeeded.

SQL> grant ALTER SESSION to dba_mod;

Grant succeeded.

SQL> grant DROP TABLESPACE to dba_mod;

Grant succeeded.

SQL> grant BECOME USER to dba_mod;

Grant succeeded.

SQL> grant DROP ROLLBACK SEGMENT to dba_mod;

Grant succeeded.

SQL> grant SELECT ANY TABLE to dba_mod;

Grant succeeded.

SQL> grant INSERT ANY TABLE to dba_mod;

Grant succeeded.

SQL> grant UPDATE ANY TABLE to dba_mod;

Grant succeeded.

SQL> grant SELECT ANY SEQUENCE to dba_mod;

Grant succeeded.

SQL> grant CREATE ROLE to dba_mod;

Grant succeeded.

SQL> grant EXECUTE ANY PROCEDURE to dba_mod;

Grant succeeded.

SQL> grant ALTER PROFILE to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY DIRECTORY to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY LIBRARY to dba_mod;

Grant succeeded.

SQL> grant EXECUTE ANY LIBRARY to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY INDEXTYPE to dba_mod;

Grant succeeded.

SQL> grant DEQUEUE ANY QUEUE to dba_mod;

Grant succeeded.

SQL> grant EXECUTE ANY EVALUATION CONTEXT to dba_mod;

Grant succeeded.

SQL> grant EXPORT FULL DATABASE to dba_mod;

Grant succeeded.

SQL> grant CREATE RULE to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY SQL PROFILE to dba_mod;

Grant succeeded.

SQL> grant ADMINISTER ANY SQL TUNING SET to dba_mod;

Grant succeeded.

SQL> grant CHANGE NOTIFICATION to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY MINING MODEL to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY CUBE DIMENSION to dba_mod;

Grant succeeded.

SQL> grant CREATE CUBE to dba_mod;

Grant succeeded.

SQL> grant ALTER ROLLBACK SEGMENT to dba_mod;

Grant succeeded.

SQL> grant DELETE ANY TABLE to dba_mod;

Grant succeeded.

SQL> grant ALTER DATABASE to dba_mod;

Grant succeeded.

SQL> grant FORCE ANY TRANSACTION to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY PROCEDURE to dba_mod;

Grant succeeded.

SQL> grant UNDER ANY TYPE to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY LIBRARY to dba_mod;

Grant succeeded.

SQL> grant CREATE DIMENSION to dba_mod;

Grant succeeded.

SQL> grant DEBUG ANY PROCEDURE to dba_mod;

Grant succeeded.

SQL> grant CREATE RULE SET to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY RULE SET to dba_mod;

Grant succeeded.

SQL> grant ANALYZE ANY DICTIONARY to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY EDITION to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY ASSEMBLY to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY CUBE to dba_mod;

Grant succeeded.

SQL> grant SELECT ANY CUBE to dba_mod;

Grant succeeded.

SQL> grant RESTRICTED SESSION to dba_mod;

Grant succeeded.

SQL> grant CREATE TABLESPACE to dba_mod;

Grant succeeded.

SQL> grant ALTER TABLESPACE to dba_mod;

Grant succeeded.

SQL> grant CREATE USER to dba_mod;

Grant succeeded.

SQL> grant ALTER USER to dba_mod;

Grant succeeded.

SQL> grant LOCK ANY TABLE to dba_mod;

Grant succeeded.

SQL> grant CREATE VIEW to dba_mod;

Grant succeeded.

SQL> grant GRANT ANY ROLE to dba_mod;

Grant succeeded.

SQL> grant CREATE TRIGGER to dba_mod;

Grant succeeded.

SQL> grant CREATE TYPE to dba_mod;

Grant succeeded.

SQL> grant EXECUTE ANY OPERATOR to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY DIMENSION to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY DIMENSION to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY OUTLINE to dba_mod;

Grant succeeded.

SQL> grant ADMINISTER DATABASE TRIGGER to dba_mod;

Grant succeeded.

SQL> grant RESUMABLE to dba_mod;

Grant succeeded.

SQL> grant FLASHBACK ANY TABLE to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY RULE SET to dba_mod;

Grant succeeded.

SQL> grant EXECUTE ANY RULE SET to dba_mod;

Grant succeeded.

SQL> grant IMPORT FULL DATABASE to dba_mod;

Grant succeeded.

SQL> grant EXECUTE ANY RULE to dba_mod;

Grant succeeded.

SQL> grant EXECUTE ANY PROGRAM to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY EDITION to dba_mod;

Grant succeeded.

SQL> grant CREATE ASSEMBLY to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY ASSEMBLY to dba_mod;

Grant succeeded.

SQL> grant CREATE CUBE DIMENSION to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY CUBE BUILD PROCESS to dba_mod;

Grant succeeded.

SQL> grant UPDATE ANY CUBE DIMENSION to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY TABLE to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY INDEX to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY SEQUENCE to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY ROLE to dba_mod;

Grant succeeded.

SQL> grant ANALYZE ANY to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY OPERATOR to dba_mod;

Grant succeeded.

SQL> grant CREATE INDEXTYPE to dba_mod;

Grant succeeded.

SQL> grant UNDER ANY TABLE to dba_mod;

Grant succeeded.

SQL> grant SELECT ANY DICTIONARY to dba_mod;

Grant succeeded.

SQL> grant GRANT ANY OBJECT PRIVILEGE to dba_mod;

Grant succeeded.

SQL> grant CREATE EVALUATION CONTEXT to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY EVALUATION CONTEXT to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY RULE to dba_mod;

Grant succeeded.

SQL> grant CREATE JOB to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY JOB to dba_mod;

Grant succeeded.

SQL> grant CREATE MINING MODEL to dba_mod;

Grant succeeded.

SQL> grant INSERT ANY CUBE DIMENSION to dba_mod;

Grant succeeded.

SQL> grant UPDATE ANY CUBE BUILD PROCESS to dba_mod;

Grant succeeded.

SQL> grant ALTER SYSTEM to dba_mod;

Grant succeeded.

SQL> grant AUDIT SYSTEM to dba_mod;

Grant succeeded.

SQL> grant UNLIMITED TABLESPACE to dba_mod;

Grant succeeded.

SQL> grant CREATE ROLLBACK SEGMENT to dba_mod;

Grant succeeded.

SQL> grant COMMENT ANY TABLE to dba_mod;

Grant succeeded.

SQL> grant CREATE CLUSTER to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY INDEX to dba_mod;

Grant succeeded.

SQL> grant DROP PUBLIC DATABASE LINK to dba_mod;

Grant succeeded.

SQL> grant CREATE PROFILE to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY MATERIALIZED VIEW to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY TYPE to dba_mod;

Grant succeeded.

SQL> grant UNDER ANY VIEW to dba_mod;

Grant succeeded.

SQL> grant EXECUTE ANY INDEXTYPE to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY OUTLINE to dba_mod;

Grant succeeded.

SQL> grant ADMINISTER RESOURCE MANAGER to dba_mod;

Grant succeeded.

SQL> grant MANAGE SCHEDULER to dba_mod;

Grant succeeded.

SQL> grant MANAGE FILE GROUP to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY MINING MODEL to dba_mod;

Grant succeeded.

SQL> grant SELECT ANY MINING MODEL to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY MEASURE FOLDER to dba_mod;

Grant succeeded.

SQL> grant DELETE ANY MEASURE FOLDER to dba_mod;

Grant succeeded.

SQL> grant CREATE TABLE to dba_mod;

Grant succeeded.

SQL> grant BACKUP ANY TABLE to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY CLUSTER to dba_mod;

Grant succeeded.

SQL> grant DROP PUBLIC SYNONYM to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY VIEW to dba_mod;

Grant succeeded.

SQL> grant CREATE SEQUENCE to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY SEQUENCE to dba_mod;

Grant succeeded.

SQL> grant FORCE TRANSACTION to dba_mod;

Grant succeeded.

SQL> grant CREATE PROCEDURE to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY PROCEDURE to dba_mod;

Grant succeeded.

SQL> grant ALTER RESOURCE COST to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY TYPE to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY OPERATOR to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY INDEXTYPE to dba_mod;

Grant succeeded.

SQL> grant ENQUEUE ANY QUEUE to dba_mod;

Grant succeeded.

SQL> grant ON COMMIT REFRESH to dba_mod;

Grant succeeded.

SQL> grant DEBUG CONNECT SESSION to dba_mod;

Grant succeeded.

SQL> grant EXECUTE ANY CLASS to dba_mod;

Grant succeeded.

SQL> grant MANAGE ANY FILE GROUP to dba_mod;

Grant succeeded.

SQL> grant EXECUTE ANY ASSEMBLY to dba_mod;

Grant succeeded.

SQL> grant EXECUTE ASSEMBLY to dba_mod;

Grant succeeded.

SQL> grant COMMENT ANY MINING MODEL to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY CUBE DIMENSION to dba_mod;

Grant succeeded.

SQL> grant DELETE ANY CUBE DIMENSION to dba_mod;

Grant succeeded.

SQL> grant SELECT ANY CUBE DIMENSION to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY TABLE to dba_mod;

Grant succeeded.

SQL> grant CREATE SYNONYM to dba_mod;

Grant succeeded.

SQL> grant CREATE PUBLIC SYNONYM to dba_mod;

Grant succeeded.

SQL> grant AUDIT ANY to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY TRIGGER to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY TRIGGER to dba_mod;

Grant succeeded.

SQL> grant DROP PROFILE to dba_mod;

Grant succeeded.

SQL> grant GRANT ANY PRIVILEGE to dba_mod;

Grant succeeded.

SQL> grant CREATE LIBRARY to dba_mod;

Grant succeeded.

SQL> grant CREATE OPERATOR to dba_mod;

Grant succeeded.

SQL> grant MERGE ANY VIEW to dba_mod;

Grant succeeded.

SQL> grant ADMINISTER SQL TUNING SET to dba_mod;

Grant succeeded.

SQL> grant UPDATE ANY CUBE to dba_mod;

Grant succeeded.

SQL> grant INSERT ANY MEASURE FOLDER to dba_mod;

Grant succeeded.

SQL> grant ADMINISTER SQL MANAGEMENT OBJECT to dba_mod;

Grant succeeded.

SQL> grant MANAGE TABLESPACE to dba_mod;

Grant succeeded.

SQL> grant DROP USER to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY CLUSTER to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY SYNONYM to dba_mod;

Grant succeeded.

SQL> grant CREATE DATABASE LINK to dba_mod;

Grant succeeded.

SQL> grant CREATE PUBLIC DATABASE LINK to dba_mod;

Grant succeeded.

SQL> grant CREATE MATERIALIZED VIEW to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY MATERIALIZED VIEW to dba_mod;

Grant succeeded.

SQL> grant EXECUTE ANY TYPE to dba_mod;

Grant succeeded.

SQL> grant QUERY REWRITE to dba_mod;

Grant succeeded.

SQL> grant GLOBAL QUERY REWRITE to dba_mod;

Grant succeeded.

SQL> grant MANAGE ANY QUEUE to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY CONTEXT to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY EVALUATION CONTEXT to dba_mod;

Grant succeeded.

SQL> grant ALTER ANY RULE to dba_mod;

Grant succeeded.

SQL> grant ADVISOR to dba_mod;

Grant succeeded.

SQL> grant SELECT ANY TRANSACTION to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY SQL PROFILE to dba_mod;

Grant succeeded.

SQL> grant READ ANY FILE GROUP to dba_mod;

Grant succeeded.

SQL> grant CREATE EXTERNAL JOB to dba_mod;

Grant succeeded.

SQL> grant CREATE ANY CUBE to dba_mod;

Grant succeeded.

SQL> grant CREATE MEASURE FOLDER to dba_mod;

Grant succeeded.

SQL> grant CREATE CUBE BUILD PROCESS to dba_mod;

Grant succeeded.

SQL> grant FLASHBACK ARCHIVE ADMINISTER to dba_mod;

Grant succeeded.

SQL>

Modifying our DBAs privileges:


SQL> revoke dba from dfitzj;

Revoke succeeded.

SQL> grant dba_mod to dfitzj;

Grant succeeded.

SQL>

It's now time to again test if our DBA can drop the bingnorfer.empdrop table:


SQL> connect dfitzj/^%^%^%^%^%^%^%^%^%^%^
Connected.
SQL> drop table bingnorfer.empdrop;
drop table bingnorfer.empdrop
                      *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>

It appears we have succeeded in preventing the table from being dropped; removing the DROP ANY privileges from the DBA_MOD role keeps objects that aren't owned by the specified DBA account from being dropped from the database, including triggers, indexes, packages, procedures, etc. It is a limited victory, however, as a DBA with access to the 'oracle' user account on Linux/UNIX or an account granted the ORA_DBA role in a Windows environment can bypass these restrictions:


SQL> connect / as sysdba
Connected.
SQL> drop table bingnorfer.empdrop;

Table dropped.

SQL>

It seems the situation becomes one of ensuring that an accidentally dropped table can be recovered/restored. If the recyclebin hasn't been disabled for 'performance' reasons (an ambiguous 'reason' since the recyclebin doesn't usually produce any performance issues) it's a simple task to flashback the missing table to before it was dropped:


SQL> flashback table bingnorfer.empdrop to before drop;

Flashback complete.

SQL>

The table has been recovered/restored to its state just before the drop. A DBA exercise afterwards is to rename any indexes present on the table as they still have the recyclebin names:


SQL> select index_name
  2  from user_indexes
  3  where table_name = 'EMPDROP'
  4  /

INDEX_NAME
------------------------------
EMPDROP_IDX2
EMPDROP_IDX1

SQL> connect / as sysdba
Connected.
SQL> drop table bingnorfer.empdrop;

Table dropped.

SQL> flashback table bingnorfer.empdrop to before drop;

Flashback complete.

SQL> connect bingnorfer/#@#@#@#@#@#@#@#@#@#@
Connected.
SQL> select * from user_ind_columns
  2  where table_name = 'EMPDROP'
  3  /

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
--------------- ------------- ----------- ----
BIN$843BovlIRAGGlUqlWe5q/Q==$0 EMPDROP
EMPNO
              1            22           0 ASC

BIN$B62aeRx+RlerON0vDt1+dA==$0 EMPDROP
DEPTNO
              1            22           0 ASC


SQL>

We know which recyclebin name goes with the original index name so the indexes can be renamed:


SQL> alter index "BIN$843BovlIRAGGlUqlWe5q/Q==$0" rename to empdrop_idx1;

Index altered.

SQL> alter index "BIN$B62aeRx+RlerON0vDt1+dA==$0" rename to empdrop_idx2;

Index altered.

SQL> select * from user_ind_columns
  2  where table_name = 'EMPDROP'
  3  /

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
--------------- ------------- ----------- ----
EMPDROP_IDX1                   EMPDROP
EMPNO
              1            22           0 ASC

EMPDROP_IDX2                   EMPDROP
DEPTNO
              1            22           0 ASC


SQL>

This table was small (less than 20 rows) so the flashback and rename were executed fairly quickly. What if the table has more data:


SQL> select count(*) from empdrop;

  COUNT(*)
----------
   9437184

SQL> SQL> select * from user_ind_columns
  2  where table_name = 'EMPDROP'
  3  /

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
--------------- ------------- ----------- ----
BIN$OfyFePmsTPyt5qYQzxXlpw==$0 EMPDROP
EMPNO
              1            22           0 ASC

BIN$P768D2zRQ2mtGkwBkr42SA==$0 EMPDROP
DEPTNO
              1            22           0 ASC


SQL> set timing on
SQL> alter index "BIN$OfyFePmsTPyt5qYQzxXlpw==$0" rename to empdrop_idx1;

Index altered.

Elapsed: 00:00:00.00
SQL> alter index "BIN$P768D2zRQ2mtGkwBkr42SA==$0" rename to empdrop_idx2;

Index altered.

Elapsed: 00:00:00.00
SQL> 

The rename really took no time at all; such an act does lock the table so if the table is being used (the table was restored using flashback table and sessions immediately started executing DML against it) you may need to wait for a 'quiet time' in DEV/TEST/QA to run the rename. Since an outage would be required for PROD you should have time to recover the table and rename the indexes before anyone starts using it.

What if the table were dropped with the PURGE option? At that point flashback table wouldn't work as there would be no recyclebin object to restore. You would then need an RMAN backup of the table to recover it to its last known state. Yes, an export of the table would restore the structure and indexes but wouldn't restore any of the data inserted or updated since the export was taken so that isn't really a good option to rely upon. In Oracle 12c you can use RMAN to recover a single table; RMAN goes through the process of creating an auxiliary database, restores the tablespaces necessary to recover the table or tables and associated indexes, creates a data pump export then imports the desired tables into the target database. The following RMAN command accomplishes this, again in Oracle 12c:


recover table bingnorfer.empdrop
until time 'SYSDATE'
auxiliary destination '/tmp/oracle12c/recover'
datapump destination '/tmp/recover12c/dumpfiles'
dump file 'empdrop_exp_dump.dmp';

In 11gR2 and earlier releases it will be necessary to create an empty auxiliary database, containing the SYSTEM, SYSAUX and UNDO tablespaces (just the SYSTEM and ROLLBACK tablespaces for 9i and earlier releases) as well as the tablespace where the dropped table resides. If there are indexes on the table the associated index tablespace must also be created. Perform an incomplete recovery of the database, using a recovered backup controlfile, to the point in time to just before the drop. Export the table from the auxiliary database then import it into the target database. Such a process is covered in the online Oracle documentation found at http://tahiti.oracle.com.

It isn't an easy task to prevent a DBA from accidentally dropping a table, especially if he or she is connected using "/ as sysdba", a connection that should really only be used to create, drop, startup or shutdown a database. It can be done by implementing a modified DBA role, but those DBAs with database server access as 'oracle' can easily and quickly bypass those restrictions. Managing database server access as 'oracle' is a topic for another article, but it is necessary to be aware such connections can be made to a database and tables can be carelessly or inadvertently dropped. It is also necessary to know how to restore dropped tables to a database using both flashack table and the more involved RMAN procedures.

"Ooops, I dropped an important table." That shouldn't be a phrase you hear but in the rush to get things done sometimes the unthinkable happens. If a table does get dropped from PROD you have ways to restore and recover that table to put the database, and the associated application, back in working order. Hopefully you won't need them.

See all articles by David Fitzjarrell



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM