Oracle wrap 和 unwrap( 加密与解密) 说明

 

一. Wrap 说明

官网的说明如下:

A PL/SQL Source Text Wrapping

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/wrap.htm#LNPLS1744

 

You can wrap the PL/SQL source text for any of these stored PL/SQL units, thereby preventing 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 a wrapped 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 a DBMS_DDL subprogram.The PL/SQL Wrapper utility wraps the source text of every wrappable PL/SQL unitcreated by a specified SQL file. The DBMS_DDL subprograms wrap thesource text of single dynamically generated wrappable PL/SQL units.

            Both the PL/SQL Wrapper utility and DBMS_DDL subprograms 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:http://space.itpub.net/12932950/viewspace-619808

 

            Oracle加密的原理就是先对源码进行lz压缩lzstr,然后对压缩数据进行SHA-1运算得到40位的加密串shstr,然后将加密串与压缩串拼接得到shstr+lzstr,然后对拼接后的字符串进行Oracle双字符转换(转换表)。最后将转换后的字符串进行base64编码,最终得到wrap的加密串。

 

            The default file extension for input_file is sql. The default nameof output_file is input_file.plb. Therefore, these commands are equivalent:

            wrapiname=/mydir/myfile

            wrapiname=/mydir/myfile.sql oname=/mydir/myfile.plb

            Thisexample specifies a different file extension for input_file and adifferent name for output_file:

            wrapiname=/mydir/myfile.src oname=/yourdir/yourfile.out

 

wrap 的使用步骤如下:

(1)将我们要加密的sql 语句保存到一个sql文本里。

(2)用wrap 进行处理,指定输入的sql,即我们第一步的问题,然后指定输出的路径和文件名,默认扩展名是plb。

(3)执行我们第二部进过wrap 处理的sql,即plb文件,创建我们的对象.

 

 

示例1:wrap funcation

--函数

CREATE OR REPLACE FUNCTION F_DAVE (

  n int

) RETURN string

IS

BEGIN

  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;

/

 

SYS@dave2(db2)> select F_DAVE(4) fromdual;

F_DAVE(4)

--------------------------------------------------------------------------------

Dave come from HuaiNing!

 

BTW: 今天群里有人问我的blog的例子里为啥有安庆,因为我是安庆怀宁人。

 

[oracle@db2 ~]$ pwd

/home/oracle

[oracle@db2 ~]$ cat dave.sql

CREATE OR REPLACE FUNCTION F_DAVE (

  n int

) RETURNstring

IS

BEGIN

  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;

/

 

[oracle@db2 ~]$ wrap iname=dave.sql

 

PL/SQL Wrapper: Release 10.2.0.1.0-Production on Thu Aug 18 22:59:14 2011

 

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

 

Processing dave.sql to dave.plb

[oracle@db2 ~]$ ls

bifile.bbd dave.plb  dave.sql  Desktop log.bbd

 

[oracle@db2 ~]$ cat dave.plb

CREATE OR REPLACE FUNCTION F_DAVE wrapped

a000000

1

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

8

10d e7

S9NWrpt8q6tkKEMxCcfYJz2aLF4wgxDQLZ4VfC9AkE6OnV4ydypXGhveHcDg8UXy98WIg6xR

crtc/BRdQJjutbna/9+g0LlaSx87/znV+y926S1AeC0IRi/tjPJTyvJereDdk8mftMo8QMjV

fw0xXn0zVagAawwNVhSAiy/JPTMKkrBkC5ruMwQSTe6JQNq7Q2QtJV0hgQou0rYuet4/gJ5B

wAj75ph6EA==

 

/

 

SYS@dave2(db2)> @dave.plb

 

--再次调用函数,正常使用:

SYS@dave2(db2)> select F_DAVE(4) fromdual;

 

F_DAVE(4)

--------------------------------------------------------------------------------

Dave come from HuaiNing!

 

--查看函数源码,已经加过密了:

SYS@dave2(db2)> select text fromdba_source where name='F_DAVE';

 

TEXT

--------------------------------------------------------------------------------

FUNCTION F_DAVE wrapped

a000000

1

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

 

TEXT

--------------------------------------------------------------------------------

abcd

abcd

abcd

abcd

abcd

abcd

abcd

8

10d e7

S9NWrpt8q6tkKEMxCcfYJz2aLF4wgxDQLZ4VfC9AkE6OnV4ydypXGhveHcDg8UXy98WIg6xR

crtc/BRdQJjutbna/9+g0LlaSx87/znV+y926S1AeC0IRi/tjPJTyvJereDdk8mftMo8QMjV

 

TEXT

--------------------------------------------------------------------------------

fw0xXn0zVagAawwNVhSAiy/JPTMKkrBkC5ruMwQSTe6JQNq7Q2QtJV0hgQou0rYuet4/gJ5B

wAj75ph6EA==

 

 

