Let’s consider a scenario in which we have written a PL/SQL stored procedure, and we want to prevent anyone, including the DBA, from seeing and/or modifying the source code. Is that possible? Definitely yes: Oracle provides the WRAP utility to accomplish this, but it implied calling it from the Operating System; from 10g onwards it is possible to call it from inside the database.
Let’s see how to make use of both modalities, starting with a review of the code we are interested in hiding:
[oracle@caliope ~]$ cat salary.sql
CREATE OR REPLACE
FUNCTION salary (
empno emp.empno%TYPE )
RETURN NUMBER
IS
sal emp.sal%TYPE;
BEGIN
SELECT sal INTO salary.sal
FROM emp
WHERE empno = salary.empno;
RETURN ( salary.sal );
END;The first alternative is to use wrap from the Operating System.
1. It must be called by referencing the file containing the source code.
[oracle@caliope ~]$ wrap iname=salary.sql
PL/SQL Wrapper: Release 10.2.0.4.0- Production on Sat Nov 01 14:21:47 2008
Copyright (c) 1993, 2004, Oracle. All rights reserved.
Processing salary.sql to salary.plb2. We check the result.
[oracle@caliope ~]$ cat salary.plb
CREATE OR REPLACE
FUNCTION salary wrapped
a000000
367
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
cc d6
+/IgG5qcUWhn5W4aX1Z1gJbmalUwgy5KLcvWfHSi2sHVSCY2DKrK4KYn8hQGyrdN4JG7orD5
ELaEY9EXT7sxm8hT0NF8GQX2BSRNdGc/i2nS6FUq4oJr3+Z/vda3u1pli6dW3SXcl4vlGtfr
ShxK0Ikb/IzmSgsWLtX2Nyo5Ob28iiwzrfFQOmdO1j2jRP8s5Z3aTTuovLrHOuGA3Hyl
/3. We load the code into the database.
[oracle@caliope ~]$ sqlplus scott/tiger @salary.plb
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Nov 1 14:23:59 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Function created.4. We check that the function is working.
SCOTT@orcl> select salary( 7654 ) from dual;
SALARY(7654)
------------
1312.5
SCOTT@orcl>As a result, we now have a stored procedure whose source code is not intelligible to the naked eye.
What to do if the code is already in the database? Well, we can export it to a file and follow the previous procedure, but it is also possible to make use of the improved DBMS_DDL package; so let’s get started.
1. Let’s use the following script to transform the source code.
[oracle@caliope ~]$ cat wrapped.sql
DECLARE
v_source DBMS_SQL.VARCHAR2A;
BEGIN
v_source(1) := 'CREATE OR REPLACE ';
FOR s IN ( SELECT line, text
FROM user_source
WHERE name = 'SALARY'
ORDER BY line )
LOOP
v_source(s.line+1) := s.text;
END LOOP;
dbms_ddl.create_wrapped(
ddl => v_source,
lb => 1,
ub => v_source.count );
END;
/2. Now we run it.
SCOTT@orcl> @wrapped
PL/SQL procedure successfully completed.3. We verify that the source code was wrapped.
SCOTT@orcl> select text from user_source
2 where name = 'SALARY' order by line;
TEXT
--------------------------------------------------------------------------------
FUNCTION salary wrapped
a000000
369
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
c3 d2
NL5qz7Eyn6/UUHrpUacIYXrnSkQwgy7wLcsVfHSiWHOUxMoNmSQvf6CVAlrwXr70aca5WXwI
/nAKWlYzkLkxeCuI8kessTO0QSAhNtwDkRNJ6sbC/1+niQxcN3d3A9MG5qanSMCbmJCZRkFk
zHqG9AcGWonxLvVFqyc+fCfcn4q5PwtD7ZLkhFU52f6bi7u9g2dWnLjOpqUrlos=4. Finally, let’s see if it is working.
SCOTT@orcl> select salary( 7654 ) from dual;
SALARY(7654)
------------
1312.5Once again, the goal has been achieved!
In summary, we have seen two ways to transform our source code into an indecipherable text using WRAP.
The code shown is neither bug-free nor optimized, but it can serve as a basis. Oh, and don’t forget to take a look at the documentation, to get a complete picture of the available functionality.
