Securing Code

Let us say , you want to secure  your code ( protecting your intellectual property  OR internal logic OR some other sensitive information or for  any other reason ) ,  Oracle enables us to do this via WRAP utility .  This is offered in two forms ( WRAP os utility or DBMS_DDL PL/SQL package ) . 

C:\securecode>type PKS_HR__EMPLOYEE.SQL
CREATE OR REPLACE
PACKAGE  pkg_hr__employee
AS
PROCEDURE check_emp(
    emp_no NUMBER);
END PKG_HR__EMPLOYEE;
/

C:\securecode>type PKB_HR__EMPLOYEE.SQL
CREATE OR REPLACE
PACKAGE body pkg_hr__employee
AS
PROCEDURE check_emp(
    emp_no NUMBER)
AS
BEGIN
  dbms_output.put_line('Validating  Employee' || emp_no) ;
  /****************
  ...
  ...
  Do some process
  *************/
END ;
END PKG_HR__EMPLOYEE;
/

C:\securecode>sqlplus zahir@dbdev

SQL*Plus: Release 11.2.0.2.0 Production on Sat Jul 2 22:31:32 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @PKS_HR__EMPLOYEE.SQL

Package created.

SQL> @PKB_HR__EMPLOYEE.SQL

Package body created.

SQL> set pagesize 0
SQL> Select text from all_source where name='PKG_HR__EMPLOYEE' and owner ='ZAHIR';
PACKAGE  pkg_hr__employee
AS
PROCEDURE check_emp(
    emp_no NUMBER);
END PKG_HR__EMPLOYEE;
PACKAGE body pkg_hr__employee
AS
PROCEDURE check_emp(
    emp_no NUMBER)
AS
BEGIN
  dbms_output.put_line('Validating  Employee' || emp_no) ;
  /****************
  ...
  ...
  Do some process
  *************/
END ;
END PKG_HR__EMPLOYEE;

19 rows selected.

As you can see , the entire source code can be visible . This could be potential security issue . We can obfuscate the code with wrap utility . When wrap the PL/SQL code , the new file is created with the default extension .plb in the same directory as the source code . But it can be placed in any directory .

When you view the file , the  body of the code is scrambled . And that whats you see when you look at the data dictionary . You may want to wrap only the package body not the package spec , as the spec specifies the interface  to the third parties ( developers , vendors ,... )

SQL> ho wrap iname=PKB_HR__EMPLOYEE.SQL

PL/SQL Wrapper: Release 11.2.0.2.0- 64bit Production on Sat Jul 02 22:36:59 2011

Copyright (c) 1993, 2009, Oracle.  All rights reserved.

Processing PKB_HR__EMPLOYEE.SQL to PKB_HR__EMPLOYEE.plb

SQL> ho type PKB_HR__EMPLOYEE.plb
CREATE OR REPLACE
PACKAGE body pkg_hr__employee wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
b0 e7
XFaLhgps11nNjgbFBXcfMwOADlUwg2IuNZ7WfC+KrWS0EyMpW3pYkczMty8ur+tmw9h3YQkj
Ps7OSA4dA0dL2triaS7MZloKgLS7mVUZok4LmoeaQm/plm7EnjKl5+XmPS+f0ZTdcDXUrL2Q
ok+VXvQBeTCwAbG3staZaliheXl8LtHrYYewcpJxV/iWWo9X+dBlha3FZlAR31JF6BqXXlZ5
zeN1Tz+TCXyc

/
SQL> @PKB_HR__EMPLOYEE.plb

Package body created.

SQL> Select text from all_source where name='PKG_HR__EMPLOYEE' and owner ='ZAHIR';
PACKAGE  pkg_hr__employee
AS
PROCEDURE check_emp(
    emp_no NUMBER);
END PKG_HR__EMPLOYEE;
PACKAGE body pkg_hr__employee wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
b0 e7
XFaLhgps11nNjgbFBXcfMwOADlUwg2IuNZ7WfC+KrWS0EyMpW3pYkczMty8ur+tmw9h3YQkj
Ps7OSA4dA0dL2triaS7MZloKgLS7mVUZok4LmoeaQm/plm7EnjKl5+XmPS+f0ZTdcDXUrL2Q
ok+VXvQBeTCwAbG3staZaliheXl8LtHrYYewcpJxV/iWWo9X+dBlha3FZlAR31JF6BqXXlZ5
zeN1Tz+TCXyc


6 rows selected.

Wrapping the source code of the trigger will not have any effect . As a work around , you can place the logic in a package and wrap the package body . See below . 

SQL> ho type trg_employee.sql
create or replace trigger emp_upd_trigger
    before update of ename,job,mgr,hiredate,sal,comm,deptno on emp
    for each row
    begin
     DBMS_OUTPUT.PUT_LINE('Validating  Employee' || :new.EMPNO) ;
        /****************
       ...
       ...
       Do some process
      *************/
   end;

SQL> ho wrap iname=trg_employee.sql

PL/SQL Wrapper: Release 11.2.0.2.0- 64bit Production on Sat Jul 02 22:45:31 2011

Copyright (c) 1993, 2009, Oracle.  All rights reserved.

Processing trg_employee.sql to trg_employee.plb

SQL> @trg_employee.plb
 13  /

Trigger created.

SQL> Select text from all_source where name='EMP_UPD_TRIGGER' and owner ='ZAHIR';
trigger emp_upd_trigger
    before update of ename,job,mgr,hiredate,sal,comm,deptno on emp
    for each row
    begin
     DBMS_OUTPUT.PUT_LINE('Validating  Employee' || :new.EMPNO) ;





   end;

11 rows selected.

SQL> ho type trg_employee_with_pkg.sql
create or replace trigger emp_upd_trigger
    before update of ename,job,mgr,hiredate,sal,comm,deptno on emp
    for each row
    begin
    pkg_hr__employee.check_emp(:new.EMPNO);
   end;

SQL> ho wrap iname=trg_employee_with_pkg.sql

PL/SQL Wrapper: Release 11.2.0.2.0- 64bit Production on Sat Jul 02 22:48:34 2011

Copyright (c) 1993, 2009, Oracle.  All rights reserved.

Processing trg_employee_with_pkg.sql to trg_employee_with_pkg.plb

SQL> @trg_employee_with_pkg.plb
  8  /

Trigger created.

Happy  Wrapping.