示例2:SYS.DBMS_DDL 函数

            the dbms_ddl package provides wrap functionsand create_wrapped procedures, each of whichwraps the pl/sql source text of a single dynamically generated wrappable pl/sqlunit.

            The DBMS_DDL packagealso provides the exception MALFORMED_WRAP_INPUT (ORA-24230),which is raised if the input to WRAP or CREATE_WRAPPED isnot a valid wrappable PL/SQL unit. (For the list of wrappable PL/SQL units, seethe introduction to "PL/SQLSource Text Wrapping".)

 

            Each WRAP functiontakes as input a single CREATE statement that creates a wrappablePL/SQL unit and returns an equivalent CREATE statement in which thePL/SQL source text is wrapped. For more information about the WRAP functions,see OracleDatabase PL/SQL Packages and Types Reference.

 

            Each CREATE_WRAPPED proceduredoes what its corresponding WRAP function does and then runs thereturned CREATE statement, creating the specified PL/SQL unit. Formore information about the CREATE_WRAPPED procedures。

 

该示例直接参考官方文档:

 

DECLARE

 package_text  VARCHAR2(32767); --text for creating package spec and body

 

 FUNCTION generate_spec (pkgname VARCHAR2) RETURN VARCHAR2 AS

 BEGIN

   RETURN 'CREATE PACKAGE ' || pkgname || ' AUTHID DEFINER AS

     PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER);

     PROCEDURE fire_employee (emp_id NUMBER);

     END ' || pkgname || ';';

  ENDgenerate_spec;

 

 FUNCTION generate_body (pkgname VARCHAR2) RETURN VARCHAR2 AS

 BEGIN

    RETURN'CREATE PACKAGE BODY ' || pkgname || ' AS

     PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) IS

     BEGIN

       UPDATE employees

         SET salary = salary + amount WHERE employee_id = emp_id;

     END raise_salary;

     PROCEDURE fire_employee (emp_id NUMBER) IS

     BEGIN

       DELETE FROM employees WHERE employee_id = emp_id;

     END fire_employee;

   END ' || pkgname || ';';

  ENDgenerate_body;

 

BEGIN

 package_text := generate_spec('emp_actions');  -- Generate package spec

 EXECUTE IMMEDIATE package_text;                -- Create package spec

 package_text := generate_body('emp_actions');  -- Generate package body

  SYS.DBMS_DDL.CREATE_WRAPPED(package_text);     -- Create wrapped package body

END;

/

 

二. Unwrap 说明

            wrap的目的是为了加密,所以Oracle并没有提供unwrap 的方法。 itpub上的一些牛人研究了一下这个问题,写了一些unwrap的代码。 具体讨论的过程,参考itpub的2个帖子:

          http://www.itpub.net/thread-1154232-1-2.html

          http://www.itpub.net/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 amosunwrapper

IS

   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 amosunwrapper

IS

   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

   AS import java.io.*;

import java.util.zip.*;

public class UNWRAPPER

{

  public static String Inflate( byte[] src )

  {

    try

    {

      ByteArrayInputStream bis = new ByteArrayInputStream( src );

      InflaterInputStream iis = newInflaterInputStream( bis );

      StringBuffer sb = newStringBuffer();

      for( int c = iis.read(); c != -1; c = iis.read() )

      {

        sb.append( (char) c );

      }

      return sb.toString();

    } catch ( Exception e )

    {

    }

    return null;

  }

  public static byte[] Deflate( String src, intquality )

