oracle wrap 和 unwrap（加密与解密）说明 -凯发app官方网站

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:

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 name=/mydir/myfile.plb

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

wrapiname=/mydir/myfile.src name=/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

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

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

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

abcd

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

abcd

text

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

abcd

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 (),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 .)

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 .

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个帖子：

我这里贴一下unwrap 的代码：

/* formatted on2011/8/18 12:59:54 (qp5 v5.163.1008.3004) */

create or replace package amosunwrapper

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

function deflate (src in varchar2)

return raw

begin

return deflate (src, 6);

end;

function deflate (src in varchar2, quality in number)

return raw

language java

name 'unwrapper.deflate( java.lang.string, int ) returnbyte[]';

function inflate (src in raw)

return varchar2

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)

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 wner = 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),

20)

1),

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

into vlzinflatestr

from dba_source

where wner = o and name = n and type = t and line = i;

else

select text

into vlzinflatestr

from dba_source

where wner = 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

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:

email: dvd.dba@gmail.com

dba1 群：62697716(满); dba2 群：62697977(满) dba3 群：62697850(满)

dba 超级群：63306533(满); dba4 群： 83829929(满) dba5群： 142216823(满)

dba6 群：158654907(满) 聊天群：40132017(满) 聊天2群：69087192(满)

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

作者：tianlesoftware 发表于2011-8-18 15:41:38

阅读：363 评论：1

link url:

阅读(16335) | 评论(0) | 转发(0) |

上一篇： oracle rba(redo byte address) 说明

下一篇： oracle 10g dbms_rowid 包源码

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们

oracle wrap 和 unwrap（ 加密与解密） 说明 -凯发app官方网站

一. wrap 说明

示例1：wrap funcation

示例2：sys.dbms_ddl 函数

二. unwrap 说明

oracle wrap 和 unwrap（加密与解密）说明 -凯发app官方网站