Manage Learn to apply best practices and optimize your operations.

How to control DDL commands in your database

This tip offers a mechanism to control unauthorized DDL commands against Oracle databases.

For many years, we had problems in our production databases: tables would be dropped or truncated accidentally, or someone would create undesired objects. Also, we sometimes needed to do maintenance and we don't want unauthorized users to change something in the database. To fix these problems, we created a mechanism to control DDL commands against our databases. It has been tested on 8.1.7 and 9.2.

Here are the implementation steps:

1. Create a table TAB_DDL_CONTROL. This table will store all the users and their permission level.

2. Create a table TAB_DDL_CONTROL_LOG (this is optional). This table will keep track of changes made in the TAB_DDL_CONTROL table.

3. Create synonyms for the tables above.

4. Add two initial users into TAB_DDL_CONTROL:

  • 'Admin' (usually this user is used for backups, exports and other admin operations)
  • 'Others' (for any user which is not in the table)

5. Create a trigger TRG_DDL_CONTROL. This trigger will be fired before any CREATE OR DROP OR ALTER OR TRUNCATE ON DATABASE command is issued by anybody. It will check against the TAB_DDL_CONTROL table for current permission levels of the user issuing the DDL command.

6. Create a trigger MT_TAB_DDL_CONTROL. This trigger will be fired before an update on TAB_DDL_CONTROL to add additional information into TAB_DDL_CONTROL and also will insert another record into TAB_DDL_CONTROL_LOG (optional).

7. Script to check and update TAB_DDL_CONTROL. This script will allow turning on and off DDLs for a specific or all users.

Here are the scripts that correspond to the steps above:

 
--1 
CREATE TABLE TAB_DDL_CONTROL
(DDL_USER    VARCHAR2(30)
,DDL_ALLOWED VARCHAR2(1)
,OSUSER      VARCHAR2(30)
,USERNAME    VARCHAR2(30)
,TIMESTAMP   DATE
,DBAONLY     VARCHAR2(1))
/

--2
CREATE TABLE TAB_DDL_CONTROL_LOG
(DDL_USER    VARCHAR2(30)
,DDL_STATUS  VARCHAR2(5)
,DBAONLY     VARCHAR2(1)
,OSUSER      VARCHAR2(30)
,USERNAME    VARCHAR2(30)
,TIMESTAMP   DATE)
/

--3
CREATE PUBLIC SYNONYM TAB_DDL_CONTROL FOR TAB_DDL_CONTROL;
CREATE PUBLIC SYNONYM TAB_DDL_CONTROL_LOG FOR TAB_DDL_CONTROL_LOG;

--4
INSERT INTO TAB_DDL_CONTROL VALUES ('OTHERS','Y',null,null,null,'N');
INSERT INTO TAB_DDL_CONTROL VALUES ('ADMIN','Y',null,null,null,'N');

--5
CREATE OR REPLACE TRIGGER TRG_DDL_CONTROL
BEFORE CREATE OR DROP OR ALTER OR TRUNCATE ON DATABASE

DECLARE

SOURCE               VARCHAR2(100);
W_DDL_ALLOWED        VARCHAR2(1);
W_DBAONLY            VARCHAR2(1); 
W_OSUSER             VARCHAR2(30);  
W_MODULE             VARCHAR2(48);
W_PROGRAM            VARCHAR2(64); 
W_DDL_USER           VARCHAR2(30);

NO_DDL_ALLOWED       EXCEPTION;

BEGIN

  BEGIN 
    SELECT DDL_USER,DDL_ALLOWED INTO W_DDL_USER,W_DDL_ALLOWED FROM
TAB_DDL_CONTROL                        
     WHERE  DDL_USER = USER;

    EXCEPTION

       WHEN NO_DATA_FOUND THEN

            SELECT DDL_USER,DDL_ALLOWED,DBAONLY INTO
W_DDL_USER,W_DDL_ALLOWED,W_DBAONLY
            FROM TAB_DDL_CONTROL                  
            WHERE  DDL_USER = 'OTHERS';

    END;

-- *****************************************************************
-- OPTIONAL: If you want to allow some DDL through your application:

    SELECT OSUSER,NVL(PROGRAM,'NODDL'),NVL(MODULE,'NODDL') INTO
W_OSUSER,W_PROGRAM,W_MODULE
    FROM V$SESSION                                    
    WHERE SID = (SELECT DISTINCT SID FROM V$MYSTAT);