  {

    try

    {

      byte[] tmp = newbyte[ src.length() + 100 ];

      Deflater defl = new Deflater( quality );

      defl.setInput( src.getBytes( "UTF-8" ) );

      defl.finish();

      int cnt = defl.deflate( tmp );

      byte[] res = newbyte[ cnt ];

      for( int i = 0; i < cnt; i++ )

        res = tmp;

      return res;

    } 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

   AS import java.io.*;

import java.util.zip.*;

 

public class UNWRAPPER

{

  public static String Inflate( byte[] src )

  {

    try

    {

      ByteArrayInputStream bis = newByteArrayInputStream( src );

      InflaterInputStream iis = newInflaterInputStream( bis );

      StringBuffer sb = newStringBuffer();

      for( int c = iis.read(); c != -1; c = iis.read() )

      {

        sb.append( (char) c );

      }

String hello = new String(sb.toString().getBytes("iso8859-1"), "GBK");

      return hello;

    } catch ( Exception e )

    {

    }

    return null;

  }

  public static byte[] Deflate( String src, intquality )

  {

    try

    {

      byte[] tmp = newbyte[ src.length() + 100 ];

      Deflater defl = new Deflater( quality );

      defl.setInput( src.getBytes( "UTF-8" ) );

      defl.finish();

      int cnt = defl.deflate( tmp );

      byte[] res = newbyte[ cnt ];

      for( int i = 0; i < cnt; i++ )

        res = tmp;

      return res;

    } catch ( Exception e )

    {

    }

    return null;

  }

}

/

 

ALTER JAVA SOURCE UNWRAPPER COMPILE

/

 

 

/* Formatted on2011/8/18 13:00:41 (QP5 v5.163.1008.3004) */

CREATE OR REPLACE PROCEDURE unwrap (o IN VARCHAR, n IN VARCHAR, t IN VARCHAR)

AS

   vWrappedtext    VARCHAR2 (32767);

   vtrimtext       VARCHAR2 (32767);

   vChar           VARCHAR2 (2);

   vRepchar        VARCHAR2 (2);

   vLZinflatestr   VARCHAR2 (32767);

   nLen            INTEGER;

   nLoop           INTEGER;

   nCnt            INTEGER;

   code            VARCHAR (512);

BEGIN

   code :=

      '3D6585B318DBE287F152AB634BB5A05F7D687B9B24C228678ADEA4261E03EB176F343E7A3FD2A96A0FE935561FB14D1078D975F6BC4104816106F9ADD6D5297E869E79E505BA84CC6E278EB05DA8F39FD0A271B858DD2C38994C480755E4538C46B62DA5AF322240DC50C3A1258B9C16605CCFFD0C981CD4376D3C3A30E86C3147F533DA43C8E35E1994ECE6A39514E09D64FA5915C52FCABB0BDFF297BF0A76B449445A1DF0009621807F1A82394FC1A7D70DD1D8FF139370EE5BEFBE09B97772E7B254B72AC7739066200E51EDF87C8F2EF412C62B83CDACCB3BC44EC069366202AE88FCAA4208A64557D39ABDE1238D924A1189746B91FBFEC901EA1BF7CE'; --sys.idltranslate表内容存到字符数组

 

   vtrimtext := '';

 

   SELECT COUNT (*)

     INTO ncnt

     FROM DBA_SOURCE

    WHERE owner = o AND Name = n AND TYPE = t;

 

   IF ncnt > 0 AND ncnt <= 5

   THEN

      FOR i IN 1 .. ncnt

      LOOP

         IF i = 1

         THEN

            SELECT RTRIM (SUBSTR (TEXT,

                                  INSTR (TEXT,

                                         CHR (10),

                                         1,

                                         20)

                                  + 1),

                          CHR (10))                      --保存去掉前边20行的BASE64码正文

              INTO vLZinflatestr

              FROM DBA_SOURCE

             WHERE owner = o AND Name = n AND TYPE = t AND line = i;

         ELSE

            SELECT text

              INTO vLZinflatestr

              FROM DBA_SOURCE

             WHERE owner = o AND Name = n AND TYPE = t AND line = i;

         END IF;

 

         vtrimtext := vtrimtext || vLZinflatestr;

      END LOOP;

   END IF;

 

   vtrimtext := REPLACE (vtrimtext, CHR (10), '');

   nLen := LENGTH (vtrimtext) / 256;

   vWrappedtext := '';

 

   FOR i IN 0 .. nLen

   LOOP

      --ifi< nLen   then

      vWrappedtext :=

         vWrappedtext

         || UTL_ENCODE.base64_decode (

               UTL_RAW.cast_to_raw (SUBSTRB (vtrimtext, 256 * i + 1, 256)));

   --else

   --vWrappedtext:=vWrappedtext||utl_encode.base64_decode(utl_raw.cast_to_raw(substrb(vtrimtext,64*i+1 ))) ;

   --endif;

   --DBMS_OUTPUT.PUT_LINE(vWrappedtext);

   END LOOP;

 

   --vWrappedtext:=substr(vWrappedtext,41);

   nLen := LENGTH (vWrappedtext) / 2 - 1;

 

   vLZinflatestr := '';

 

   FOR nLoop IN 20 .. nLen

   LOOP                                                            --从第41字节开始

      vChar := SUBSTRB (vWrappedtext, nLoop * 2 + 1, 2);

      vLZinflatestr :=

         vLZinflatestr || SUBSTR (code, TO_NUMBER (vChar, 'XX') * 2 + 1, 2); --从字符串变量匹配

   --DBMS_OUTPUT.PUT_LINE(vLZinflatestr);

   END LOOP;

 

   --DBMS_OUTPUT.PUT_LINE(vLZinflatestr);

   DBMS_OUTPUT.PUT_LINE (amosunwrapper.inflate (vLZinflatestr));

END;

/

 

阿里巴巴的张端弄了一个界面的Unwrap软件,下载地址:

            破解(Unwrap) 10, 11G PLSQL

            http://www.hellodba.com/reader.php?ID=36&lang=cn

 

 

unwrap 我们第一节创建的F_DAVE函数:

           

            

       不过Oracle 对一些对象进行加密的同时,也提供了代码,比如DBMS_ROWID包。 对于该包使用unwrap 和 直接从dba_source 查询的结果是一致的,而且dba_source 还提供了注释。

 

SQL>exec unwrap('SYS','DBMS_ROWID','PACKAGE BODY');

SQL>select * from dba_source where name='DBMS_ROWID';

 

 

 

 

 

-------------------------------------------------------------------------------------------------------

Blog: http://blog.csdn.net/tianlesoftware

Weibo: http://weibo.com/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群:62697716(满);   DBA2 群:62697977(满)  DBA3 群:62697850(满)  

DBA 超级群:63306533(满);  DBA4 群: 83829929(满) DBA5群: 142216823(满) 

DBA6 群:158654907(满)  聊天 群:40132017(满)   聊天2群:69087192(满)

--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请

posted @ 2011-08-18 15:41  hibernate我最强  阅读(828)  评论(0编辑  收藏  举报