No DDL in Production Environment

There might be a couple of reasons you want to disable DDL in a production environment (other than the normal operations of the database). One being you don't have your schemas locked down (i.e. no service accounts). Another might be the need to track all changes in the environment for reporting/auditing purposes.

It's fairly easy to do.

First, create an Application Context:

CREATE OR REPLACE
CONTEXT secure_ddl
  USING secure_ddl
  ACCESSED GLOBALLY;

The USING clause is the package/procedure/function that will set the secure_ddl context.

Next, create a procedure that will set the context using DBMS_SESSION:

CREATE OR REPLACE
PROCEDURE secure_ddl( p_ticket IN VARCHAR2 )
AS
  l_sessionid NUMBER := SYS_CONTEXT( 'USERENV', 'SESSIONID' );
BEGIN
  dbms_session.set_identifier( l_sessionid );

  dbms_session.set_context
    ( namespace => 'SECURE_DDL_CONTEXT',
      attribute => 'CAN_DO_DDL',
      value => 'Y',
      username => SYS_CONTEXT( 'USERENV', 'SESSION_USER' ),
      client_id => l_sessionid );
END secure_ddl;
/

Finally, create a trigger that fires BEFORE DDL.

CREATE OR REPLACE
TRIGGER enable_ddl
  BEFORE DDL ON DATABASE
  WHEN ( ora_dict_obj_owner = 'APPLICATION_OWNER' )
BEGIN
  IF SYS_CONTEXT( 'SECURE_DDL_CONTEXT', 'CAN_DO_DDL' ) <> 'Y' 
    OR SYS_CONTEXT( 'SECURE_DDL_CONTEXT', 'CAN_DO_DDL' ) IS NULL
  THEN
    raise_application_error( -20001, 'must enable DDL' );
  END IF;
END enable_ddl;
/

So let's test it out.

I'll create 2 users, the APPLICATION_OWNER (see trigger) and TESTUSER. APPLICATION_OWNER is the schema that will store all your application objects and code. TESTUSER will be granted the DBA role which will allow them access to create objects in the APPLICATION_OWNER schema.

CREATE USER application_owner IDENTIFIED BY application_owner
  DEFAULT TABLESPACE users
  QUOTA 1G ON users;

GRANT create session, resource TO application_owner;

CREATE USER testuser IDENTIFIED BY testuser
  DEFAULT TABLESPACE users
  QUOTA 5M ON users;

GRANT dba TO testuser;

Let's test it out. Login as TESTUSER and create a table.

TESTUSER@RMDEV2>set sqlprompt TESTUSER@11G>
TESTUSER@11G>CREATE TABLE application_owner.t ( x NUMBER );
CREATE TABLE application_owner.t ( x NUMBER )
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: must enable DDL
ORA-06512: at line 5

Nice. A user with DBA privileges cannot perform DDL in the APPLICATION_OWNER schema. Since the SECURE_DDL procedure is not in the SYS schema, TESTUSER can call it to enable DDL.

TESTUSER@11G>EXEC CJUSTICE.SECURE_DDL( 'TICKET 1' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
TESTUSER@11G>CREATE TABLE application_owner.t( x NUMBER );

Table created.

So what about APPLICATION_OWNER? Can that user perform DDL on objects in it's own schema (see #1 above)? I've logged into the APPLICATION_OWNER schema:

APPLICATION_OWNER@11G>CREATE TABLE t ( x NUMBER );
CREATE TABLE t ( x NUMBER )
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: must enable DDL
ORA-06512: at line 5

Very cool.

Again, this method could be helpful if:
1. You don't have your schema (application) accounts locked down
2. You want to track all DDL in your production environment.

The procedure can be easily expanded into a series of calls, specifically ENABLE_DDL and DISABLE_DDL. When you call ENABLE_DDL, a record is written to a table with the ticket number and the start time. When you call DISABLE_DDL, the end time is captured and you can further query DBA_OBJECTS and capture all objects that changed during that window.

In a future post, I'll work to expand on this concept to provide this kind of tracking.