-- This means that any application different from SQL*Plus, SQL*Navigator 
-- and PowerBuilder can create user and database links.

    IF          UPPER(W_PROGRAM) NOT LIKE  '%PLUS%'
           AND  UPPER(W_PROGRAM) NOT LIKE  '%SQLNAV%'
           AND  UPPER(W_PROGRAM) NOT LIKE  '%PB%'
           AND  UPPER(W_MODULE)  NOT LIKE  '%PLUS%'
           AND  UPPER(W_MODULE)  NOT LIKE  'SQLNAV%' 
           AND  UPPER(W_MODULE)  NOT LIKE  '%PB%'
           AND ( SYS.DICTIONARY_OBJ_TYPE = 'USER'
              OR SYS.DICTIONARY_OBJ_TYPE = 'DATABASE LINK')
           THEN 
              NULL;
    ELSE

-- END OF OPTIONAL PART
-- ****************************************************

       IF W_DDL_ALLOWED = 'N'
          THEN
          RAISE NO_DDL_ALLOWED;
       ELSE
          IF    W_DBAONLY = 'Y' 
            AND W_DDL_USER = 'OTHERS'
                THEN

-- DBAOSUser: hardcode the OSUser for the list of DBAs that will be
-- allowed to issue DDL. When the permission for DDLs is on only for DBAS.

                   IF  UPPER(W_OSUSER)  NOT IN ('dbaOSUuser')
                       THEN
                          RAISE NO_DDL_ALLOWED; 
                   END IF;

          END IF;
       END IF; 
    END IF;

  EXCEPTION

         WHEN NO_DDL_ALLOWED THEN
           RAISE_APPLICATION_ERROR(-20001,'NO DDLS ARE ALLOWED AT THIS TIME');
         WHEN OTHERS THEN
           RAISE;
  END;

/

-- 6 
alter TRIGGER TRG_DDL_CONTROL compile ;

CREATE OR REPLACE TRIGGER MT_TAB_DDL_CONTROL
BEFORE UPDATE ON "TAB_DDL_CONTROL"
FOR EACH ROW
DECLARE
W_OSUSER      VARCHAR2(30);
W_USERNAME    VARCHAR2(30);
W_TIMESTAMP   DATE;
BEGIN
    SELECT USERNAME,OSUSER,SYSDATE
    INTO W_USERNAME,W_OSUSER,W_TIMESTAMP
    FROM V$SESSION
    WHERE SID = (SELECT DISTINCT SID FROM V$MYSTAT);
    :NEW.OSUSER    := W_OSUSER;
    :NEW.USERNAME  := W_USERNAME;
    :NEW.TIMESTAMP := W_TIMESTAMP;
    IF :NEW.DDL_ALLOWED = 'N'
        THEN
       :NEW.DBAONLY := 'N';
    END IF;

    INSERT INTO TAB_DDL_CONTROL_LOG VALUES (
    :NEW.DDL_USER   
    ,DECODE(:NEW.DDL_ALLOWED,'Y','OPEN','CLOSE')
    ,:NEW.DBAONLY
    ,W_OSUSER
    ,W_USERNAME
    ,W_TIMESTAMP);
END;
/


--7
set echo off
set veri off
set feed off
SET LINE 100
COL DBAONLY FORMAT A10
COL CURRENT FORMAT A7
COL DDL_STATUS FORMAT A10
COL USERNAME FORMAT A15
SELECT  SUBSTR(DDL_USER,1,8) DDL_USER,
        DECODE(DDL_allowed,'N','CLOSE','OPEN') DDL_STATUS,
        DECODE(DBAONLY,'Y','ONLY DBAS','N','EVERYBODY',DBAONLY) DBAONLY,
        SUBSTR(OSUSER,1,15) OSUSER,
        USERNAME,
        TO_CHAR(TIMESTAMP,'DD-MON-YYYY HH24:MI:SS') TIMESTAMP
        FROM TAB_DDL_CONTROL
/
accept DDL_ALLOWED char prompt 'Enter DDL_ALLOWED  (Y/N) : N > ';
accept DBAONLY     char prompt 'Enter ONLY DBAS    (Y/N) : Y > ';
accept USERNAME    char prompt 'Enter USERNAME  (ADMIM/OTHERS) : OTHERS > ';

update tab_ddL_control
set  DDL_ALLOWED = NVL(UPPER('&DDL_ALLOWED'),'N')
    ,DBAONLY     = NVL(UPPER('&DBAONLY'),'Y')
WHERE DDL_USER = NVL(UPPER('&USERNAME'),'OTHERS')
/
commit;
UNDEF USERNAME
set veri on
set feed on

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close