Sometimes during certain operations (like migrations of full or partial datasets) it is needed to limit the DDL operations allowed. In this article we will investigate how to limit and manage this situation.
Database Level Trigger for all DDL operations
Implement a trigger at database level before any DDL to only allow the DDLs issued by a restricted list of database users. For all the other users, an application error will be raised for custom error ORA-20001 displaying the user affected and the event type.
Here is an example of such trigger code:
CREATE OR REPLACE TRIGGER disable_ddl BEFORE DDL ON DATABASE DECLARE
v_user VARCHAR2(100);
v_event VARCHAR2(100);
BEGIN
v_user := sys_context('USERENV', 'SESSION_USER');
v_event := ora_sysevent;
IF user NOT IN ( 'SYS', 'SYSTEM', 'GGADMIN', 'ZDMMIG' ) THEN
raise_application_error(-20001, 'DDL disabled for user: '
|| v_user
|| ' and event: '
|| v_event);
END IF;
END;
/The trigger can be enabled by the owner as needed according to operations schedule:
ALTER TRIGGER "RADU"."DISABLE_DDL" ENABLE;The trigger can be disabled by the owner as needed:
ALTER TRIGGER "RADU"."DISABLE_DDL" DISABLE;Example error stacks
Error raised by an add column attempt
ORA-04088: error during execution of trigger 'RADU.DISABLE_DDL'
ORA-00604: Error occurred at recursive SQL level 1. Check subsequent errors.
ORA-20001: DDL disabled for user: RADU and event: ALTER
ORA-06512: at line 9
04088. 00000 - "error during execution of trigger '%s.%s'"
*Cause: A runtime error occurred during execution of a trigger.
*Action: Check the triggers which were involved in the operation.
Vendor code 4088Error raised by a TRUNCATE operation
Error starting at line : 1 in command -
truncate table "RADU"."SUPPLIER" drop storage
Error report -
ORA-04088: error during execution of trigger 'RADU.DISABLE_DDL'
ORA-00604: Error occurred at recursive SQL level 1. Check subsequent errors.
ORA-20001: DDL disabled for user: RADU and event: TRUNCATE
ORA-06512: at line 9
04088. 00000 - "error during execution of trigger '%s.%s'"
*Cause: A runtime error occurred during execution of a trigger.
*Action: Check the triggers which were involved in the operation.Abbreviations
- DDL – Data Definition Language
References
- Primary Note: Overview of Oracle Data Definition Language (DDL) (Doc ID 1501399.1)
Script tested on: Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 – for Oracle Cloud and Engineered Systems Version 23.7.0.25.03