Monday, December 7, 2009

Restrict DDL on a system

CREATE OR REPLACE TRIGGER SYS.restrict_ddl_in_db
BEFORE ddl ON DATABASE
DECLARE
machinename VARCHAR2(64);
message VARCHAR2(150) := 'You have no rights to execute DDL statements on DEVELOPBETA! Call the DBA! ';
BEGIN
SELECT SYS_CONTEXT ('USERENV', 'host') INTO machinename FROM DUAL;
IF machinename IS NULL then
message := message || ' ' || 'NULL';
else
message := message || ' ' || machinename;
end if;
IF INSTR(upper(machinename),'*****WORKGROUP\XPSP3-WBB',1) <> 0
OR INSTR(lower(machinename),'bg\fed',1) <> 0
OR INSTR(lower(machinename),'bg\plamen',1) <> 0
OR INSTR(lower(machinename),'developbeta',1) <> 0
OR INSTR(lower(machinename),'oralin1',1) <> 0
OR machinename IS NULL
THEN
null;
ELSE
RAISE_APPLICATION_ERROR (num => -20000,msg => message);
END IF;
END;
/

No comments: