Oracle Trigger Encryption – Wrapping Database Trigger’s Source Code Without Encryption

oracleplsqlplsql-developer

I'm going to wrap my source of database logon trigger. i run the command,
"wrap iname='E:\block_tools_trigger_HR.sql' oname='E:\block_tools_trigger_HR.plb'".
plb file created without any error but code not encrypted.

Source code of trigger is given blow:

CREATE OR REPLACE TRIGGER block_tools_trigger_HR
AFTER LOGON ON HRMS.SCHEMA
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT upper(program) INTO v_prog
FROM sys.v_$session
WHERE  audsid = USERENV('SESSIONID')
AND  audsid != 0
AND  ROWNUM = 1;
IF UPPER(USERENV('TERMINAL')) NOT IN ('MOAZZAM-LT') THEN
IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR --OR — Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR --    — SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR --— PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR --  — Business Objects
UPPER(v_prog) LIKE '%EXCEL%'  OR --    — MS-Excel plug-in
UPPER(v_prog) LIKE '%SQLPLUS%' OR --     — SQLPLUS
UPPER(v_prog) LIKE '%FRMBLD%' OR
UPPER(v_prog) LIKE '%IFBLD60%' or upper(v_prog) is null
THEN
RAISE_APPLICATION_ERROR(-20983, 'You are not allowed to login in '||v_prog);
END IF;
END IF;
END;

Best Answer

Oracle's wrap program doesn't wrap triggers. The way to do this is to move the code into a package or a stand-alone procedure, and make the trigger a one line call to invoke this code.

See the documentation on the limitations of wrapping: https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/wrap.htm#LNPLS01602

In your case, something like the following (untested) should work:

CREATE OR REPLACE PROCEDURE block_tools AS
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT upper(program) INTO v_prog
FROM sys.v_$session
WHERE  audsid = USERENV('SESSIONID')
AND  audsid != 0
AND  ROWNUM = 1;
IF UPPER(USERENV('TERMINAL')) NOT IN ('MOAZZAM-LT') THEN
IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR --OR — Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR --    — SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR --— PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR --  — Business Objects
UPPER(v_prog) LIKE '%EXCEL%'  OR --    — MS-Excel plug-in
UPPER(v_prog) LIKE '%SQLPLUS%' OR --     — SQLPLUS
UPPER(v_prog) LIKE '%FRMBLD%' OR
UPPER(v_prog) LIKE '%IFBLD60%' or upper(v_prog) is null
THEN
RAISE_APPLICATION_ERROR(-20983, 'You are not allowed to login in '||v_prog);
END IF;
END IF;
END;
/

CREATE OR REPLACE TRIGGER block_tools_trigger_HR
AFTER LOGON ON HRMS.SCHEMA
CALL block_tools;

All I've done is move all the code into a stored procedure, and redefined the trigger to CALL that procedure.

Related Question