Oracle wrap 及 unwrap.docx

Oracle wrap 与 unwrap（ 加密与解密） 说明一. Wrap 说明官网的说明如下：APL/SQL Source Text Wrapping/docs/cd/E11882_01/appdev.112/e17126/wrap.htm#LNPLS1744You can wrap the PL/SQL source text for any of thesestored PL/SQL units, therebypreventing anyone from displaying or editing that text: （1）Package specification （2）Package body （3）Type specification （4）Type body （5）Function （6）Procedure A file containing wrapped PL/SQL source text is called awrapped file. Awrapped file can be moved, backed up, or processed by SQL*Plus or the Importand Export utilities. To produce a wrapped file, use either the PL/SQL Wrapper utility or aDBMS_DDLsubprogram.The PL/SQL Wrapper utility wraps the source text of every wrappable PL/SQL unitcreated by a specified SQL file. TheDBMS_DDLsubprograms wrap thesource text of single dynamically generated wrappable PL/SQL units. Both the PL/SQL Wrapper utility andDBMS_DDLsubprograms detecttokenization errors (for example, runaway strings), but not syntax or semanticerrors (for example, nonexistent tables or views). Wrapped files are upward-compatible between Oracle Database releases. For example, youcan load files produced by the V8.1.5 PL/SQL Wrapper utility into a V8.1.6 OracleDatabase.itpub上有篇文章提到了wrap 加密的原理： From:/12932950/viewspace-619808 Oracle加密的原理就是先对源码进行lz压缩lzstr，然后对压缩数据进行SHA-1运算得到40位的加密串shstr，然后将加密串与压缩串拼接得到shstr+lzstr，然后对拼接后的字符串进行Oracle双字符转换（转换表）。最后将转换后的字符串进行base64编码，最终得到wrap的加密串。 The default file extension forinput_fileissql. The default nameofoutput_fileisinput_file.plb. Therefore, these commands are equivalent: wrapiname=/mydir/myfile wrapiname=/mydir/myfile.sql oname=/mydir/myfile.plbThisexample specifies a different file extension forinput_fileand adifferent name foroutput_file: wrapiname=/mydir/myfile.src oname=/yourdir/yourfile.outwrap 的使用步骤如下：（1）将我们要加密的sql 语句保存到一个sql文本里。（2）用wrap 进行处理，指定输入的sql，即我们第一步的问题，然后指定输出的路径与文件名，默认扩展名是plb。（3）执行我们第二部进过wrap 处理的sql，即plb文件，创建我们的对象.示例1：wrap funcation-函数CREATE OR REPLACE FUNCTION F_DAVE ( n int) RETURN stringISBEGIN IF n = 1 THEN RETURN Dave is DBA!; ELSIF n = 2 THEN RETURN Dave come from AnQing!; ELSE RETURN Dave come from HuaiNing!; END IF;END;/SYSdave2(db2) select F_DAVE(4) fromdual;F_DAVE(4)-Dave come from HuaiNing!BTW： 今天群里有人问我的blog的例子里为啥有安庆，因为我是安庆怀宁人。oracledb2 $ pwd/home/oracleoracledb2 $cat dave.sqlCREATE OR REPLACE FUNCTION F_DAVE ( n int) RETURNstringISBEGIN IF n = 1 THEN RETURN Dave is DBA!; ELSIF n = 2 THEN RETURN Dave come from AnQing!; ELSE RETURN Dave come from HuaiNing!; END IF;END;/oracledb2 $wrap iname=dave.sqlPL/SQL Wrapper: Release .0-Production on Thu Aug 18 22:59:14 2011Copyright (c) 1993, 2004, Oracle. All rights reserved.Processing dave.sql to dave.plboracledb2 $ lsbifile.bbddave.plb dave.sql Desktoplog.bbdoracledb2 $cat dave.plbCREATE OR REPLACE FUNCTION F_DAVE wrappeda0000001abcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcd810d e7S9NWrpt8q6tkKEMxCcfYJz2aLF4wgxDQLZ4VfC9AkE6OnV4ydypXGhveHcDg8UXy98WIg6xRcrtc/BRdQJjutbna/9+g0LlaSx87/znV+y926S1AeC0IRi/tjPJTyvJereDdk8mftMo8QMjVfw0xXn0zVagAawwNVhSAiy/JPTMKkrBkC5ruMwQSTe6JQNq7Q2QtJV0hgQou0rYuet4/gJ5BwAj75ph6EA=/SYSdave2(db2) dave.plb-再次调用函数，正常使用：SYSdave2(db2) select F_DAVE(4) fromdual;F_DAVE(4)-Dave come from HuaiNing!-查看函数源码，已经加过密了：SYSdave2(db2) select text fromdba_source where name=F_DAVE;TEXT-FUNCTION F_DAVE wrappeda0000001abcdabcdabcdabcdabcdabcdabcdabcdTEXT-abcdabcdabcdabcdabcdabcdabcd810d e7S9NWrpt8q6tkKEMxCcfYJz2aLF4wgxDQLZ4VfC9AkE6OnV4ydypXGhveHcDg8UXy98WIg6xRcrtc/BRdQJjutbna/9+g0LlaSx87/znV+y926S1AeC0IRi/tjPJTyvJereDdk8mftMo8QMjVTEXT-fw0xXn0zVagAawwNVhSAiy/JPTMKkrBkC5ruMwQSTe6JQNq7Q2QtJV0hgQou0rYuet4/gJ5BwAj75ph6EA=示例2：SYS.DBMS_DDL 函数 thedbms_ddlpackage provideswrapfunctionsandcreate_wrappedprocedures, each of whichwraps the pl/sql source text of a single dynamically generated wrappable pl/sqlunit. TheDBMS_DDLpackagealso provides the exceptionMALFORMED_WRAP_INPUT(ORA-24230),which is raised if the input toWRAPorCREATE_WRAPPEDisnot a valid wrappable PL/SQL unit. (For the list of wrappable PL/SQL units, seethe introduction toPL/SQLSource Text Wrapping.) EachWRAPfunctiontakes as input a singleCREATEstatement that creates a wrappablePL/SQL unit and returns an equivalentCREATEstatement in which thePL/SQL source text is wrapped. For more information about theWRAPfunctions,seeOracleDatabase PL/SQL Packages and Types Reference. EachCREATE_WRAPPEDproceduredoes what its correspondingWRAPfunction does and then runs thereturnedCREATEstatement, creating the specified PL/SQL unit. Formore information about theCREATE_WRAPPEDprocedures。该示例直接参考官方文档：DECLAREpackage_text VARCHAR2(32767); -text for creating package spec and bodyFUNCTION generate_spec (pkgname VARCHAR2) RETURN VARCHAR2 ASBEGINRETURN CREATE PACKAGE | pkgname | AUTHID DEFINER ASPROCEDURE raise_salary (emp_id NUMBER, amount NUMBER);PROCEDURE fire_employee (emp_id NUMBER);END | pkgname | ; ENDgenerate_spec;FUNCTION generate_body (pkgname VARCHAR2) RETURN VARCHAR2 ASBEGIN RETURNCREATE PACKAGE BODY | pkgname | ASPROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) ISBEGINUPDATE employeesSET salary = salary + amount WHERE employee_id = emp_id;END raise_salary;PROCEDURE fire_employee (emp_id NUMBER) ISBEGINDELETE FROM employees WHERE employee_id = emp_id;END fire_employee;END | pkgname | ; ENDgenerate_body;BEGINpackage_text := generate_spec(emp_actions); - Generate package specEXECUTE IMMEDIATE package_text; - Create package specpackage_text := generate_body(emp_actions); - Generate package body SYS.DBMS_DDL.CREATE_WRAPPED(package_text); - Create wrapped package bodyEND;/二. Unwrap 说明 wrap的目的是为了加密，所以Oracle并没有提供unwrap 的方法。 itpub上的一些牛人研究了一下这个问题，写了一些unwrap的代码。 具体讨论的过程，参考itpub的2个帖子： /thread-1154232-1-2.html /viewthread.php?tid=1175718&extra=page%3D1&frombbs=1我这里贴一下unwrap 的代码：/* Formatted on2011/8/18 12:59:54 (QP5 v5.163.1008.3004) */CREATE OR REPLACE PACKAGE amosunwrapperIS FUNCTION deflate (src IN VARCHAR2) RETURN RAW; FUNCTION deflate (src IN VARCHAR2, quality IN NUMBER) RETURN RAW; FUNCTION inflate (src IN RAW) RETURN VARCHAR2;END;/CREATE OR REPLACE PACKAGE BODY amosunwrapperIS FUNCTION deflate (src IN VARCHAR2) RETURN RAW IS BEGIN RETURN deflate (src, 6); END; FUNCTION deflate (src IN VARCHAR2, quality IN NUMBER) RETURN RAW AS LANGUAGE JAVA NAME UNWRAPPER.Deflate( java.lang.String, int ) returnbyte; FUNCTION inflate (src IN RAW) RETURN VARCHAR2 AS LANGUAGE JAVA NAME UNWRAPPER.Inflate( byte ) returnjava.lang.String;END;/* Formatted on2011/8/18 13:00:16 (QP5 v5.163.1008.3004) */CREATE OR REPLACE JAVA SOURCE NAMED UNWRAPPER ASimportjava.io.*;importjava.util.zip.*;public classUNWRAPPERpublic staticString Inflate( bytesrc)try ByteArrayInputStream bis= newByteArrayInputStream(src); InflaterInputStream iis= newInflaterInputStream(bis); StringBuffer sb= newStringBuffer();for( intc=iis.read();c!= -1;c=iis.read() ) sb.append( (char)c);returnsb.toString(); catch (Exception e)return null;public static byteDeflate(String src, intquality)trybytetmp= newbytesrc.length() + 100 ; Deflater defl= newDeflater(quality); defl.setInput(src.getBytes(UTF-8) ); defl.finish();intcnt=defl.deflate(tmp);byteres= newbytecnt;for( inti= 0;icnt;i+ ) res=tmp;returnres; catch (Exception e)return null;/ALTER JAVA SOURCE UNWRAPPER COMPILE/* Formatted on2011/8/18 13:02:57 (QP5 v5.163.1008.3004) */-为了输出中文，要修改java过程CREATE OR REPLACE JAVA SOURCE NAMED UNWRAPPER ASimportjava.io.*;importjava.util.zip.*;public classUNWRAPPERpublic staticString Inflate( bytesrc)try ByteArrayInputStream bis= newByteArrayInputStream(src); InflaterInputStream iis= newInflaterInputStream(bis); StringBuffer sb= newStringBuffer();for( intc=iis.read();c!= -1;c=iis.read() ) sb.append( (char)c);String hello= newString(sb.toString().getBytes(iso8859-1),GBK);returnhello; catch (Exception e)return nu