Posted by I’ve always been worried about taking a script that is fine to run in my non-production environments (in particular a DROP script) and accidentally running it in a Production environment, shortly followed by the typing up of a fresh resume to look for a new job once the mistake is discovered 
The standard means of a “least privilege required” model when it comes to Production access is a good step along this road to protecting the database. But ultimately, at one time or another, an administrator with appropriate privileges, good intentions, but (say) bad cut-paste skills might inadvertently run a drop command against a database they did not want to. (A well publicised example of this happened quite recently https://www.theregister.co.uk/2017/02/01/gitlab_data_loss/)
So it would be nice to have a method of adding a ‘double check’ facility so that we can guard against a DROP being run on your production system inadvertently. That way, even if you did run that drop script against Production, you would be “saved from yourself”. Here’s one such possible means you might want to consider. We’ll use a context variable that has to be set in order for DROP commands to be processed.
--
-- Our context
--
SQL> create context DDL_CHECK using ddl_checker;
Context created.
--
-- A routinte to set it
--
SQL> create or replace
2 procedure ddl_checker(p_val varchar2) is
3 begin
4 dbms_session.set_context('DDL_CHECK','ENABLED',p_val);
5 end;
6 /
Procedure created.
--
-- If we are on the Production database, DROP DDL's only proceed is the context variable is set
--
SQL> create or replace
2 trigger ddl_checker_before
3 before drop on scott.schema
4 begin
5 if sys.database_name like '%PROD%' and sys_context('DDL_CHECK','ENABLED') is null
6 then
7 raise_application_error(-20000,'DDL confirmation context is not set');
8 end if;
9 end;
10 /
Trigger created.
--
-- And after every DROP, the context is reset for the next use so it cannot be left on inadvertently
--
SQL> create or replace
2 trigger ddl_checker_after
3 after drop on scott.schema
4 begin
5 ddl_checker(null);
6 end;
7 /
Trigger created.
SQL>
SQL>
SQL> grant execute on ddl_checker to scott;
Grant succeeded.
SQL>
So now we can see what happens when SCOTT attempts some DDL in Production by mistake.
SQL> conn scott/tiger
Connected.
SQL> drop table t purge;
drop table t purge
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: DDL confirmation context is not set
ORA-06512: at line 4
--
-- We must explicitly express our intention to perform a DDL
--
SQL> exec admin.ddl_checker('true')
PL/SQL procedure successfully completed.
--
-- and only then are we allowed to run a DROP command
--
SQL> drop table t purge;
Table dropped.
--
-- and in doing so, we don't have any lingering access
--
SQL> drop table t1 purge;
drop table t1 purge
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: DDL confirmation context is not set
ORA-06512: at line 4
SQL>
I stress – this is not about securing administrator access or replacing your existing processes for auditing or locking down the privileges on your production systems. This is just about that extra check to help your over-worked and/or over-tired administrators from committing a small mistake that becomes a catastrophic error.
Learning is not a spectator sport 