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
