select trigger 查询触发器

select trigger
查询触发器


提问1:有基于SELECT的触发器吗?
回答:没有

提问2:如何使一个查询触发更新操作
回答:如下

两个表:

conn a/a
drop table t1;
drop table t2;
create table t1 (a number, b number);
create table t2 (a number, b number);
truncate table t1;
truncate table t2;
insert into t1 values(1,1);
insert into t1 values(2,2);
commit;

表t2等于

insert into t2
select a, sum(b)-avg(b) from t1 group by a;
commit;
select * from t1;
select * from t2;

SQL> select * from t1;

         A          B
---------- ----------
         1          1
         2          2

SQL> select * from t2;

         A          B
---------- ----------
         1          0
         2          0

SQL>

t1随时更新, 当查询t2时,t2的字段b根据t1更新为最新的值


方法1:
建立视图, 使用自治事务(autonomous transaction)函数更新表和返回值. 程序不直接查询表, 而改查询这个视图
使用自治事务是因为在一个查询里不能有DML操作(insert,update,delete)

create or replace function f_t2(p_a in t1.a%type)
  return t1.b%type
is
  pragma autonomous_transaction;
  v_b t1.b%type;
begin
  select sum(b)-avg(b) into v_b from t1 where a = p_a;
  update t2 set b = v_b where a = p_a;
  commit;
  return v_b;
end;
/
create or replace view v2 as select a, f_t2(a) as b from t2;

测试

select * from v2 where a=1;
select * from t2;

SQL> select * from v2 where a=1;

         A          B
---------- ----------
         1          0

SQL> select * from t2;

         A          B
---------- ----------
         1          0
         2          0

SQL>

t1插入几条数据, 查询v2, 查看结果

insert into t1 values (1,2);
insert into t1 values (2,2);
commit;
select * from t1;
select * from t2;
select * from v2 where a=1;
select * from t2;
select * from v2 where a=2;
select * from t2;

SQL> insert into t1 values (1,2);

1 row created.

SQL> insert into t1 values (2,2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

         A          B
---------- ----------
         1          1
         2          2
         1          2
         2          2

SQL> select * from t2;

         A          B
---------- ----------
         1          0
         2          0

SQL> select * from v2 where a=1;

         A          B
---------- ----------
         1        1.5

SQL> select * from t2;

         A          B
---------- ----------
         1        1.5
         2          0

SQL> select * from v2 where a=2;

         A          B
---------- ----------
         2          2

SQL> select * from t2;

         A          B
---------- ----------
         1        1.5
         2          2

SQL>

查询视图v2触发函数运行, 导致t2被更新
查询了哪条记录就更新哪条, 一次只更新一条

清除测试数据

conn a/a
drop function f_t2;
drop view v2;
drop table t2;
drop table t1;

方法2:
使用细粒度审计(Fine-Grained Auditing)调用事件处理模块(handler module)更新表
处理函数也是作为自治事务运行的

set pages 9999 line 140
set serveroutput on size unlimited
conn a/a
drop table t1;
drop table t2;
create table t1 (a number, b number);
create table t2 (a number, b number);
truncate table t1;
truncate table t2;
insert into t1 values(1,1);
insert into t1 values(2,2);
commit;

建立处理函数

conn / as sysdba
create or replace procedure p_update_t2 (
  p_object_schema VARCHAR2,
  p_object_name   VARCHAR2,
  p_policy_name   VARCHAR2
) is
begin
  merge into a.t2
    using (select a, sum(b)-avg(b) b from a.t1 group by a) t3
    on (t2.a = t3.a)
  when matched then
    update set t2.b=t3.b
  when not matched then
    insert values (t3.a, t3.b);
end;
/

添加审计策略

conn / as sysdba
begin
  dbms_fga.drop_policy (
    object_schema    => 'A'
    ,object_name     => 'T2'
    ,policy_name     => 'UPDATE_T2'
  );
end;
/
begin
  dbms_fga.add_policy (
    object_schema    => 'A'
    ,object_name     => 'T2'
    ,policy_name     => 'UPDATE_T2'
    ,audit_column    => 'B'
    ,handler_schema  => 'SYS'
    ,handler_module  => 'P_UPDATE_T2'
  );
end;
/
truncate table fga_log$;
select * from dba_audit_policies;

SQL> select * from dba_audit_policies;

OBJECT_SCHEMA                  OBJECT_NAME                    POLICY_NAME
------------------------------ ------------------------------ ------------------------------
POLICY_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
POLICY_COLUMN                  PF_SCHEMA                      PF_PACKAGE                     PF_FUNCTION                    ENA SEL INS UPD
------------------------------ ------------------------------ ------------------------------ ------------------------------ --- --- --- ---
DEL AUDIT_TRAIL  POLICY_COLU
--- ------------ -----------
A                              T2                             UPDATE_T2

B                              SYS                                                           P_UPDATE_T2                    YES YES NO  NO
NO  DB+EXTENDED  ANY_COLUMNS


SQL>

handler_schema指定的用户好像必须要和审计策略的拥有者一致, 否则查询时报错:

SQL>  select * from t2 where a=1;
 select * from t2 where a=1
  *
ERROR at line 1:
ORA-06550: line 1, column 9:
PLS-00302: component 'P_UPDATE_T2' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL>

查询表t2

conn a/a
select * from t1;
select * from t2 where a=1;

SQL> select * from t1;

         A          B
---------- ----------
         1          1
         2          2

SQL> select * from t2 where a=1;

no rows selected

SQL>

这时t2啥也没查出来
其实已经更新了, 也记录了审计日志

conn / as sysdba
select * from a.t2;
col db_user for a10
col os_user for a10
col object_schema for a10
col object_name for a10
col sql_text for a50
select to_char(timestamp,'yyyymmddhh24miss'), db_user, os_user, object_schema, object_name, sql_text from dba_fga_audit_trail;

SQL> select * from a.t2;

         A          B
---------- ----------
         1          0
         2          0

SQL>...
SQL> select to_char(timestamp,'yyyymmddhh24miss'), db_user, os_user, object_schema, object_name, sql_text from dba_fga_audit_trail;

TO_CHAR(TIMEST DB_USER    OS_USER    OBJECT_SCH OBJECT_NAM SQL_TEXT
-------------- ---------- ---------- ---------- ---------- --------------------------------------------------
20090507131441 A          oracle     A          T2         select * from t2 where a=1

SQL>

(使用sys用户查询了表a.t2, 却没有触发审计, 好像可能是因为初始化参数AUDIT_SYS_OPERATIONS设置为false.)
查询到的不是最新的,是不及时的

conn a/a
insert into t1 values (1,2);
insert into t1 values (2,2);
commit;
select * from t1;
select * from t2;
conn / as sysdba
select * from a.t2;
select to_char(timestamp,'yyyymmddhh24miss'), db_user, os_user, object_schema, object_name, sql_text from dba_fga_audit_trail;

SQL> select * from t1;

         A          B
---------- ----------
         1          1
         2          2
         1          2
         2          2

SQL> select * from t2 where a=1;

         A          B
---------- ----------
         1          0
         2          0

SQL>
...
SQL> select * from a.t2;

         A          B
---------- ----------
         1        1.5
         2          2

SQL> select to_char(timestamp,'yyyymmddhh24miss'), db_user, os_user, object_schema, object_name, sql_text from dba_fga_audit_trail;

TO_CHAR(TIMEST DB_USER    OS_USER    OBJECT_SCH OBJECT_NAM SQL_TEXT
-------------- ---------- ---------- ---------- ---------- --------------------------------------------------
20090507131441 A          oracle     A          T2         select * from t2 where a=1
20090507131602 A          oracle     A          T2         select * from t2

SQL>

有2条记录满足审计条件, 只触发了一次
这也会产生不少审计日志, 需要定时清除

清除测试数据

conn / as sysdba
begin
  dbms_fga.drop_policy (
    object_schema    => 'A'
    ,object_name     => 'T2'
    ,policy_name     => 'UPDATE_T2'
  );
end;
/
drop procedure p_update_t2;
drop table a.t2;
drop table a.t1;
truncate table fga_log$;

外部链接:
Fine-Grained Auditing
DBMS_FGA

9i/9.2: Fine Grained Auditing
10g: Fine Grained Auditing

How to cleanup the log table FGA_LOG$ ?
用truncate或delete均可

How To Exclude Users Being Audited Through the DBMS_FGA Package
如何不审计某些用户: 在审计条件中使用SYS_CONTEXT判断当前用户, 比如audit_condition => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') <> ''TST'' '

How to Avoid Common Flaws and Errors Using Fine Grained Auditing
9i下,如果表没有被分析或没有使用CBO, 会得到意外的审计结果(比预期的要多)





-fin-

unwrapping plsql code in 10g 解包10g存储过程

unwrapping plsql code in 10g
10g下解包存储过程

原理见Unwrapping 10G wrapped PL/SQL by Anton Scheffer


比如,'create package a'打包后变为:

create package a wrapped --打包后加上了标记"wrapped"
a000000 --固定值, 总是a000000
369 --数据库版本相关. 系统自带的.plb文件中好像都是1
abcd --填充符,共15行
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
9 --7:procedure 8:function 9:package b:package body d:type e:type body 16:library
a 34 --第1个数:打包前原文'\0'的长度(不包括"create "或"create or replace ") 第2个数:打包后的长度
g0a2d/TfdrHiIe3evJs3xeAzTzYwg5m49TOf9b9cWpGmpsvE4Ug= --BASE64编码

WRAP打包过程首先是ZLIB压缩, 然后简单的替换加密, 最后BASE64编码
第2步替换加密中的替换表是ORACLE内部制定的

解包过程则相反

with
src as
(select 'package a' txt from dual)
, wrap as
(select src.txt
, dbms_ddl.wrap('create '||src.txt) wrap
from src)
, base64_dcd as
(select substr(
utl_encode.base64_decode(
utl_raw.cast_to_raw(
rtrim(
substr(
wrap.wrap
,instr(wrap.wrap,chr(10),1,20)+1) --跳过前20行
,chr(10)) --去掉行尾回车, 也可以不去, UTL_ENCODE.BASE64_DECODE能处理回车
)
) --BASE64解码
, 41) x --前40个字符是SHA-1的散列值
from wrap)
, subst as 
(select utl_raw.translate( --码表转换 (这里的替换表是假的)
utl_raw.cast_to_raw(utl_raw.cast_to_varchar2(x)),
utl_raw.cast_to_raw(utl_raw.cast_to_varchar2(
'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F'||
'202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F'||
'404142434445464748494A4B4C4D4E4F505152535455565758595A5B5C5D5E5F'||
'606162636465666768696A6B6C6D6E6F707172737475767778797A7B7C7D7E7F'||
'808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9F'||
'A0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF'||
'C0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF'||
'E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEFF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF'))
,utl_raw.cast_to_raw(utl_raw.cast_to_varchar2(
'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F'||
'202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F'||
'404142434445464748494A4B4C4D4E4F505152535455565758595A5B5C5D5E5F'||
'606162636465666768696A6B6C6D6E6F707172737475767778797A7B7C7D7E7F'||
'808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9F'||
'A0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF'||
'C0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF'||
'E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEFF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF'))
) s
from base64_dcd)
select mycompress.inflate(s) --zlib解压
from subst;

MYCOMPRESS.INFLATE(S)--ZLIB解压
----------------------------------------------------------------------------------------------------------------------------------
PACKAGE a

解一个系统的, rdbms/admin/dbmsrmge.plb中的一部分

with
wrap as
(select
'xQaGj1mCrsgW3+RaZh0GMWKlijswgw33f64df3QCkxMP7i1akFTarfShhBKhXRRbWfcvTV49'||chr(10)||
'/KYmJJ2/sYRpdytaSTQi+BByn2cNooSlksJCltfJVYkyA3NojcRzSAXPpougS6vDyRuc8krb'||chr(10)||
'qcznL7hPkPI82mlZdU1QXnUjOlCfulvR6vEwG3BlN+InBrSBYQnw2G+Fv4J/EUocbXFCDTZP'||chr(10)||
'tkZcVjxsZdvE4XUJi7tYvlfp/dTfPZObsxIW/Q3b3HPu+ZW7AHr+pZox4tsc96sDYZwJte8r'||chr(10)||
'AyjqTZxrjCyOTwtwusZBP0HrzZ+bwsy07tuqj0rg8esOAZSZ6kBtOE/4HRLcThMdCFDr3g=='||chr(10)||
''||chr(10)
wrap
from dual)
, base64_dcd as
(select substr(
utl_encode.base64_decode(
utl_raw.cast_to_raw(
wrap.wrap
)
) --BASE64解码
, 41) x --前40个字符是SHA-1的散列值
from wrap)
, subst as 
(select utl_raw.translate( --码表转换 (这里的替换表是假的)
utl_raw.cast_to_raw(utl_raw.cast_to_varchar2(x)),
utl_raw.cast_to_raw(utl_raw.cast_to_varchar2(
'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F'||
'202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F'||
'404142434445464748494A4B4C4D4E4F505152535455565758595A5B5C5D5E5F'||
'606162636465666768696A6B6C6D6E6F707172737475767778797A7B7C7D7E7F'||
'808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9F'||
'A0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF'||
'C0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF'||
'E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEFF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF'))
,utl_raw.cast_to_raw(utl_raw.cast_to_varchar2(
'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F'||
'202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F'||
'404142434445464748494A4B4C4D4E4F505152535455565758595A5B5C5D5E5F'||
'606162636465666768696A6B6C6D6E6F707172737475767778797A7B7C7D7E7F'||
'808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9F'||
'A0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF'||
'C0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF'||
'E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEFF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF'))
) s
from base64_dcd)
select mycompress.inflate(s) --zlib解压
from subst;

PACKAGE dbms_rmgr_group_export AUTHID CURRENT_USER AS


FUNCTION CREATE_EXP(OBJID IN NUMBER,
VERSION IN VARCHAR2,
NEW_BLOCK OUT PLS_INTEGER)
RETURN VARCHAR2;



FUNCTION GRANT_EXP(OBJID IN NUMBER,
ISDBA IN PLS_INTEGER,
GRANTOR OUT VARCHAR2,
VERSION IN VARCHAR2,
NEW_BLOCK OUT PLS_INTEGER)
RETURN VARCHAR2;



FUNCTION AUDIT_EXP(OBJID IN NUMBER,
VERSION IN VARCHAR2,
NEW_BLOCK OUT PLS_INTEGER)
RETURN VARCHAR2;



FUNCTION DROP_EXP(OBJID IN NUMBER,
VERSION IN VARCHAR2,
NEW_BLOCK OUT PLS_INTEGER)
RETURN VARCHAR2;



FUNCTION GRANT_SYSPRIVS_EXP(VERSION IN VARCHAR2,
NEW_BLOCK OUT PLS_INTEGER)
RETURN VARCHAR2;



FUNCTION  AUDIT_SYSPRIVS_EXP(VERSION IN VARCHAR2,
NEW_BLOCK OUT PLS_INTEGER)
RETURN VARCHAR2;

END DBMS_RMGR_GROUP_EXPORT;

如果语句太大了就会报错

utl_raw.cast_to_raw(
*
ERROR at line 67:
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at "SYS.UTL_RAW", line 224

编写存储过程解包

create or replace java source named MY_COMPRESS
as
import java.io.*;
import java.util.zip.*;
public class MY_COMPRESS
{
public static String Inflate( byte[] src )
{
try
{
ByteArrayInputStream bis = new ByteArrayInputStream( src );
InflaterInputStream iis = new InflaterInputStream( bis );
StringBuffer sb = new StringBuffer();
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, int quality )
{
try
{
byte[] tmp = new byte[ src.length() + 100 ];
Deflater defl = new Deflater( quality );
defl.setInput( src.getBytes( "UTF-8" ) );
defl.finish();
int cnt = defl.deflate( tmp );
byte[] res = new byte[ cnt ];
for( int i = 0; i < cnt; i++ )
res[i] = tmp[i];
return res;
} catch ( Exception e )
{
}
return null;
}
}
/

alter java source MY_COMPRESS compile
/

create or replace package mycompress
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 mycompress
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 'MY_COMPRESS.Deflate(java.lang.String, int) return byte[]';

function inflate(src in raw) return varchar2
as language java
name 'MY_COMPRESS.Inflate(byte[]) return java.lang.String';

end;
/

create or replace package mypkg
is
function unwrap(s in varchar2) return varchar2;
function unwrap_obj(p_name in varchar2, p_type in varchar2, p_owner in varchar2 default user) return varchar2;
procedure unwrap_obj_a(p_name in varchar2, p_type in varchar2, p_owner in varchar2 default user);
end;
/

create or replace package body mypkg
is
S_SUB_FROM constant varchar2(1000) := --假的
'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F'||
'202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F'||
'404142434445464748494A4B4C4D4E4F505152535455565758595A5B5C5D5E5F'||
'606162636465666768696A6B6C6D6E6F707172737475767778797A7B7C7D7E7F'||
'808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9F'||
'A0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF'||
'C0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF'||
'E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEFF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF';
S_SUB_TO constant varchar2(1000) :=
'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F'||
'202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F'||
'404142434445464748494A4B4C4D4E4F505152535455565758595A5B5C5D5E5F'||
'606162636465666768696A6B6C6D6E6F707172737475767778797A7B7C7D7E7F'||
'808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9F'||
'A0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF'||
'C0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF'||
'E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEFF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF';
S_SUB_FROM_RAW constant raw(1000) := utl_raw.cast_to_raw(utl_raw.cast_to_varchar2(s_sub_from));
S_SUB_TO_RAW   constant raw(1000) := utl_raw.cast_to_raw(utl_raw.cast_to_varchar2(s_sub_to));
S_SHA1_DIGEST_LENGTH constant pls_integer := 41;

function unitesql(s in dbms_sql.varchar2a, nl in varchar2 default chr(10)) return varchar2
is
lb pls_integer := 1;
ub pls_integer := s.count;
t varchar2(32767);
begin
for i in lb..ub loop
t := t || s(i);
--dbms_output.put_line('['||t||']');
if i != ub then
t := t || nl;
end if;
end loop;
return t;
end;
function dividesql(s in varchar2) return dbms_sql.varchar2a
is
nl varchar2(100) := chr(10);
len pls_integer := length(s);
t   dbms_sql.varchar2a;
i   pls_integer := 1;
p   pls_integer := 1;
np  pls_integer := 0;
begin
loop
exit when p > len;
np := instr(s,nl,p);
--dbms_output.put_line('p='||p||':'||'np='||np);
if np = 0 then
t(i) := substr(s,p);
--dbms_output.put_line('t('||i||')='||t(i)||']');
exit;
end if;
t(i) := substr(s,p,np-p);
--dbms_output.put_line('s="'||s||'"');
--dbms_output.put_line('p='||p||':'||'np='||np);
--dbms_output.put_line('t('||i||')='||t(i)||'}');
p := np + 1;
i := i + 1;
end loop;
return t;
end;

--将BASE64前的转换成ZLIB后的
function translate_r_raw(x in raw) return raw
is
begin
return utl_raw.translate(x,S_SUB_TO_RAW,S_SUB_FROM_RAW);
end;
function translate_r(x in varchar2) return raw
is
begin
return translate_r_raw(utl_raw.cast_to_raw(utl_raw.cast_to_varchar2(x)));
end;

function base64_decode(s in varchar2) return varchar2
is
x dbms_sql.varchar2a;
y dbms_sql.varchar2a;
begin
x := dividesql(s);
for i in 1..x.count loop
--dbms_output.put_line('x'||i||':'||x(i)||':');
if rtrim(x(i),chr(10)) is not null then
y(i) := utl_encode.base64_decode(utl_raw.cast_to_raw(x(i)));
end if;
--dbms_output.put_line('y'||i||':'||y(i)||':');
end loop;
return unitesql(y,'');
end;

--解包WRAPPED后的代码
function unwrap_s(s in varchar2) return varchar2
is
begin
return
mycompress.inflate(
translate_r(
substr(
base64_decode(s)
, S_SHA1_DIGEST_LENGTH
)
)
);
end;
function unwrap(s in varchar2) return varchar2
is
begin
return unwrap_s(substr(s,instr(s,chr(10),1,20)+1));
end;

function unwrap(s in dbms_sql.varchar2a) return varchar2
is
begin
return unwrap(unitesql(s,''));
end;
function unwrap_obj(p_name in varchar2, p_type in varchar2, p_owner in varchar2 default user) return varchar2
is
v_text all_source.text%type;
s dbms_sql.varchar2a;
begin
begin
select text
into v_text
from all_source
where owner = p_owner
and name = p_name
and type = p_type
and line = 1
and rtrim(substr(text,1,instr(text,chr(10))-1)) like '%wrapped';
exception
when no_data_found then
dbms_output.put_line('not found, or not wrapped');
raise;
end;
for x in (select rownum,text
from all_source
where owner=p_owner
and name=p_name
and type=p_type
order by line)
loop
s(x.rownum) := x.text;
end loop;
return unwrap(s);
end;
procedure unwrap_obj_a(p_name in varchar2, p_type in varchar2, p_owner in varchar2 default user)
is
x dbms_sql.varchar2a;
begin
x := dividesql(unwrap_obj(p_name,p_type,p_owner));
for i in 1..x.count loop
dbms_output.put_line(x(i));
end loop;
end;

end;
/

运行解包过程可以得到源码

set pages 50000 line 130
set serveroutput on size unlimited
drop procedure prc1;
exec dbms_ddl.create_wrapped('create or replace procedure prc1 is begin dbms_output.put_line(''Hello World!''); end;')
select mypkg.unwrap(dbms_ddl.wrap('create or replace procedure prc1 is begin dbms_output.put_line(''Hello World!''); end;')) from dual;
select mypkg.unwrap_obj('PRC1','PROCEDURE') from dual;
exec mypkg.unwrap_obj_a(upper('dbms_aq'),'PACKAGE','SYS')
exec mypkg.unwrap_obj_a(upper('dbms_aq'),'PACKAGE BODY','SYS')

SQL> select mypkg.unwrap(dbms_ddl.wrap('create or replace procedure prc1 is begin dbms_output.put_line(''Hello World!''); end;')) from dual;

MYPKG.UNWRAP(DBMS_DDL.WRAP('CREATEORREPLACEPROCEDUREPRC1ISBEGINDBMS_OUTPUT.PUT_LINE(''HELLOWORLD!'');END;'))
----------------------------------------------------------------------------------------------------------------------------------
PROCEDURE prc1 IS BEGIN DBMS_OUTPUT.PUT_LINE('Hello World!'); END;

SQL> select mypkg.unwrap_obj('PRC1','PROCEDURE') from dual;

MYPKG.UNWRAP_OBJ('PRC1','PROCEDURE')
----------------------------------------------------------------------------------------------------------------------------------
PROCEDURE prc1 IS BEGIN DBMS_OUTPUT.PUT_LINE('Hello World!'); END;

SQL> exec mypkg.unwrap_obj_a(upper('dbms_aq'),'PACKAGE','SYS')
PACKAGE dbms_aq AUTHID CURRENT_USER AS
NAMESPACE_ANONYMOUS CONSTANT BINARY_INTEGER := 0;
NAMESPACE_AQ        CONSTANT BINARY_INTEGER := 1;
NAMESPACE_DBCHANGE CONSTANT BINARY_INTEGER := 2;
NTFN_QOS_RELIABLE        CONSTANT BINARY_INTEGER := 1;
NTFN_QOS_PAYLOAD         CONSTANT BINARY_INTEGER := 2;
NTFN_QOS_REPLICATE       CONSTANT BINARY_INTEGER := 4;
NTFN_QOS_SECURE          CONSTANT BINARY_INTEGER := 8;
NTFN_QOS_PURGE_ON_NTFN   CONSTANT BINARY_INTEGER := 16;
NTFN_QOS_MULTICBK        CONSTANT BINARY_INTEGER := 32;
NOTIFICATION_SUBSCRIBER  CONSTANT BINARY_INTEGER := 16;
PUBLIC_SUBSCRIBER        CONSTANT BINARY_INTEGER := 32;
IMMEDIATE           CONSTANT BINARY_INTEGER := 1;
ON_COMMIT           CONSTANT BINARY_INTEGER := 2;
BROWSE              CONSTANT BINARY_INTEGER := 1;
LOCKED              CONSTANT BINARY_INTEGER := 2;
REMOVE              CONSTANT BINARY_INTEGER := 3;
REMOVE_NODATA       CONSTANT BINARY_INTEGER := 4;
GET_SIGNATURE       CONSTANT BINARY_INTEGER := 5;
FIRST_MESSAGE             CONSTANT BINARY_INTEGER := 1;
FIRST                     CONSTANT BINARY_INTEGER := 1;
FIRST_MESSAGE_ONE_GROUP   CONSTANT BINARY_INTEGER := 1;
FIRST_MESSAGE_MULTI_GROUP CONSTANT BINARY_INTEGER := 4;
NEXT_MESSAGE              CONSTANT BINARY_INTEGER := 3;
NEXT                      CONSTANT BINARY_INTEGER := 3;
NEXT_MESSAGE_ONE_GROUP    CONSTANT BINARY_INTEGER := 3;
NEXT_MESSAGE_MULTI_GROUP  CONSTANT BINARY_INTEGER := 5;
NEXT_TRANSACTION          CONSTANT BINARY_INTEGER := 2;
WAITING             CONSTANT BINARY_INTEGER := 1;
READY               CONSTANT BINARY_INTEGER := 0;
PROCESSED           CONSTANT BINARY_INTEGER := 2;
EXPIRED             CONSTANT BINARY_INTEGER := 3;
SPILLED             CONSTANT BINARY_INTEGER := 7;
DEFERRED            CONSTANT BINARY_INTEGER := 8;
DEFRSPIL            CONSTANT BINARY_INTEGER := 9;
BUFFERED_EXPIRED    CONSTANT BINARY_INTEGER := 10;
BEFORE              CONSTANT BINARY_INTEGER := 2;
TOP                 CONSTANT BINARY_INTEGER := 3;
FOREVER             CONSTANT BINARY_INTEGER := -1;
NO_WAIT             CONSTANT BINARY_INTEGER := 0;
NO_DELAY            CONSTANT BINARY_INTEGER := 0;
NEVER               CONSTANT BINARY_INTEGER := -1;
OBJECT_TYPE         CONSTANT BINARY_INTEGER := 1;
RAW_TYPE            CONSTANT BINARY_INTEGER := 2;
BUFFERED_MESSAGE    CONSTANT BINARY_INTEGER := 1;
PERSISTENT               CONSTANT BINARY_INTEGER := 1;
BUFFERED                 CONSTANT BINARY_INTEGER := 2;
PERSISTENT_OR_BUFFERED   CONSTANT BINARY_INTEGER := 3;
JMS_TEXT_MESSAGE   CONSTANT BINARY_INTEGER := 0;
JMS_BYTES_MESSAGE  CONSTANT BINARY_INTEGER := 1;
JMS_STREAM_MESSAGE CONSTANT BINARY_INTEGER := 2;
JMS_MAP_MESSAGE    CONSTANT BINARY_INTEGER := 3;
JMS_OBJECT_MESSAGE CONSTANT BINARY_INTEGER := 4;
TYPE AQ$_RECIPIENT_LIST_T IS TABLE OF SYS.AQ$_AGENT
INDEX BY BINARY_INTEGER;
TYPE AQ$_AGENT_LIST_T IS TABLE OF SYS.AQ$_AGENT INDEX BY BINARY_INTEGER;
TYPE MESSAGE_PROPERTIES_T IS
RECORD(
PRIORITY          BINARY_INTEGER NOT NULL DEFAULT 1,
DELAY             BINARY_INTEGER NOT NULL DEFAULT NO_DELAY,
EXPIRATION        BINARY_INTEGER NOT NULL DEFAULT NEVER,
CORRELATION       VARCHAR2(128) DEFAULT NULL,
ATTEMPTS          BINARY_INTEGER,
RECIPIENT_LIST    AQ$_RECIPIENT_LIST_T,
EXCEPTION_QUEUE   VARCHAR2(61) DEFAULT NULL,
ENQUEUE_TIME      DATE,
STATE             BINARY_INTEGER,
SENDER_ID         SYS.AQ$_AGENT DEFAULT NULL,
ORIGINAL_MSGID    RAW(16)       DEFAULT NULL,
SIGNATURE         AQ$_SIG_PROP  DEFAULT NULL,
TRANSACTION_GROUP VARCHAR2(30)  DEFAULT NULL,
USER_PROPERTY     SYS.ANYDATA   DEFAULT NULL,
DELIVERY_MODE     PLS_INTEGER   NOT NULL DEFAULT PERSISTENT);
TYPE MESSAGE_PROPERTIES_ARRAY_T IS VARRAY(2147483647)
OF MESSAGE_PROPERTIES_T;
TYPE ERROR_INFO_T IS RECORD (
ERROR_POSITION    NUMBER,
ERROR_NO          NUMBER,
ERROR_MSG         VARCHAR2(4000));
TYPE ERROR_ARRAY_T IS VARRAY(2147483647) OF ERROR_INFO_T;
TYPE MSGID_ARRAY_T IS VARRAY(2147483647)  OF RAW(16);
TYPE ENQUEUE_OPTIONS_T IS
RECORD(
VISIBILITY          BINARY_INTEGER NOT NULL DEFAULT ON_COMMIT,
RELATIVE_MSGID      RAW(16)  DEFAULT NULL,
SEQUENCE_DEVIATION  BINARY_INTEGER DEFAULT NULL,
TRANSFORMATION      VARCHAR2(61) DEFAULT NULL,
BUFMESG_PROP        BINARY_INTEGER DEFAULT NULL,
DELIVERY_MODE     PLS_INTEGER   NOT NULL DEFAULT PERSISTENT);
TYPE DEQUEUE_OPTIONS_T IS
RECORD(
CONSUMER_NAME     VARCHAR2(30) DEFAULT NULL,
DEQUEUE_MODE      BINARY_INTEGER NOT NULL DEFAULT REMOVE,
NAVIGATION        BINARY_INTEGER NOT NULL DEFAULT NEXT_MESSAGE,
VISIBILITY        BINARY_INTEGER NOT NULL DEFAULT ON_COMMIT,
WAIT              BINARY_INTEGER NOT NULL DEFAULT FOREVER,
MSGID             RAW(16)                 DEFAULT NULL,
CORRELATION       VARCHAR2(128)           DEFAULT NULL,
DEQ_CONDITION     VARCHAR2(4000)          DEFAULT NULL,
SIGNATURE         AQ$_SIG_PROP            DEFAULT NULL,
TRANSFORMATION    VARCHAR2(61)            DEFAULT NULL,
BUFMESG_PROP      BINARY_INTEGER          DEFAULT NULL,
DELIVERY_MODE     PLS_INTEGER   NOT NULL  DEFAULT PERSISTENT);
PROCEDURE ENQUEUE(
QUEUE_NAME           IN    VARCHAR2,
ENQUEUE_OPTIONS      IN    ENQUEUE_OPTIONS_T,
MESSAGE_PROPERTIES   IN    MESSAGE_PROPERTIES_T,
PAYLOAD              IN    "<OPAQUE_1>",
MSGID                OUT   RAW);
PROCEDURE ENQUEUE(
QUEUE_NAME           IN    VARCHAR2,
ENQUEUE_OPTIONS      IN    ENQUEUE_OPTIONS_T,
MESSAGE_PROPERTIES   IN    MESSAGE_PROPERTIES_T,
PAYLOAD              IN    "<ADT_1>",
MSGID                OUT   RAW);
PROCEDURE ENQUEUE(
QUEUE_NAME           IN    VARCHAR2,
ENQUEUE_OPTIONS      IN    ENQUEUE_OPTIONS_T,
MESSAGE_PROPERTIES   IN    MESSAGE_PROPERTIES_T,
PAYLOAD              IN    RAW,
MSGID                OUT   RAW);
FUNCTION ENQUEUE_ARRAY (
QUEUE_NAME                IN     VARCHAR2,
ENQUEUE_OPTIONS           IN     ENQUEUE_OPTIONS_T,
ARRAY_SIZE                IN     PLS_INTEGER,
MESSAGE_PROPERTIES_ARRAY  IN     MESSAGE_PROPERTIES_ARRAY_T,
PAYLOAD_ARRAY             IN     "<COLLECTION_1>",
MSGID_ARRAY               OUT    MSGID_ARRAY_T)
RETURN PLS_INTEGER;
FUNCTION ENQUEUE_ARRAY (
QUEUE_NAME                IN     VARCHAR2,
ENQUEUE_OPTIONS           IN     ENQUEUE_OPTIONS_T,
ARRAY_SIZE                IN     PLS_INTEGER,
MESSAGE_PROPERTIES_ARRAY  IN     MESSAGE_PROPERTIES_ARRAY_T,
PAYLOAD_ARRAY             IN     "<COLLECTION_1>",
MSGID_ARRAY               OUT    MSGID_ARRAY_T,
ERROR_ARRAY               OUT    ERROR_ARRAY_T)
RETURN PLS_INTEGER;
PROCEDURE DEQUEUE(
QUEUE_NAME           IN    VARCHAR2,
DEQUEUE_OPTIONS      IN    DEQUEUE_OPTIONS_T,
MESSAGE_PROPERTIES   OUT   MESSAGE_PROPERTIES_T,
PAYLOAD              OUT   "<OPAQUE_1>",
MSGID                OUT   RAW);
PROCEDURE DEQUEUE(
QUEUE_NAME           IN    VARCHAR2,
DEQUEUE_OPTIONS      IN    DEQUEUE_OPTIONS_T,
MESSAGE_PROPERTIES   OUT   MESSAGE_PROPERTIES_T,
PAYLOAD              OUT   "<ADT_1>",
MSGID                OUT   RAW);
PROCEDURE DEQUEUE(
QUEUE_NAME           IN    VARCHAR2,
DEQUEUE_OPTIONS      IN    DEQUEUE_OPTIONS_T,
MESSAGE_PROPERTIES   OUT   MESSAGE_PROPERTIES_T,
PAYLOAD              OUT   RAW,
MSGID                OUT   RAW);
FUNCTION DEQUEUE_ARRAY(
QUEUE_NAME                IN    VARCHAR2,
DEQUEUE_OPTIONS           IN    DEQUEUE_OPTIONS_T,
ARRAY_SIZE                IN    PLS_INTEGER,
MESSAGE_PROPERTIES_ARRAY  OUT   MESSAGE_PROPERTIES_ARRAY_T,
PAYLOAD_ARRAY             OUT   "<COLLECTION_1>",
MSGID_ARRAY               OUT   MSGID_ARRAY_T)
RETURN PLS_INTEGER;
FUNCTION DEQUEUE_ARRAY(
QUEUE_NAME                IN    VARCHAR2,
DEQUEUE_OPTIONS           IN    DEQUEUE_OPTIONS_T,
ARRAY_SIZE                IN    PLS_INTEGER,
MESSAGE_PROPERTIES_ARRAY  OUT   MESSAGE_PROPERTIES_ARRAY_T,
PAYLOAD_ARRAY             OUT   "<COLLECTION_1>",
MSGID_ARRAY               OUT   MSGID_ARRAY_T,
ERROR_ARRAY               OUT   ERROR_ARRAY_T)
RETURN PLS_INTEGER;
PROCEDURE LISTEN(
AGENT_LIST           IN    AQ$_AGENT_LIST_T,
WAIT                 IN    BINARY_INTEGER DEFAULT FOREVER,
AGENT                OUT   SYS.AQ$_AGENT);
PROCEDURE LISTEN(
AGENT_LIST            IN    AQ$_AGENT_LIST_T,
WAIT                  IN    BINARY_INTEGER DEFAULT FOREVER,
LISTEN_DELIVERY_MODE  IN    PLS_INTEGER DEFAULT PERSISTENT,
AGENT                 OUT   SYS.AQ$_AGENT,
MESSAGE_DELIVERY_MODE OUT   PLS_INTEGER);
PROCEDURE BIND_AGENT(
AGENT                IN    AQ$_AGENT,
CERTIFICATE_LOCATION IN    VARCHAR2 DEFAULT NULL);
PROCEDURE UNBIND_AGENT(
AGENT                IN    AQ$_AGENT);
PROCEDURE REGISTER(
REG_LIST      IN       SYS.AQ$_REG_INFO_LIST,
REG_COUNT     IN       NUMBER);
PROCEDURE UNREGISTER(
REG_LIST      IN       SYS.AQ$_REG_INFO_LIST,
REG_COUNT     IN       NUMBER);
PROCEDURE POST(
POST_LIST     IN       SYS.AQ$_POST_INFO_LIST,
POST_COUNT    IN       NUMBER);
PROCEDURE AQ$_ENQUEUE(
QUEUE_NAME           IN      VARCHAR2,
SENDER_NAME          IN      VARCHAR2         DEFAULT NULL,
SENDER_ADDR          IN      VARCHAR2         DEFAULT NULL,
SENDER_PROTOCOL      IN      BINARY_INTEGER   DEFAULT NULL,
ORIGINAL_MSGID       IN      RAW              DEFAULT NULL,
CORRELATION          IN      VARCHAR2         DEFAULT NULL,
VISIBILITY           IN      BINARY_INTEGER   DEFAULT ON_COMMIT,
PRIORITY             IN      BINARY_INTEGER   DEFAULT 1,
DELAY                IN      BINARY_INTEGER   DEFAULT 0,
EXPIRATION           IN      BINARY_INTEGER   DEFAULT NEVER,
RELATIVE_MSGID       IN      RAW              DEFAULT NULL,
SEQUENCE_DEVIATION   IN      BINARY_INTEGER   DEFAULT NULL,
EXCEPTION_QUEUE      IN      VARCHAR2         DEFAULT NULL,
RECIPIENTS           IN      AQ$_RECIPIENT_LIST_T,
PAYLOAD_TYPE         IN      BINARY_INTEGER   DEFAULT OBJECT_TYPE,
RAW_USER_DATA        IN      RAW              DEFAULT NULL,
OBJECT_USER_DATA     IN      "<ADT_1>",
MSGID                OUT     RAW,
SIGNATURE            IN      AQ$_SIG_PROP     DEFAULT NULL,
TRANSFORMATION       IN      VARCHAR2         DEFAULT NULL,
BUFMESG_PROP         IN      BINARY_INTEGER   DEFAULT NULL,
ANYDATA_CONVERT      IN      BOOLEAN          DEFAULT FALSE,
LCR2XML_CONVERT      IN      BOOLEAN          DEFAULT FALSE,
USER_PROPERTY        IN      SYS.ANYDATA      DEFAULT NULL,
DELIVERY_MODE        IN      PLS_INTEGER      DEFAULT PERSISTENT);
PROCEDURE AQ$_DEQUEUE(
QUEUE_NAME              IN      VARCHAR2,
SUBSCRIBER              IN      VARCHAR2        DEFAULT NULL,
MSGID                   IN      RAW             DEFAULT NULL,
CORRELATION             IN      VARCHAR2        DEFAULT NULL,
DEQUEUE_MODE            IN      BINARY_INTEGER  DEFAULT REMOVE,
NAVIGATION              IN      BINARY_INTEGER  DEFAULT FIRST,
VISIBILITY              IN      BINARY_INTEGER  DEFAULT ON_COMMIT,
WAIT                    IN      BINARY_INTEGER  DEFAULT FOREVER,
ENQUEUE_TIME            OUT     DATE,
STATE                   OUT     BINARY_INTEGER,
OUT_MSGID               OUT     RAW,
OUT_CORRELATION         OUT     VARCHAR2,
PRIORITY                OUT     BINARY_INTEGER,
DELAY                   OUT     BINARY_INTEGER,
EXPIRATION              OUT     BINARY_INTEGER,
ATTEMPTS                OUT     BINARY_INTEGER,
EXCEPTION_QUEUE         OUT     VARCHAR2,
REMOTE_RECIPIENTS       OUT     AQ$_RECIPIENT_LIST_T,
SENDER_NAME             OUT     VARCHAR2,
SENDER_ADDR             OUT     VARCHAR2,
SENDER_PROTOCOL         OUT     BINARY_INTEGER,
ORIGINAL_MSGID          OUT     RAW,
PAYLOAD_TYPE            IN      BINARY_INTEGER    DEFAULT OBJECT_TYPE,
RAW_USER_DATA           OUT     RAW,
OBJECT_USER_DATA        OUT     "<ADT_1>",
DEQ_CONDITION           IN      VARCHAR2          DEFAULT NULL,
SIGNATURE               IN      SYS.AQ$_SIG_PROP  DEFAULT NULL,
OUT_SIGN                OUT     SYS.AQ$_SIG_PROP,
TRANSFORMATION          IN      VARCHAR2        DEFAULT NULL,
BUFMESG_PROP            IN      BINARY_INTEGER  DEFAULT NULL,
LCR2XML_CONVERT         IN      BOOLEAN         DEFAULT FALSE);
PROCEDURE AQ$_DEQUEUE(
QUEUE_NAME              IN      VARCHAR2,
SUBSCRIBER              IN      VARCHAR2        DEFAULT NULL,
MSGID                   IN      RAW             DEFAULT NULL,
CORRELATION             IN      VARCHAR2        DEFAULT NULL,
DEQUEUE_MODE            IN      BINARY_INTEGER  DEFAULT REMOVE,
NAVIGATION              IN      BINARY_INTEGER  DEFAULT FIRST,
VISIBILITY              IN      BINARY_INTEGER  DEFAULT ON_COMMIT,
WAIT                    IN      BINARY_INTEGER  DEFAULT FOREVER,
ENQUEUE_TIME            OUT     DATE,
STATE                   OUT     BINARY_INTEGER,
OUT_MSGID               OUT     RAW,
OUT_CORRELATION         OUT     VARCHAR2,
PRIORITY                OUT     BINARY_INTEGER,
DELAY                   OUT     BINARY_INTEGER,
EXPIRATION              OUT     BINARY_INTEGER,
ATTEMPTS                OUT     BINARY_INTEGER,
EXCEPTION_QUEUE         OUT     VARCHAR2,
REMOTE_RECIPIENTS       OUT     AQ$_RECIPIENT_LIST_T,
SENDER_NAME             OUT     VARCHAR2,
SENDER_ADDR             OUT     VARCHAR2,
SENDER_PROTOCOL         OUT     BINARY_INTEGER,
ORIGINAL_MSGID          OUT     RAW,
PAYLOAD_TYPE            IN      BINARY_INTEGER    DEFAULT OBJECT_TYPE,
RAW_USER_DATA           OUT     RAW,
OBJECT_USER_DATA        OUT     "<ADT_1>",
DEQ_CONDITION           IN      VARCHAR2          DEFAULT NULL,
SIGNATURE               IN      SYS.AQ$_SIG_PROP  DEFAULT NULL,
OUT_SIGN                OUT     SYS.AQ$_SIG_PROP,
TRANSFORMATION          IN      VARCHAR2        DEFAULT NULL,
BUFMESG_PROP            IN      BINARY_INTEGER  DEFAULT NULL,
LCR2XML_CONVERT         IN      BOOLEAN         DEFAULT FALSE,
USER_PROPERTY           OUT     SYS.ANYDATA
);
PROCEDURE AQ$_DEQUEUE(
QUEUE_NAME              IN      VARCHAR2,
SUBSCRIBER              IN      VARCHAR2        DEFAULT NULL,
MSGID                   IN      RAW             DEFAULT NULL,
CORRELATION             IN      VARCHAR2        DEFAULT NULL,
DEQUEUE_MODE            IN      BINARY_INTEGER  DEFAULT REMOVE,
NAVIGATION              IN      BINARY_INTEGER  DEFAULT FIRST,
VISIBILITY              IN      BINARY_INTEGER  DEFAULT ON_COMMIT,
WAIT                    IN      BINARY_INTEGER  DEFAULT FOREVER,
ENQUEUE_TIME            OUT     DATE,
STATE                   OUT     BINARY_INTEGER,
OUT_MSGID               OUT     RAW,
OUT_CORRELATION         OUT     VARCHAR2,
PRIORITY                OUT     BINARY_INTEGER,
DELAY                   OUT     BINARY_INTEGER,
EXPIRATION              OUT     BINARY_INTEGER,
ATTEMPTS                OUT     BINARY_INTEGER,
EXCEPTION_QUEUE         OUT     VARCHAR2,
REMOTE_RECIPIENTS       OUT     AQ$_RECIPIENT_LIST_T,
SENDER_NAME             OUT     VARCHAR2,
SENDER_ADDR             OUT     VARCHAR2,
SENDER_PROTOCOL         OUT     BINARY_INTEGER,
ORIGINAL_MSGID          OUT     RAW,
PAYLOAD_TYPE            IN      BINARY_INTEGER    DEFAULT OBJECT_TYPE,
RAW_USER_DATA           OUT     RAW,
OBJECT_USER_DATA        OUT     "<ADT_1>",
DEQ_CONDITION           IN      VARCHAR2          DEFAULT NULL,
SIGNATURE               IN      SYS.AQ$_SIG_PROP  DEFAULT NULL,
OUT_SIGN                OUT     SYS.AQ$_SIG_PROP,
TRANSFORMATION          IN      VARCHAR2        DEFAULT NULL,
BUFMESG_PROP            IN      BINARY_INTEGER  DEFAULT NULL,
LCR2XML_CONVERT         IN      BOOLEAN         DEFAULT FALSE,
USER_PROPERTY           OUT     SYS.ANYDATA,
DELIVERY_MODE           IN      PLS_INTEGER      DEFAULT PERSISTENT,
OUT_DELIVERY_MODE       OUT     PLS_INTEGER
);
END DBMS_AQ;

PL/SQL procedure successfully completed.

SQL> exec mypkg.unwrap_obj_a(upper('dbms_aq'),'PACKAGE BODY','SYS')
PACKAGE BODY dbms_aq AS
PROCEDURE AQ$_ENQUEUE(
QUEUE_NAME           IN      VARCHAR2,
SENDER_NAME          IN      VARCHAR2         DEFAULT NULL,
SENDER_ADDR          IN      VARCHAR2         DEFAULT NULL,
SENDER_PROTOCOL      IN      BINARY_INTEGER   DEFAULT NULL,
ORIGINAL_MSGID       IN      RAW              DEFAULT NULL,
CORRELATION          IN      VARCHAR2         DEFAULT NULL,
VISIBILITY           IN      BINARY_INTEGER   DEFAULT ON_COMMIT,
PRIORITY             IN      BINARY_INTEGER   DEFAULT 1,
DELAY                IN      BINARY_INTEGER   DEFAULT 0,
EXPIRATION           IN      BINARY_INTEGER   DEFAULT NEVER,
RELATIVE_MSGID       IN      RAW              DEFAULT NULL,
SEQUENCE_DEVIATION   IN      BINARY_INTEGER   DEFAULT NULL,
EXCEPTION_QUEUE      IN      VARCHAR2         DEFAULT NULL,
RECIPIENTS           IN      AQ$_RECIPIENT_LIST_T,
PAYLOAD_TYPE         IN      BINARY_INTEGER   DEFAULT OBJECT_TYPE,
RAW_USER_DATA        IN      RAW              DEFAULT NULL,
OBJECT_USER_DATA     IN      "<ADT_1>",
MSGID                OUT     RAW,
SIGNATURE            IN      AQ$_SIG_PROP     DEFAULT NULL,
TRANSFORMATION       IN      VARCHAR2         DEFAULT NULL,
BUFMESG_PROP         IN      BINARY_INTEGER   DEFAULT NULL,
ANYDATA_CONVERT      IN      BOOLEAN          DEFAULT FALSE,
LCR2XML_CONVERT      IN      BOOLEAN          DEFAULT FALSE,
USER_PROPERTY        IN      SYS.ANYDATA      DEFAULT NULL,
DELIVERY_MODE        IN      PLS_INTEGER      DEFAULT PERSISTENT
) IS
EXTERNAL
NAME "kwqiexenqueue"
WITH CONTEXT
PARAMETERS(CONTEXT,
QUEUE_NAME          OCISTRING,   QUEUE_NAME         INDICATOR SB4,
SENDER_NAME         OCISTRING,   SENDER_NAME        INDICATOR SB4,
SENDER_ADDR         OCISTRING,   SENDER_ADDR        INDICATOR SB4,
SENDER_PROTOCOL     SB4,         SENDER_PROTOCOL    INDICATOR SB4,
ORIGINAL_MSGID      OCIRAW,      ORIGINAL_MSGID     INDICATOR SB4,
CORRELATION         OCISTRING,   CORRELATION        INDICATOR SB4,
VISIBILITY          SB4,         VISIBILITY         INDICATOR SB4,
PRIORITY            SB4,         PRIORITY           INDICATOR SB4,
DELAY               SB4,         DELAY              INDICATOR SB4,
EXPIRATION          SB4,         EXPIRATION         INDICATOR SB4,
RELATIVE_MSGID      OCIRAW,      RELATIVE_MSGID     INDICATOR SB4,
SEQUENCE_DEVIATION  SB4,
SEQUENCE_DEVIATION  INDICATOR SB4,
EXCEPTION_QUEUE     OCISTRING,
EXCEPTION_QUEUE     INDICATOR SB4,
PAYLOAD_TYPE        SB4,
RAW_USER_DATA       OCIRAW,      RAW_USER_DATA      INDICATOR SB4,
OBJECT_USER_DATA,
OBJECT_USER_DATA     INDICATOR STRUCT,
OBJECT_USER_DATA     TDO OCITYPE,
RECIPIENTS           OCICOLL,
MSGID                OCIRAW,
SIGNATURE,
SIGNATURE            INDICATOR STRUCT,
SIGNATURE            TDO OCITYPE,
TRANSFORMATION       OCISTRING,
TRANSFORMATION       INDICATOR SB4,
BUFMESG_PROP         SB4,
BUFMESG_PROP         INDICATOR SB4,
ANYDATA_CONVERT      SB4,
ANYDATA_CONVERT      INDICATOR  SB4,
LCR2XML_CONVERT      SB4,
LCR2XML_CONVERT      INDICATOR SB4,
USER_PROPERTY,
USER_PROPERTY        INDICATOR SB4,
DELIVERY_MODE        SB4
)
LIBRARY DBMS_AQ_LIB;
PROCEDURE AQ$_DEQUEUE(
QUEUE_NAME              IN      VARCHAR2,
SUBSCRIBER              IN      VARCHAR2        DEFAULT NULL,
MSGID                   IN      RAW             DEFAULT NULL,
CORRELATION             IN      VARCHAR2        DEFAULT NULL,
DEQUEUE_MODE            IN      BINARY_INTEGER  DEFAULT REMOVE,
NAVIGATION              IN      BINARY_INTEGER  DEFAULT FIRST,
VISIBILITY              IN      BINARY_INTEGER  DEFAULT ON_COMMIT,
WAIT                    IN      BINARY_INTEGER  DEFAULT FOREVER,
ENQUEUE_TIME            OUT     DATE,
STATE                   OUT     BINARY_INTEGER,
OUT_MSGID               OUT     RAW,
OUT_CORRELATION         OUT     VARCHAR2,
PRIORITY                OUT     BINARY_INTEGER,
DELAY                   OUT     BINARY_INTEGER,
EXPIRATION              OUT     BINARY_INTEGER,
ATTEMPTS                OUT     BINARY_INTEGER,
EXCEPTION_QUEUE         OUT     VARCHAR2,
REMOTE_RECIPIENTS       OUT     AQ$_RECIPIENT_LIST_T,
SENDER_NAME             OUT     VARCHAR2,
SENDER_ADDR             OUT     VARCHAR2,
SENDER_PROTOCOL         OUT     BINARY_INTEGER,
ORIGINAL_MSGID          OUT     RAW,
PAYLOAD_TYPE            IN      BINARY_INTEGER  DEFAULT OBJECT_TYPE,
RAW_USER_DATA           OUT     RAW,
OBJECT_USER_DATA        OUT     "<ADT_1>",
DEQ_CONDITION           IN      VARCHAR2        DEFAULT NULL,
SIGNATURE               IN      SYS.AQ$_SIG_PROP  DEFAULT NULL,
OUT_SIGN                OUT     SYS.AQ$_SIG_PROP,
TRANSFORMATION          IN      VARCHAR2          DEFAULT NULL,
BUFMESG_PROP            IN      BINARY_INTEGER    DEFAULT NULL,
LCR2XML_CONVERT         IN      BOOLEAN           DEFAULT FALSE,
USER_PROPERTY           OUT     SYS.ANYDATA
)
IS
EXTERNAL
NAME "kwqidxdequeue101"
WITH CONTEXT
PARAMETERS(CONTEXT,
QUEUE_NAME      OCISTRING,        QUEUE_NAME        INDICATOR SB4,
SUBSCRIBER      OCISTRING,        SUBSCRIBER        INDICATOR SB4,
MSGID           OCIRAW,           MSGID             INDICATOR SB4,
CORRELATION     OCISTRING,        CORRELATION       INDICATOR SB4,
DEQUEUE_MODE    SB4,
WAIT            SB4,              WAIT              INDICATOR SB4,
VISIBILITY      SB4,              VISIBILITY        INDICATOR SB4,
NAVIGATION      SB4,
OUT_MSGID       OCIRAW,
OUT_CORRELATION OCISTRING,        OUT_CORRELATION   INDICATOR SB4,
PRIORITY        SB4,              PRIORITY          INDICATOR SB4,
DELAY           SB4,              DELAY             INDICATOR SB4,
EXPIRATION      SB4,              EXPIRATION        INDICATOR SB4,
STATE           SB4,              STATE             INDICATOR SB4,
ENQUEUE_TIME    OCIDATE,          ENQUEUE_TIME      INDICATOR SB4,
ATTEMPTS        SB4,              ATTEMPTS          INDICATOR SB4,
EXCEPTION_QUEUE OCISTRING,           EXCEPTION_QUEUE   INDICATOR SB4,
REMOTE_RECIPIENTS         OCICOLL,
REMOTE_RECIPIENTS         INDICATOR SB2,
SENDER_NAME     OCISTRING,        SENDER_NAME       INDICATOR SB4,
SENDER_ADDR     OCISTRING,           SENDER_ADDR       INDICATOR SB4,
SENDER_PROTOCOL SB4,                 SENDER_PROTOCOL   INDICATOR SB4,
ORIGINAL_MSGID  OCIRAW,           ORIGINAL_MSGID    INDICATOR SB4,
PAYLOAD_TYPE    SB4,
RAW_USER_DATA   OCIRAW,           RAW_USER_DATA     INDICATOR SB4,
RAW_USER_DATA   MAXLEN    SB4,
OBJECT_USER_DATA,
OBJECT_USER_DATA INDICATOR STRUCT,
OBJECT_USER_DATA TDO OCITYPE,
OBJECT_USER_DATA DURATION OCIDURATION,
DEQ_CONDITION   OCISTRING,        DEQ_CONDITION     INDICATOR SB4,
SIGNATURE,
SIGNATURE INDICATOR STRUCT,
SIGNATURE TDO OCITYPE,
OUT_SIGN,
OUT_SIGN INDICATOR STRUCT,
OUT_SIGN TDO OCITYPE,
OUT_SIGN DURATION OCIDURATION,
TRANSFORMATION  OCISTRING,
TRANSFORMATION  INDICATOR SB4,
BUFMESG_PROP         SB4,
BUFMESG_PROP         INDICATOR SB4,
LCR2XML_CONVERT      SB4,
LCR2XML_CONVERT      INDICATOR SB4,
USER_PROPERTY,
USER_PROPERTY        INDICATOR SB4
)
LIBRARY DBMS_AQ_LIB;
PROCEDURE ENQUEUE(
QUEUE_NAME          IN  VARCHAR2,
ENQUEUE_OPTIONS     IN  ENQUEUE_OPTIONS_T,
MESSAGE_PROPERTIES  IN  MESSAGE_PROPERTIES_T,
PAYLOAD             IN  "<ADT_1>",
MSGID               OUT RAW) IS
EXTERNAL
NAME "kwqiadtenqueue"
WITH CONTEXT
PARAMETERS(CONTEXT,
QUEUE_NAME OCISTRING, QUEUE_NAME INDICATOR SB4,
ENQUEUE_OPTIONS,
ENQUEUE_OPTIONS INDICATOR STRUCT,
MESSAGE_PROPERTIES,
MESSAGE_PROPERTIES INDICATOR STRUCT,
PAYLOAD, PAYLOAD INDICATOR STRUCT, PAYLOAD TDO OCITYPE,
MSGID   OCIRAW)
LIBRARY DBMS_AQ_LIB;
PROCEDURE ENQUEUE(
QUEUE_NAME          IN  VARCHAR2,
ENQUEUE_OPTIONS     IN  ENQUEUE_OPTIONS_T,
MESSAGE_PROPERTIES  IN  MESSAGE_PROPERTIES_T,
PAYLOAD             IN  RAW,
MSGID               OUT RAW) IS
SNAME VARCHAR2(30) := NULL;
SADDR VARCHAR2(1024) := NULL;
SPROTOCOL NUMBER := NULL;
ORIG_MID RAW(16) := NULL;
BEGIN
IF MESSAGE_PROPERTIES.SENDER_ID IS NOT NULL THEN
SNAME := MESSAGE_PROPERTIES.SENDER_ID.NAME;
SADDR := MESSAGE_PROPERTIES.SENDER_ID.ADDRESS;
SPROTOCOL := MESSAGE_PROPERTIES.SENDER_ID.PROTOCOL;
END IF;
IF MESSAGE_PROPERTIES.ORIGINAL_MSGID IS NOT NULL THEN
ORIG_MID := MESSAGE_PROPERTIES.ORIGINAL_MSGID;
END IF;
AQ$_ENQUEUE(
QUEUE_NAME => QUEUE_NAME,
SENDER_NAME => SNAME,
SENDER_ADDR => SADDR,
SENDER_PROTOCOL => SPROTOCOL,
ORIGINAL_MSGID => ORIG_MID,
VISIBILITY => ENQUEUE_OPTIONS.VISIBILITY,
RELATIVE_MSGID => ENQUEUE_OPTIONS.RELATIVE_MSGID,
SEQUENCE_DEVIATION => ENQUEUE_OPTIONS.SEQUENCE_DEVIATION,
CORRELATION => MESSAGE_PROPERTIES.CORRELATION,
PRIORITY => MESSAGE_PROPERTIES.PRIORITY,
DELAY => MESSAGE_PROPERTIES.DELAY,
EXPIRATION => MESSAGE_PROPERTIES.EXPIRATION,
EXCEPTION_QUEUE => MESSAGE_PROPERTIES.EXCEPTION_QUEUE,
TRANSFORMATION => ENQUEUE_OPTIONS.TRANSFORMATION,
RECIPIENTS => MESSAGE_PROPERTIES.RECIPIENT_LIST,
MSGID => MSGID,
PAYLOAD_TYPE => RAW_TYPE,
RAW_USER_DATA => PAYLOAD,
OBJECT_USER_DATA => NULL,
SIGNATURE => MESSAGE_PROPERTIES.SIGNATURE,
BUFMESG_PROP => ENQUEUE_OPTIONS.BUFMESG_PROP,
USER_PROPERTY => MESSAGE_PROPERTIES.USER_PROPERTY,
DELIVERY_MODE => ENQUEUE_OPTIONS.DELIVERY_MODE);
END;
PROCEDURE ENQUEUE(
QUEUE_NAME          IN  VARCHAR2,
ENQUEUE_OPTIONS     IN  ENQUEUE_OPTIONS_T,
MESSAGE_PROPERTIES  IN  MESSAGE_PROPERTIES_T,
PAYLOAD             IN  "<OPAQUE_1>",
MSGID               OUT RAW) IS
EXTERNAL
NAME "kwqiopqenqueue"
WITH CONTEXT
PARAMETERS(CONTEXT,
QUEUE_NAME OCISTRING, QUEUE_NAME INDICATOR SB4,
ENQUEUE_OPTIONS,
ENQUEUE_OPTIONS INDICATOR STRUCT,
MESSAGE_PROPERTIES,
MESSAGE_PROPERTIES INDICATOR STRUCT,
PAYLOAD, PAYLOAD INDICATOR SB2, PAYLOAD TDO OCITYPE,
MSGID   OCIRAW)
LIBRARY DBMS_AQ_LIB;
FUNCTION ENQUEUE_ARRAY (
QUEUE_NAME                IN    VARCHAR2,
ENQUEUE_OPTIONS           IN    ENQUEUE_OPTIONS_T,
ARRAY_SIZE                IN    PLS_INTEGER,
MESSAGE_PROPERTIES_ARRAY  IN    MESSAGE_PROPERTIES_ARRAY_T,
PAYLOAD_ARRAY             IN    "<COLLECTION_1>",
MSGID_ARRAY               OUT   MSGID_ARRAY_T) RETURN PLS_INTEGER IS
EXTERNAL
NAME "kwqienqarray0"
WITH CONTEXT
PARAMETERS(CONTEXT,
QUEUE_NAME OCISTRING, QUEUE_NAME INDICATOR SB4,
ENQUEUE_OPTIONS,
ENQUEUE_OPTIONS INDICATOR STRUCT,
ARRAY_SIZE UB4,
MESSAGE_PROPERTIES_ARRAY,
MESSAGE_PROPERTIES_ARRAY INDICATOR,
PAYLOAD_ARRAY, PAYLOAD_ARRAY INDICATOR, PAYLOAD_ARRAY TDO OCITYPE,
MSGID_ARRAY,
RETURN UB4)
LIBRARY DBMS_AQ_LIB;
FUNCTION ENQUEUE_ARRAY (
QUEUE_NAME                IN    VARCHAR2,
ENQUEUE_OPTIONS           IN    ENQUEUE_OPTIONS_T,
ARRAY_SIZE                IN    PLS_INTEGER,
MESSAGE_PROPERTIES_ARRAY  IN    MESSAGE_PROPERTIES_ARRAY_T,
PAYLOAD_ARRAY             IN    "<COLLECTION_1>",
MSGID_ARRAY               OUT   MSGID_ARRAY_T,
ERROR_ARRAY               OUT   ERROR_ARRAY_T) RETURN PLS_INTEGER IS
EXTERNAL
NAME "kwqienqarray"
WITH CONTEXT
PARAMETERS(CONTEXT,
QUEUE_NAME OCISTRING, QUEUE_NAME INDICATOR SB4,
ENQUEUE_OPTIONS,
ENQUEUE_OPTIONS INDICATOR STRUCT,
ARRAY_SIZE UB4,
MESSAGE_PROPERTIES_ARRAY,
MESSAGE_PROPERTIES_ARRAY INDICATOR,
PAYLOAD_ARRAY, PAYLOAD_ARRAY INDICATOR, PAYLOAD_ARRAY TDO OCITYPE,
MSGID_ARRAY,
ERROR_ARRAY,ERROR_ARRAY INDICATOR SB2,
ERROR_ARRAY DURATION OCIDURATION,
RETURN UB4)
LIBRARY DBMS_AQ_LIB;
PROCEDURE DEQUEUE(
QUEUE_NAME           IN         VARCHAR2,
DEQUEUE_OPTIONS      IN         DEQUEUE_OPTIONS_T,
MESSAGE_PROPERTIES   OUT        MESSAGE_PROPERTIES_T,
PAYLOAD              OUT       "<OPAQUE_1>",
MSGID                OUT        RAW) IS
EXTERNAL
NAME "kwqiopqdequeue"
WITH CONTEXT
PARAMETERS(CONTEXT,
QUEUE_NAME      OCISTRING,        QUEUE_NAME        INDICATOR SB4,
DEQUEUE_OPTIONS,
DEQUEUE_OPTIONS INDICATOR STRUCT,
MESSAGE_PROPERTIES,
MESSAGE_PROPERTIES INDICATOR STRUCT,
MESSAGE_PROPERTIES DURATION OCIDURATION,
PAYLOAD, PAYLOAD INDICATOR SB2,
PAYLOAD TDO OCITYPE, PAYLOAD DURATION OCIDURATION,
MSGID   OCIRAW)
LIBRARY DBMS_AQ_LIB;
PROCEDURE DEQUEUE(
QUEUE_NAME           IN         VARCHAR2,
DEQUEUE_OPTIONS      IN         DEQUEUE_OPTIONS_T,
MESSAGE_PROPERTIES   OUT        MESSAGE_PROPERTIES_T,
PAYLOAD              OUT        "<ADT_1>",
MSGID                OUT        RAW) IS
EXTERNAL
NAME "kwqiadtdequeue"
WITH CONTEXT
PARAMETERS(CONTEXT,
QUEUE_NAME      OCISTRING,        QUEUE_NAME        INDICATOR SB4,
DEQUEUE_OPTIONS,
DEQUEUE_OPTIONS INDICATOR STRUCT,
MESSAGE_PROPERTIES,
MESSAGE_PROPERTIES INDICATOR STRUCT,
MESSAGE_PROPERTIES DURATION OCIDURATION,
PAYLOAD, PAYLOAD INDICATOR STRUCT,
PAYLOAD TDO OCITYPE, PAYLOAD DURATION OCIDURATION,
MSGID   OCIRAW)
LIBRARY DBMS_AQ_LIB;
PROCEDURE AQ$_DEQUEUE(
QUEUE_NAME              IN      VARCHAR2,
SUBSCRIBER              IN      VARCHAR2        DEFAULT NULL,
MSGID                   IN      RAW             DEFAULT NULL,
CORRELATION             IN      VARCHAR2        DEFAULT NULL,
DEQUEUE_MODE            IN      BINARY_INTEGER  DEFAULT REMOVE,
NAVIGATION              IN      BINARY_INTEGER  DEFAULT FIRST,
VISIBILITY              IN      BINARY_INTEGER  DEFAULT ON_COMMIT,
WAIT                    IN      BINARY_INTEGER  DEFAULT FOREVER,
ENQUEUE_TIME            OUT     DATE,
STATE                   OUT     BINARY_INTEGER,
OUT_MSGID               OUT     RAW,
OUT_CORRELATION         OUT     VARCHAR2,
PRIORITY                OUT     BINARY_INTEGER,
DELAY                   OUT     BINARY_INTEGER,
EXPIRATION              OUT     BINARY_INTEGER,
ATTEMPTS                OUT     BINARY_INTEGER,
EXCEPTION_QUEUE         OUT     VARCHAR2,
REMOTE_RECIPIENTS       OUT     AQ$_RECIPIENT_LIST_T,
SENDER_NAME             OUT     VARCHAR2,
SENDER_ADDR             OUT     VARCHAR2,
SENDER_PROTOCOL         OUT     BINARY_INTEGER,
ORIGINAL_MSGID          OUT     RAW,
PAYLOAD_TYPE            IN      BINARY_INTEGER  DEFAULT OBJECT_TYPE,
RAW_USER_DATA           OUT     RAW,
OBJECT_USER_DATA        OUT     "<ADT_1>",
DEQ_CONDITION           IN      VARCHAR2        DEFAULT NULL,
SIGNATURE               IN      SYS.AQ$_SIG_PROP  DEFAULT NULL,
OUT_SIGN                OUT     SYS.AQ$_SIG_PROP,
TRANSFORMATION          IN      VARCHAR2          DEFAULT NULL,
BUFMESG_PROP            IN      BINARY_INTEGER    DEFAULT NULL,
LCR2XML_CONVERT         IN      BOOLEAN           DEFAULT FALSE,
USER_PROPERTY           OUT     SYS.ANYDATA,
DELIVERY_MODE           IN      PLS_INTEGER       DEFAULT PERSISTENT,
OUT_DELIVERY_MODE       OUT     PLS_INTEGER
)
IS
EXTERNAL
NAME "kwqidxdequeue102"
WITH CONTEXT
PARAMETERS(CONTEXT,
QUEUE_NAME      OCISTRING,        QUEUE_NAME        INDICATOR SB4,
SUBSCRIBER      OCISTRING,        SUBSCRIBER        INDICATOR SB4,
MSGID           OCIRAW,           MSGID             INDICATOR SB4,
CORRELATION     OCISTRING,        CORRELATION       INDICATOR SB4,
DEQUEUE_MODE    SB4,
WAIT            SB4,              WAIT              INDICATOR SB4,
VISIBILITY      SB4,              VISIBILITY        INDICATOR SB4,
NAVIGATION      SB4,
OUT_MSGID       OCIRAW,
OUT_CORRELATION OCISTRING,        OUT_CORRELATION   INDICATOR SB4,
PRIORITY        SB4,              PRIORITY          INDICATOR SB4,
DELAY           SB4,              DELAY             INDICATOR SB4,
EXPIRATION      SB4,              EXPIRATION        INDICATOR SB4,
STATE           SB4,              STATE             INDICATOR SB4,
ENQUEUE_TIME    OCIDATE,          ENQUEUE_TIME      INDICATOR SB4,
ATTEMPTS        SB4,              ATTEMPTS          INDICATOR SB4,
EXCEPTION_QUEUE OCISTRING,           EXCEPTION_QUEUE   INDICATOR SB4,
REMOTE_RECIPIENTS         OCICOLL,
REMOTE_RECIPIENTS         INDICATOR SB2,
SENDER_NAME     OCISTRING,        SENDER_NAME       INDICATOR SB4,
SENDER_ADDR     OCISTRING,           SENDER_ADDR       INDICATOR SB4,
SENDER_PROTOCOL SB4,                 SENDER_PROTOCOL   INDICATOR SB4,
ORIGINAL_MSGID  OCIRAW,           ORIGINAL_MSGID    INDICATOR SB4,
PAYLOAD_TYPE    SB4,
RAW_USER_DATA   OCIRAW,           RAW_USER_DATA     INDICATOR SB4,
RAW_USER_DATA   MAXLEN    SB4,
OBJECT_USER_DATA,
OBJECT_USER_DATA INDICATOR STRUCT,
OBJECT_USER_DATA TDO OCITYPE,
OBJECT_USER_DATA DURATION OCIDURATION,
DEQ_CONDITION   OCISTRING,        DEQ_CONDITION     INDICATOR SB4,
SIGNATURE,
SIGNATURE INDICATOR STRUCT,
SIGNATURE TDO OCITYPE,
OUT_SIGN,
OUT_SIGN INDICATOR STRUCT,
OUT_SIGN TDO OCITYPE,
OUT_SIGN DURATION OCIDURATION,
TRANSFORMATION  OCISTRING,
TRANSFORMATION  INDICATOR SB4,
BUFMESG_PROP         SB4,
BUFMESG_PROP         INDICATOR SB4,
LCR2XML_CONVERT      SB4,
LCR2XML_CONVERT      INDICATOR SB4,
USER_PROPERTY,
USER_PROPERTY        INDICATOR SB4,
USER_PROPERTY        DURATION OCIDURATION,
DELIVERY_MODE        SB4,
OUT_DELIVERY_MODE    SB4
)
LIBRARY DBMS_AQ_LIB;
PROCEDURE AQ$_DEQUEUE(
QUEUE_NAME              IN      VARCHAR2,
SUBSCRIBER              IN      VARCHAR2        DEFAULT NULL,
MSGID                   IN      RAW             DEFAULT NULL,
CORRELATION             IN      VARCHAR2        DEFAULT NULL,
DEQUEUE_MODE            IN      BINARY_INTEGER  DEFAULT REMOVE,
NAVIGATION              IN      BINARY_INTEGER  DEFAULT FIRST,
VISIBILITY              IN      BINARY_INTEGER  DEFAULT ON_COMMIT,
WAIT                    IN      BINARY_INTEGER  DEFAULT FOREVER,
ENQUEUE_TIME            OUT     DATE,
STATE                   OUT     BINARY_INTEGER,
OUT_MSGID               OUT     RAW,
OUT_CORRELATION         OUT     VARCHAR2,
PRIORITY                OUT     BINARY_INTEGER,
DELAY                   OUT     BINARY_INTEGER,
EXPIRATION              OUT     BINARY_INTEGER,
ATTEMPTS                OUT     BINARY_INTEGER,
EXCEPTION_QUEUE         OUT     VARCHAR2,
REMOTE_RECIPIENTS       OUT     AQ$_RECIPIENT_LIST_T,
SENDER_NAME             OUT     VARCHAR2,
SENDER_ADDR             OUT     VARCHAR2,
SENDER_PROTOCOL         OUT     BINARY_INTEGER,
ORIGINAL_MSGID          OUT     RAW,
PAYLOAD_TYPE            IN      BINARY_INTEGER  DEFAULT OBJECT_TYPE,
RAW_USER_DATA           OUT     RAW,
OBJECT_USER_DATA        OUT     "<ADT_1>",
DEQ_CONDITION           IN      VARCHAR2        DEFAULT NULL,
SIGNATURE               IN      SYS.AQ$_SIG_PROP  DEFAULT NULL,
OUT_SIGN                OUT     SYS.AQ$_SIG_PROP,
TRANSFORMATION          IN      VARCHAR2         DEFAULT NULL,
BUFMESG_PROP            IN      BINARY_INTEGER   DEFAULT NULL,
LCR2XML_CONVERT         IN      BOOLEAN          DEFAULT FALSE)
IS
EXTERNAL
NAME "kwqidxdequeue92"
WITH CONTEXT
PARAMETERS(CONTEXT,
QUEUE_NAME      OCISTRING,        QUEUE_NAME        INDICATOR SB4,
SUBSCRIBER      OCISTRING,        SUBSCRIBER        INDICATOR SB4,
MSGID           OCIRAW,           MSGID             INDICATOR SB4,
CORRELATION     OCISTRING,        CORRELATION       INDICATOR SB4,
DEQUEUE_MODE    SB4,
WAIT            SB4,              WAIT              INDICATOR SB4,
VISIBILITY      SB4,              VISIBILITY        INDICATOR SB4,
NAVIGATION      SB4,
OUT_MSGID       OCIRAW,
OUT_CORRELATION OCISTRING,        OUT_CORRELATION   INDICATOR SB4,
PRIORITY        SB4,              PRIORITY          INDICATOR SB4,
DELAY           SB4,              DELAY             INDICATOR SB4,
EXPIRATION      SB4,              EXPIRATION        INDICATOR SB4,
STATE           SB4,              STATE             INDICATOR SB4,
ENQUEUE_TIME    OCIDATE,          ENQUEUE_TIME      INDICATOR SB4,
ATTEMPTS        SB4,              ATTEMPTS          INDICATOR SB4,
EXCEPTION_QUEUE OCISTRING,           EXCEPTION_QUEUE   INDICATOR SB4,
REMOTE_RECIPIENTS         OCICOLL,
REMOTE_RECIPIENTS         INDICATOR SB2,
SENDER_NAME     OCISTRING,        SENDER_NAME       INDICATOR SB4,
SENDER_ADDR     OCISTRING,           SENDER_ADDR       INDICATOR SB4,
SENDER_PROTOCOL SB4,                 SENDER_PROTOCOL   INDICATOR SB4,
ORIGINAL_MSGID  OCIRAW,           ORIGINAL_MSGID    INDICATOR SB4,
PAYLOAD_TYPE    SB4,
RAW_USER_DATA   OCIRAW,           RAW_USER_DATA     INDICATOR SB4,
RAW_USER_DATA   MAXLEN    SB4,
OBJECT_USER_DATA,
OBJECT_USER_DATA INDICATOR STRUCT,
OBJECT_USER_DATA TDO OCITYPE,
OBJECT_USER_DATA DURATION OCIDURATION,
DEQ_CONDITION   OCISTRING,        DEQ_CONDITION     INDICATOR SB4,
SIGNATURE,
SIGNATURE INDICATOR STRUCT,
SIGNATURE TDO OCITYPE,
OUT_SIGN,
OUT_SIGN INDICATOR STRUCT,
OUT_SIGN TDO OCITYPE,
OUT_SIGN DURATION OCIDURATION,
TRANSFORMATION  OCISTRING,
TRANSFORMATION  INDICATOR SB4,
BUFMESG_PROP         SB4,
BUFMESG_PROP         INDICATOR SB4,
LCR2XML_CONVERT      SB4,
LCR2XML_CONVERT      INDICATOR SB4
)
LIBRARY DBMS_AQ_LIB;
PROCEDURE DEQUEUE(
QUEUE_NAME           IN         VARCHAR2,
DEQUEUE_OPTIONS      IN         DEQUEUE_OPTIONS_T,
MESSAGE_PROPERTIES   OUT        MESSAGE_PROPERTIES_T,
PAYLOAD              OUT        RAW,
MSGID                OUT        RAW) IS
DUMMY_BUFFER   SYS.AQ$_DUMMY_T;
SENDER_NAME    VARCHAR2(30);
SENDER_ADDRESS VARCHAR2(1024);
SENDER_PROTOCOL NUMBER;
SENDER_ID      SYS.AQ$_AGENT;
BEGIN
SENDER_ID := SYS.AQ$_AGENT('name', 'address', 0);
AQ$_DEQUEUE(
QUEUE_NAME  => QUEUE_NAME,
SUBSCRIBER => DEQUEUE_OPTIONS.CONSUMER_NAME,
MSGID  => DEQUEUE_OPTIONS.MSGID,
CORRELATION => DEQUEUE_OPTIONS.CORRELATION,
DEQ_CONDITION => DEQUEUE_OPTIONS.DEQ_CONDITION,
DEQUEUE_MODE => DEQUEUE_OPTIONS.DEQUEUE_MODE,
NAVIGATION => DEQUEUE_OPTIONS.NAVIGATION,
WAIT => DEQUEUE_OPTIONS.WAIT,
VISIBILITY => DEQUEUE_OPTIONS.VISIBILITY,
OUT_MSGID => MSGID,
OUT_CORRELATION => MESSAGE_PROPERTIES.CORRELATION,
ENQUEUE_TIME => MESSAGE_PROPERTIES.ENQUEUE_TIME,
STATE =>  MESSAGE_PROPERTIES.STATE,
PRIORITY => MESSAGE_PROPERTIES.PRIORITY,
DELAY => MESSAGE_PROPERTIES.DELAY,
EXPIRATION => MESSAGE_PROPERTIES.EXPIRATION,
ATTEMPTS => MESSAGE_PROPERTIES.ATTEMPTS,
EXCEPTION_QUEUE => MESSAGE_PROPERTIES.EXCEPTION_QUEUE,
TRANSFORMATION => DEQUEUE_OPTIONS.TRANSFORMATION,
REMOTE_RECIPIENTS => MESSAGE_PROPERTIES.RECIPIENT_LIST,
SENDER_NAME => SENDER_NAME,
SENDER_ADDR => SENDER_ADDRESS,
SENDER_PROTOCOL => SENDER_PROTOCOL,
ORIGINAL_MSGID => MESSAGE_PROPERTIES.ORIGINAL_MSGID,
PAYLOAD_TYPE => RAW_TYPE,
RAW_USER_DATA => PAYLOAD,
OBJECT_USER_DATA => DUMMY_BUFFER,
SIGNATURE => DEQUEUE_OPTIONS.SIGNATURE,
OUT_SIGN => MESSAGE_PROPERTIES.SIGNATURE,
BUFMESG_PROP => DEQUEUE_OPTIONS.BUFMESG_PROP,
USER_PROPERTY => MESSAGE_PROPERTIES.USER_PROPERTY,
DELIVERY_MODE => DEQUEUE_OPTIONS.DELIVERY_MODE,
OUT_DELIVERY_MODE => MESSAGE_PROPERTIES.DELIVERY_MODE);
IF (SENDER_NAME IS NULL AND SENDER_ADDRESS IS NULL AND
SENDER_PROTOCOL IS NULL) THEN
MESSAGE_PROPERTIES.SENDER_ID := NULL;
ELSE
IF SENDER_NAME IS NOT NULL THEN
SENDER_ID.NAME := SENDER_NAME;
ELSE
SENDER_ID.NAME := NULL;
END IF;
IF SENDER_ADDRESS IS NOT NULL THEN
SENDER_ID.ADDRESS := SENDER_ADDRESS;
ELSE
SENDER_ID.ADDRESS := NULL;
END IF;
IF SENDER_PROTOCOL IS NOT NULL THEN
SENDER_ID.PROTOCOL := SENDER_PROTOCOL;
ELSE
SENDER_ID.PROTOCOL := NULL;
END IF;
MESSAGE_PROPERTIES.SENDER_ID := SENDER_ID;
END IF;
END;
FUNCTION DEQUEUE_ARRAY(
QUEUE_NAME                IN    VARCHAR2,
DEQUEUE_OPTIONS           IN    DEQUEUE_OPTIONS_T,
ARRAY_SIZE                IN    PLS_INTEGER,
MESSAGE_PROPERTIES_ARRAY  OUT   MESSAGE_PROPERTIES_ARRAY_T,
PAYLOAD_ARRAY             OUT   "<COLLECTION_1>",
MSGID_ARRAY               OUT   MSGID_ARRAY_T) RETURN PLS_INTEGER IS
EXTERNAL
NAME "kwqideqarray0"
WITH CONTEXT
PARAMETERS(CONTEXT,
QUEUE_NAME      OCISTRING,        QUEUE_NAME        INDICATOR SB4,
DEQUEUE_OPTIONS,
DEQUEUE_OPTIONS INDICATOR STRUCT,
ARRAY_SIZE UB4,
MESSAGE_PROPERTIES_ARRAY,
MESSAGE_PROPERTIES_ARRAY INDICATOR,
MESSAGE_PROPERTIES_ARRAY TDO OCITYPE,
MESSAGE_PROPERTIES_ARRAY DURATION OCIDURATION,
PAYLOAD_ARRAY, PAYLOAD_ARRAY INDICATOR SB2,
PAYLOAD_ARRAY TDO OCITYPE, PAYLOAD_ARRAY DURATION OCIDURATION,
MSGID_ARRAY,MSGID_ARRAY INDICATOR, MSGID_ARRAY DURATION OCIDURATION,
RETURN UB4)
LIBRARY DBMS_AQ_LIB;
FUNCTION DEQUEUE_ARRAY(
QUEUE_NAME                IN    VARCHAR2,
DEQUEUE_OPTIONS           IN    DEQUEUE_OPTIONS_T,
ARRAY_SIZE                IN    PLS_INTEGER,
MESSAGE_PROPERTIES_ARRAY  OUT   MESSAGE_PROPERTIES_ARRAY_T,
PAYLOAD_ARRAY             OUT   "<COLLECTION_1>",
MSGID_ARRAY               OUT   MSGID_ARRAY_T,
ERROR_ARRAY               OUT   ERROR_ARRAY_T) RETURN PLS_INTEGER IS
EXTERNAL
NAME "kwqideqarray"
WITH CONTEXT
PARAMETERS(CONTEXT,
QUEUE_NAME      OCISTRING,        QUEUE_NAME        INDICATOR SB4,
DEQUEUE_OPTIONS,
DEQUEUE_OPTIONS INDICATOR STRUCT,
ARRAY_SIZE UB4,
MESSAGE_PROPERTIES_ARRAY,
MESSAGE_PROPERTIES_ARRAY INDICATOR,
MESSAGE_PROPERTIES_ARRAY TDO OCITYPE,
MESSAGE_PROPERTIES_ARRAY DURATION OCIDURATION,
PAYLOAD_ARRAY, PAYLOAD_ARRAY INDICATOR SB2,
PAYLOAD_ARRAY TDO OCITYPE, PAYLOAD_ARRAY DURATION OCIDURATION,
MSGID_ARRAY,MSGID_ARRAY INDICATOR, MSGID_ARRAY DURATION OCIDURATION,
ERROR_ARRAY,ERROR_ARRAY INDICATOR SB2,
ERROR_ARRAY DURATION OCIDURATION,
RETURN UB4)
LIBRARY DBMS_AQ_LIB;
PROCEDURE LISTEN(
AGENT_LIST            IN    AQ$_AGENT_LIST_T,
WAIT                  IN    BINARY_INTEGER DEFAULT FOREVER,
LISTEN_DELIVERY_MODE  IN    PLS_INTEGER DEFAULT PERSISTENT,
AGENT                 OUT   SYS.AQ$_AGENT,
MESSAGE_DELIVERY_MODE OUT   PLS_INTEGER) IS
EXTERNAL
NAME "kwqilisten"
WITH CONTEXT
PARAMETERS(CONTEXT,
AGENT_LIST OCICOLL,
WAIT                   SB4,  WAIT                  INDICATOR SB4,
LISTEN_DELIVERY_MODE   SB4,
AGENT,                       AGENT                 INDICATOR STRUCT,
MESSAGE_DELIVERY_MODE  SB4)
LIBRARY DBMS_AQ_LIB;
PROCEDURE LISTEN(
AGENT_LIST           IN     AQ$_AGENT_LIST_T,
WAIT                 IN     BINARY_INTEGER DEFAULT FOREVER,
AGENT                OUT    SYS.AQ$_AGENT) IS
DELIV_MODE     PLS_INTEGER;
BEGIN
LISTEN(AGENT_LIST             => AGENT_LIST,
WAIT                   => WAIT,
LISTEN_DELIVERY_MODE   => PERSISTENT,
AGENT                  => AGENT,
MESSAGE_DELIVERY_MODE  => DELIV_MODE);
END LISTEN;
PROCEDURE BIND_AGENT(
AGENT                IN    AQ$_AGENT,
CERTIFICATE_LOCATION IN    VARCHAR2 DEFAULT NULL) IS
BEGIN
DBMS_AQADM_SYS.I_BIND_AGENT(AGENT => AGENT,
CERTIFICATE_LOCATION => CERTIFICATE_LOCATION);
END BIND_AGENT;
PROCEDURE UNBIND_AGENT(
AGENT                IN   AQ$_AGENT) IS
BEGIN
DBMS_AQADM_SYS.I_UNBIND_AGENT(AGENT => AGENT);
END UNBIND_AGENT;
PROCEDURE REGISTER(
REG_LIST      IN       SYS.AQ$_REG_INFO_LIST,
REG_COUNT     IN       NUMBER) IS
EXTERNAL
NAME "kwqiplsregister"
WITH CONTEXT
PARAMETERS(CONTEXT,
REG_LIST    OCICOLL,
REG_COUNT   OCINUMBER)
LIBRARY DBMS_AQ_LIB;
PROCEDURE UNREGISTER(
REG_LIST      IN       SYS.AQ$_REG_INFO_LIST,
REG_COUNT     IN       NUMBER) IS
EXTERNAL
NAME "kwqiplsunregister"
WITH CONTEXT
PARAMETERS(CONTEXT,
REG_LIST    OCICOLL,
REG_COUNT   OCINUMBER)
LIBRARY DBMS_AQ_LIB;
PROCEDURE POST(
POST_LIST     IN       SYS.AQ$_POST_INFO_LIST,
POST_COUNT    IN       NUMBER) IS
EXTERNAL
NAME "kwqiplspost"
WITH CONTEXT
PARAMETERS(CONTEXT,
POST_LIST    OCICOLL,
POST_COUNT   OCINUMBER)
LIBRARY DBMS_AQ_LIB;

END;

PL/SQL procedure successfully completed.

SQL>

这真不错,一定要改成java的



外部链接:
ZLIB Compressed Data Format Specification version 3.3
GZIP file format specification version 4.3
Multipurpose Internet Mail Extensions - (MIME) Part One: - Format of Internet Message Bodies
Adler-32
Cyclic redundancy check


加密方法见:how to protect your plsql code



-fin-

how to protect your plsql code 保护存储过程

how to protect your plsql code
如何保护你的存储过程


概述:

两种方法:
a.使用包PACKAGE封装(encapsulate), 让用户拥有执行权限，但看不到SQL代码
如果想让用户能查看代码，不能执行，请参考Procedure vs Package内的方法

they would have to
a) be granted access to DBA_SOURCE which doesn't have any filters on what can be seen
b) have create/debug ANY procedure (very bad idea, avoid the ANY privileges)
c) have a view created for them against DBA_SOURCE that provided this access
d) user B provide them a stored procedure that shows this information.
here is a demonstration of (d)
...

b.用WRAP命令行程序或DBMS_DDL.WRAP打包,
这并不安全，可以被解包，获得源码，参考后面的资料和unwrapping plsql code in 10g



正文:

1. 创建PACKAGE封装SQL代码
用户即使拥有包的可执行权限, 也不能看到包体的SQL源码

用户A建一个包, 授权可执行权限给用户B

conn a/a
create or replace package pkg1
is
  procedure hello;
end;
/
create or replace package body pkg1
is
  procedure hello
  is
  begin
    dbms_output.put_line('Hello World!');
  end;
end;
/
grant execute on pkg1 to b;

用户B下能看到包的定义, 看不到包体中的SQL源码, 但可以运行

conn b/b
set pages 50000 line 120
set serveroutput on size unlimited
select text from all_source where owner='A' and name='PKG1' order by line;
exec a.pkg1.hello
desc a.pkg1

SQL> select text from all_source where owner='A' and name='PKG1' order by line;

TEXT
------------------------------------------------------------------------------------------------------------------------
package pkg1
is
  procedure hello;
end;

SQL> exec a.pkg1.hello
Hello World!

PL/SQL procedure successfully completed.

SQL> desc a.pkg1
PROCEDURE HELLO

SQL>

如果是使用单独的存储过程或函数, 则无法隐藏代码

conn a/a
create or replace procedure prc1
is
begin
  dbms_output.put_line('it''s prc1');
end;
/
grant execute on prc1 to b;
create or replace function fun1
  return varchar2
is
begin
  return 'it''s fun1';
end;
/
grant execute on fun1 to b;
conn b/b
set pages 50000 line 120
set serveroutput on size unlimited
desc a.prc1
select text from all_source where owner='A' and name='PRC1' order by line;
exec a.prc1
desc a.fun1
select text from all_source where owner='A' and name='FUN1' order by line;
select a.fun1 from dual;

SQL> exec a.prc1
SQLv select text from all_source where owner='A' and name='PRC1' order by line;

TEXT
------------------------------------------------------------------------------------------------------------------------
procedure prc1
is
begin
  dbms_output.put_line('it''s prc1');
end;

SQL> select text from all_source where owner='A' and name='FUN1' order by line;

TEXT
------------------------------------------------------------------------------------------------------------------------
function fun1
  return varchar2
is
begin
  return 'it''s fun1';
end;

6 rows selected.

SQL>

因此建议使用包, 还有更多好处尽在: Procedure vs Package

because packages

o break the dependency chain (no cascading invalidations when you install a new package body -- if you have procedures that call procedures -- compiling one will invalidate your database)
o support encapsulation -- I will be allowed to write MODULAR, easy to understand code -- rather then MONOLITHIC, non-understandable procedures
o increase my namespace measurably.  package names have to be unique in a schema, but I can have many procedures across packages with the same name without colliding
o support overloading
o support session variables when you need them
o promote overall good coding techniques, stuff that lets you write code that is modular, 
understandable, logically grouped together....

If you are a programmer - you would see the benefits of packages over a proliferation of standalone procedures in a heartbeat. 

2. 用WRAP程序打包加密

用法:
wrap iname=input_file [oname=output_file]

比如加密一个包:

set pages 50000 line 130
set serveroutput on size unlimited
create or replace package pkg1
is
  procedure hello;
  function hello2(n in varchar2) return varchar2;
end;
/
save pkg1.sql replace
create or replace package body pkg1
is
  procedure hello
  is
  begin
    dbms_output.put_line('Hello World!');
  end;
  function hello2(n in varchar2) return varchar2
  is
  begin
    dbms_output.put_line('Hello, '||n||'!');
  end;
end;
/
save pkg1.sql append
!cat pkg1.sql
!wrap iname=pkg1.sql
!cat pkg1.plb
@pkg1.plb
select text from user_source where name='PKG1' and type='PACKAGE' order by line;
select text from user_source where name='PKG1' and type='PACKAGE BODY' order by line;
desc pkg1

SQL> create or replace package pkg1
is
  procedure hello;
  function hello2(n in varchar2) return varchar2;
end;
/
  2    3    4    5    6
Package created.

SQL> save pkg1.sql replace
Wrote file pkg1.sql
SQL> create or replace package body pkg1
is
  procedure hello
  is
  begin
    dbms_output.put_line('Hello World!');
  end;
  function hello2(n in varchar2) return varchar2
  is
  begin
    dbms_output.put_line('Hello, '||n||'!');
  end;
end;
/
  2    3    4    5    6    7    8    9   10   11   12   13   14
Package body created.

SQL> save pkg1.sql append
Appended file to pkg1.sql
SQL> !cat pkg1.sql
create or replace package pkg1
is
  procedure hello;
  function hello2(n in varchar2) return varchar2;
end;
/
create or replace package body pkg1
is
  procedure hello
  is
  begin
    dbms_output.put_line('Hello World!');
  end;
  function hello2(n in varchar2) return varchar2
  is
  begin
    dbms_output.put_line('Hello, '||n||'!');
  end;
end;
/

SQL> !wrap iname=pkg1.sql

PL/SQL Wrapper: Release 10.2.0.4.0- 64bit Production on Wed Apr 01 06:39:23 2009

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

Processing pkg1.sql to pkg1.plb

SQL> !cat pkg1.plb
create or replace package pkg1 wrapped
a000000
369
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
9
5a 8d
8JaRjQCpdPKaOi05fKryJOQXYfgwg5m49TOf9b9cFoXceF2bdIsJaefnm7+fMr2ywFyluIEI
CMQ5nr4Ckq0HMJL4NaJfIeSOenOOFXbWbgB21l8hlKxxhObWL+E8ceI/0Tx0pquab+0=

/
create or replace package body pkg1 wrapped
a000000
369
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
dc e3
5AxGCR+k0mFGtl3AL13kbu0X6Ccwg43wf5kVfI5GkPjVQV6jL6/i3N6bfqwIbBr0BnfpUnxp
Uv/QeD016geY1tCG/1ItwQDmF0OIHP4F6CWERI80lBaMgGkRX2Jd8NqKKo02yFU4BHl00UAy
sxEhONd3KmO93OuCTKfYMacb8W4LH6E2eSmCsTw27I+F7WloxLIwdLOqrw+/1XCtXv+K/lJh
lZmIFHgt

/

SQL> @pkg1.plb

Package created.


Package body created.

SQL> select text from user_source where name='PKG1' and type='PACKAGE' order by line;

TEXT
----------------------------------------------------------------------------------------------------------------------------------
package pkg1 wrapped
a000000
369
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
9
5a 8d
8JaRjQCpdPKaOi05fKryJOQXYfgwg5m49TOf9b9cFoXceF2bdIsJaefnm7+fMr2ywFyluIEI
CMQ5nr4Ckq0HMJL4NaJfIeSOenOOFXbWbgB21l8hlKxxhObWL+E8ceI/0Tx0pquab+0=


SQL> select text from user_source where name='PKG1' and type='PACKAGE BODY' order by line;

TEXT
----------------------------------------------------------------------------------------------------------------------------------
package body pkg1 wrapped
a000000
369
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
dc e3
5AxGCR+k0mFGtl3AL13kbu0X6Ccwg43wf5kVfI5GkPjVQV6jL6/i3N6bfqwIbBr0BnfpUnxp
Uv/QeD016geY1tCG/1ItwQDmF0OIHP4F6CWERI80lBaMgGkRX2Jd8NqKKo02yFU4BHl00UAy
sxEhONd3KmO93OuCTKfYMacb8W4LH6E2eSmCsTw27I+F7WloxLIwdLOqrw+/1XCtXv+K/lJh
lZmIFHgt


SQL> desc pkg1
PROCEDURE HELLO
FUNCTION HELLO2 RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 N                              VARCHAR2                IN

SQL>

包定义和包体都被加密了, 加密后的代码第一行存储过程名以wrapped结尾.
通常, 包定义不需要加密, 应该暴露给使用者

有时wrap不支持SQL语法的新写法, wrap命令后再加上edebug=wrap_new_sql参数即可, 见How to Wrap New SQL Using Wrap Utility


外部链接:
Obfuscating PL/SQL Source Code>
Wrapping PL/SQL Source Code
The WRAP Utility and the DBMS_DDL Package
Protecting PL/SQL Code
FAQ: Wrapping PL/SQL Source Code
Wrapping PL/SQL Code Primer - Oracle Database 11g PL/SQL Programming By Michael McLaughlin


3. 用DBMS_DDL.WRAP动态加密(Dynamic wrapping)
10gR2提供了DBMS_DDL.WRAP和DBMS_DDL.CREATE_WRAPPED为存储过程加密
WRAP返回加密后的代码, CREATE_WRAPPED加密代码并创建

WRAP用法:

DBMS_DDL.WRAP(
   ddl      VARCHAR2) 
  RETURN VARCHAR2;
DBMS_DDL.WRAP(
   ddl      DBMS_SQL.VARCHAR2S, 
   lb       PLS_INTEGER, 
   ub       PLS_INTEGER) 
  RETURN DBMS_SQL.VARCHAR2S;
DBMS_DDL.WRAP(
   ddl      DBMS_SQL.VARCHAR2A, 
   lb       PLS_INTEGER, 
   ub       PLS_INTEGER) 
  RETURN DBMS_SQL.VARCHAR2A;

VARCHAR2大小限制是32767字节, DBMS_SQL.VARCHAR2S是TABLE OF VARCHAR2(256), DBMS_SQL.VARCHAR2A是TABLE OF VARCHAR2(32767)
也就是说后两个函数可以处理SQL过程大于32k的情况, lb,ub分别是字符串数组的上下标

如:

select dbms_ddl.wrap(q'{create or replace procedure -
prc1 is begin dbms_output.put_line('it''s prc1'); end;}')
 from dual;

SQL> select dbms_ddl.wrap(q'{create or replace procedure -
prc1 is begin dbms_output.put_line('it''s prc1'); end;}')
 from dual;
>   2
DBMS_DDL.WRAP(Q'{CREATEORREPLACEPROCEDUREPRC1ISBEGINDBMS_OUTPUT.PUT_LINE('IT''SPRC1');END;}')
------------------------------------------------------------------------------------------------------------------------
create or replace procedure  prc1 wrapped
a000000
369
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
41 79
tCj/ToEfSjDvOgaZlsfca7W1grUwg5nnm7+fMr2ywFwWFvLfw6V0K7jAMv7SXrhSm7JK/iiy
veeysx0GMCyuJOqygUfTMoHgS/IyLvZE6iQf9jmmyeHTRA==


SQL>

参数是字符串数组, 举例:

set serveroutput on size unlimited
declare
  plain_sql   dbms_sql.varchar2a;
  wrapped_sql dbms_sql.varchar2a;
begin
  plain_sql(1) := 'create or replace procedure prc1 ';
  plain_sql(2) := 'is ';
  plain_sql(3) := 'begin ';
  plain_sql(4) := 'dbms_output.put_line(''it''''s prc1''); ';
  plain_sql(5) := 'end;';
  wrapped_sql := dbms_ddl.wrap(plain_sql,1,plain_sql.count);
  for i in 1..wrapped_sql.count loop
    dbms_output.put_line(wrapped_sql(i));
  end loop;
end;
/

SQL> declare
  2    plain_sql   dbms_sql.varchar2a;
  3    wrapped_sql dbms_sql.varchar2a;
  4  begin
  5    plain_sql(1) := 'create or replace procedure prc1 ';
  6    plain_sql(2) := 'is ';
  7    plain_sql(3) := 'begin ';
  8    plain_sql(4) := 'dbms_output.put_line(''it''''s prc1''); ';
  9    plain_sql(5) := 'end;';
 10    wrapped_sql := dbms_ddl.wrap(plain_sql,1,plain_sql.count);
 11    for i in 1..wrapped_sql.count loop
 12      dbms_output.put_line(wrapped_sql(i));
 13    end loop;
 14  end;
 15  /
create or replace procedure prc1 wrapped

a000000
369
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
41
79
tCj/ToEfSjDvOgaZlsfca7W1grUwg5nnm7+fMr2ywFwWFvLfw6V0K7jAMv7SXrhSm7JK/iiy
veeysx0GMCyuJOqygUfTMoHgS/IyLvZE6iQf9jmmyeHTRA==



PL/SQL procedure successfully completed.

SQL>

注意数组每行最后要加上空格或回车符


CREATE_WRAPPED举例:

drop procedure prc1;
exec dbms_ddl.create_wrapped(q'{create or replace procedure prc1 is begin dbms_output.put_line('it''s prc1'); end;}')
desc prc1

SQL> drop procedure prc1;

Procedure dropped.

SQL> exec dbms_ddl.create_wrapped(q'{create or replace procedure prc1 is begin dbms_output.put_line('it''s prc1'); end;}')

PL/SQL procedure successfully completed.

SQL> desc prc1
PROCEDURE prc1

SQL>

外部链接:
Dynamic Wrap
DBMS_DDL
DBMS_DDL



4. 解包unwrap

列出一些资料, 以后再说
How to unwrap PL/SQL
Pete Finnigan Podcast about PL/SQL wrapping
2006_BlackHat_Vegas-V8-Finnigan-How_to_Unwrap_Oracle

Unwrapping 10G wrapped PL/SQL by Anton Scheffer
Wrapped PL/SQL - The Oracle Hacker's Handbook By David Litchfield
AUTOMATIC DETECTION OF VULNERABILITIES IN WRAPPED PACKAGES IN ORACLE

Oracle PL/SQL REWRAP Unwrap
UnwrapOracle




-fin-

prevent users using exp or imp 禁止用户使用导出和导入

how to prevent users using exp or imp

提问: 何如禁止用户使用exp或imp
回答: 可以, 但禁止了也不能保证数据安全


1.
参考文档
$ORACLE_HOME/rdbms/admin/catexp.sql
和
Default Privileges Assigned To PUBLIC In 10.2.0.4 Database

只要从PUBLIC回收一些视图的权限, 便可以禁止用户使用exp或imp


2.
exp加trace=y, 跟踪一下

exp a/a file=a.dmp rows=n tables=\(no_such_table\) log=a.exp.log trace=y

[oracle@DEV-RPT-2 ~]$ exp a/a file=a.dmp rows=n tables=\(no_such_table\) log=a.exp.log trace=y

Export: Release 10.2.0.4.0 - Production on Tue Mar 24 05:51:18 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...
EXP-00011: A.NO_SUCH_TABLE does not exist
Export terminated successfully with warnings.
[oracle@DEV-RPT-2 ~]$

查看跟踪文件, 查看exp查询的EXU,IMP视图等

sed -n '
/EXU/Is/.*[^a-zA-Z0-9_$]\(.*EXU[a-zA-Z0-9_$]*\).*/\1/Ip
/IMP/Is/.*[^a-zA-Z0-9_$]\(.*IMP[a-zA-Z0-9_$]*\).*/\1/Ip
' $(ls -t $ORACLE_BASE/admin/$ORACLE_SID/udump/*.trc|head -1)

[oracle@DEV-RPT-2 ~]$ sed -n '
> /EXU/Is/.*[^a-zA-Z0-9_$]\(.*EXU[a-zA-Z0-9_$]*\).*/\1/Ip
> /IMP/Is/.*[^a-zA-Z0-9_$]\(.*IMP[a-zA-Z0-9_$]*\).*/\1/Ip
> ' $(ls -t $ORACLE_BASE/admin/$ORACLE_SID/udump/*.trc|head -1)
EXU8OPT
EXU8USRU
EXU8FUL
EXU8VER
EXU81JAVT
EXU9XDBUID
EXU81CSC
EXU9GSAS
EXU9NLS
EXU10ADEFPSWITCHES
EXU10R2DEFPSWITCHES
EXU8NXPU
EXU10TABS
EXU8NXPU
[oracle@DEV-RPT-2 ~]$

3. 跟踪imp

imp a/a file=a.dmp show=y log=a.imp.log trace=y

[oracle@DEV-RPT-2 ~]$ imp a/a file=a.dmp show=y log=a.imp.log trace=y

Import: Release 10.2.0.4.0 - Production on Tue Mar 24 05:53:33 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


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

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing A's objects into A
Import terminated successfully without warnings.
[oracle@DEV-RPT-2 ~]$

查看跟踪文件

sed -n '
/EXU/Is/.*[^a-zA-Z0-9_$]\(.*EXU[a-zA-Z0-9_$]*\).*/\1/Ip
/IMP/Is/.*[^a-zA-Z0-9_$]\(.*IMP[a-zA-Z0-9_$]*\).*/\1/Ip
' $(ls -t $ORACLE_BASE/admin/$ORACLE_SID/udump/*.trc|head -1)

[oracle@DEV-RPT-2 ~]$ sed -n '
> /EXU/Is/.*[^a-zA-Z0-9_$]\(.*EXU[a-zA-Z0-9_$]*\).*/\1/Ip
> /IMP/Is/.*[^a-zA-Z0-9_$]\(.*IMP[a-zA-Z0-9_$]*\).*/\1/Ip
> ' $(ls -t $ORACLE_BASE/admin/$ORACLE_SID/udump/*.trc|head -1)
imp
exu8opt
IMP9USR
EXU8FUL
EXU9NLS
IMP9COMPAT
SET_IMP_TIMEZONE
SET_IMPORT_MODE
SET_IMP_EVENTS
[oracle@DEV-RPT-2 ~]$

4. 根据前面文档和跟踪得出的结果, 取消exp,imp权限

禁止exp

revoke select on sys.exu8usru from public;

[oracle@DEV-RPT-2 ~]$ exp a/a file=a.dmp rows=n tables=\(no_such_table\)

Export: Release 10.2.0.4.0 - Production on Tue Mar 24 07:57:10 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully
[oracle@DEV-RPT-2 ~]$
[oracle@DEV-RPT-2 ~]$ imp a/a file=a.dmp show=y

Import: Release 10.2.0.4.0 - Production on Tue Mar 24 07:57:41 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


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

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing A's objects into A
Import terminated successfully without warnings.
[oracle@DEV-RPT-2 ~]$

grant select on sys.exu8usru to public;

禁止imp

revoke select on sys.imp9usr from public;

[oracle@DEV-RPT-2 ~]$ exp a/a file=a.dmp rows=n tables=\(no_such_table\)

Export: Release 10.2.0.4.0 - Production on Tue Mar 24 07:58:27 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...
EXP-00011: A.NO_SUCH_TABLE does not exist
Export terminated successfully with warnings.
[oracle@DEV-RPT-2 ~]$
[oracle@DEV-RPT-2 ~]$ imp a/a file=a.dmp show=y

Import: Release 10.2.0.4.0 - Production on Tue Mar 24 07:58:35 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


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

IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00023: Import views not installed, please notify your DBA
IMP-00000: Import terminated unsuccessfully
[oracle@DEV-RPT-2 ~]$

grant select on sys.imp9usr to public;

禁止exp和imp

revoke select on sys.exu8opt from public;

[oracle@DEV-RPT-2 ~]$ exp a/a file=a.dmp rows=n tables=\(no_such_table\)

Export: Release 10.2.0.4.0 - Production on Tue Mar 24 07:59:45 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully
[oracle@DEV-RPT-2 ~]$
[oracle@DEV-RPT-2 ~]$ imp a/a file=a.dmp show=y

Import: Release 10.2.0.4.0 - Production on Tue Mar 24 07:59:51 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00023: Import views not installed, please notify your DBA
IMP-00000: Import terminated unsuccessfully
[oracle@DEV-RPT-2 ~]$

grant select on sys.exu8opt to public;

以上是10.2.0.4的测试结果, 其它版本的没试, 应该类似



外部链接:
Prevent the use of EXP and IMP for specified Oracle Users
Trace option in Export and Import
Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump
Compatibility Matrix for Export And Import Between Different Oracle Versions



-fin-

prevent remote login as sysdba - 禁止SYSDBA远程登录

prevent remote login as sysdba - 禁止SYSDBA远程登录




1.删除密码文件禁止sysdba登录

remote_login_passwordfile参数是EXCLUSIVE
且存在密码文件orapw

SQL> show parameter remote_login_passwordfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL> !ls -l $ORACLE_HOME/dbs/orapw$ORACLE_SID
-rw-r-----  1 oracle oinstall 2048 Feb 25 06:46 /home/oracle/app/oracle/product/10.2/dbs/orapwtest

SQL>

删除密码文件

SQL> !mv $ORACLE_HOME/dbs/orapw$ORACLE_SID $ORACLE_HOME/dbs/orapw$ORACLE_SID.old

SQL> !ls -l $ORACLE_HOME/dbs/orapw$ORACLE_SID*
-rw-r-----  1 oracle oinstall 2048 Feb 25 06:46 /home/oracle/app/oracle/product/10.2/dbs/orapwtest.old

SQL>

sysdba,sysoper不能远程登录了

SQL> conn sys/change_on_install@mgt:1541/test as sysdba
ERROR:
ORA-01031: insufficient privileges


SQL> conn sys/change_on_install@mgt:1541/test as sysoper
ERROR:
ORA-01031: insufficient privileges


SQL>

可以操作系统认证登录

SQL> conn / as sysdba
Connected.
SQL>

2.用orapwd的nosysdba选项禁止sysdba登录

orapwd重新创建密码文件, 密码不变, 加上nosysdba=y选项

[oracle@MGT ~]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=change_on_install entries=5 force=y nosysdba=y
[oracle@MGT ~]$

禁止sysdba远程登录, 但允许本地sysdba登录(操作系统认证)和远程sysoper登录

SQL> conn sys/manager@mgt:1541/test as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> conn / as sysdba
Connected.
SQL> conn sys/manager@mgt:1541/test as sysoper
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>

3.设置remote_login_passwordfile禁止sysdba登录

重建密码文件

[oracle@MGT ~]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=change_on_install entries=5 force=y
[oracle@MGT ~]$

可以连接sysdba

SQL> conn sys/change_on_install@mgt:1541/test as sysdba
Connected.
SQL>

设置remote_login_passwordfile=none, 重启数据库

alter system set remote_login_passwordfile=none scope=spfile;

SQL> conn / as sysdba
Connected.
SQL> alter system set remote_login_passwordfile=none scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size                  2084392 bytes
Variable Size             251658712 bytes
Database Buffers          159383552 bytes
Redo Buffers                6303744 bytes
Database mounted.
Database opened.
SQL> show parameter remote_login_passwordfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      NONE
SQL>

sysdba连接失败

SQL> conn sys/change_on_install@mgt:1541/test as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn sys/change_on_install@mgt:1541/test as sysoper
ERROR:
ORA-01017: invalid username/password; logon denied


SQL>

恢复设置

SQL> conn / as sysdba
Connected.
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

System altered.

SQL>

需要重启数据库


4. 修改密码散列值
alter user sys identified by values ... 将sys用户密码散列修改为一个不存在的值

SQL> conn sys/change_on_install@test as sysdba
Connected.
SQL> select username,password from dba_users where username='SYS';

USERNAME   PASSWORD
---------- ------------------------------
SYS        D4C5016086B2DC6A

SQL> alter user sys identified by values '1234567890123456';

User altered.

SQL> conn sys/change_on_install@test as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> alter user sys identified by values 'D4C5016086B2DC6A';

User altered.

SQL>

外部链接:
Database Administrator Security and Privileges
Disable Remote SYSDBA Connections
ORA-1031 When Connecting Remotely AS SYSDBA
Why Can I Login AS SYSDBA With any Username and Password?
How to Set up the Oracle Password File
SYSDBA and SYSOPER Privileges in Oracle
Problem - REMOTE_LOGIN_PASSWORDFILE Policy Violation when remote_login_password_file Parameter is set to 'EXCLUSIVE'
Remote login as Sysdba to Oracle database server




-fin-

how to recover SecureCRT saved session password - 恢复SecureCRT保存的密码

如何恢复SecureCRT保存的密码

很老很土却很有效的办法，现公之于众，好危险哟。幸好我根本不用盗版软件啊


登录密码保存在CRT的session配置文件中，不过是加密保存的，看不懂
也不知道它的加密算法，没法解密. 所以如果能让CRT自己显示出来就好了

首先修改要登录的主机的IP地址，改成另外一台机器, 这台的登录密码要跟原来机器的不一样
然后连接，这时因为登录密码错误，会弹出一个窗口，显示了用户名和密码，密码是用一串星号表示的
随便下一个星号密码查看软件，查看一下就行了
我找了两个，应该都能用
侠客密码查看器
Asterisk Logger





-fin-

disallow user to change password

----------
Forwarded message ----------
From: XIE WEN-MFK346
<wenxie at motorola.com>
Date:
2008/10/23
Subject: 禁止用户修改自己的密码
To:
xiewenxiewen at gmail.com















使用密码校验函数实现

















建一个用户
conn
/ as sysdba
create user b identified by b;
grant
connect,resource to b;












用户可以随便修改自己的密码
conn
b/b
alter user b identified by bb;
conn b/bb












建一个密码校验函数
conn
/ as sysdba





create or replace
function pwd_verify_func (
username varchar2,

password varchar2,
old_password varchar2
) return
boolean
is
begin
if user in ('SYS','SYSTEM') then

return true;
else
return false;

end if;
end;
/






只允许SYS,SYSTEM用户修改密码






建用户概要文件(profile)
create
profile pwd_profile limit password_verify_function
pwd_verify_func;
alter user b profile pwd_profile;












用户企图修改自己的密码
conn
b/bb
alter user b identified by b;











报错ORA-28221





!oerr ora 28221











必须执行REPLACE语句





alter user b
identified by b replace bb;











用户不能修改自己的密码了











用SYS用户可以修改
conn
/ as sysdba
alter user b identified by b;












修改一下函数中的提示信息





create or replace
function pwd_verify_func (
username varchar2,

password varchar2,
old_password varchar2
) return
boolean
is
begin
if user in ('SYS','SYSTEM') then

return true;
else

raise_application_error(-20001, 'You are not allowed to change the
password');
end if;
end;
/












conn b/b





alter user b
identified by bb replace b;























参考:







Password
Complexity Verification
@?/rdbms/admin/utlpwdmg.sql




























Xie Wen (谢文)




Network &

Operations,

Multimedia Applications & Services (MDB)

MOTOROLA Inc.

NO.104 mail box,

8th floor, Motorola Tower,

No.

1 Wang Jing East Road, Chao Yang District,

Beijing 100102 P. R.

China

e-mail wenxie at motorola.com








或者使用数据库级的触发器
Stopping a user from changing his own Oracle database password



-fin-

nessus installation snapshots - nessus安装截图

---------- Forwarded message ----------
From: XIE WEN-MFK346 <wenxie at motorola.com>
Date: 2008/8/28
Subject: nessus安装截图
To: xiewenxiewen at gmail.com

nessus是世界上最好用的安全扫描工具之一，而且对个人用户是免费的


1.下载nessus
访问网址 http://www.nessus.org/download/
下载nessus安装程序


不用填个人信息，直接点"Click Here to Download Nessus Directly"


服务器是redhat es4 64位的，要下载对应的版本，用es4.i386的就行



2. 申请注册号
http://www.nessus.org/plugins/?view=register-info


选"Register a HomeFeed"


输入邮件地址


等一会，注册号会发到你的邮箱



3. 安装
rpm -Uvh Nessus-3.2.1-es4.i386.rpm



4.注册
访问网址 https://plugins.nessus.org/offline.php


按提示运行 /opt/nessus/bin/nessus-fetch --challenge 获取口令


输入口令和注册号


显示


记住第一个url地址
http://plugins.nessus.org/get.php?f=all-2.0.tar.gz&u=34a15dd5903bb80755a35dc1eedb56ac&p=a37a58daefcd003abccdeefc5dae2166
以后手工下载插件还会用到

然后点击第二个链接，下载 nessus-fetch.rc，url地址是:
http://plugins.nessus.org/mkconfig.php?ac=注册号&c=口令
如
https://plugins.nessus.org/mkconfig.php?ac=F7B4-D650-0C92-3B97-5373&c=4b3302bf5896ffc9555c53768352f5342c9ba479

把nessus-fetch.rc拷到/opt/nessus/etc/nessus/下
cat /opt/nessus/etc/nessus/nessus-fetch.rc



5.下载安装最新插件
修改配置文件/opt/nessus/etc/nessus/nessusd.conf,禁止自动升级
grep auto_update /opt/nessus/etc/nessus/nessusd.conf


a.如果服务器可以连接互联网，那么运行
/opt/nessus/sbin/nessus-update-plugins
自动下载最新插件


b.也可以手工下载，下载地址就是第4步得到的地址

然后解压
tar -C /opt/nessus/lib/nessus/plugins/ -zxf all-2.0.tar.gz

重建插件数据库 /opt/nessus/var/nessus/plugins-code.db,运行
/opt/nessus/sbin/nessusd -Q 或 /opt/nessus/sbin/nessusd -R



6.创建用户
运行 /opt/nessus/sbin/nessus-adduser
创建一个用户并设置用户登录权限
用户名:admin
认证方式:pass
密码:admin
规则:只允许本机访问
accept
127.0.0.1/32
default
deny



7. 启动nessusd服务
运行
/sbin/service nessusd start


nessus服务器安装完成。
然后就可以用bin/nessus扫描服务器漏洞了，这是命令行模式的，最好再装个图形界面的NessusClient


8. 安装NessusClient
下载地址http://www.nessus.org/download/


安装
rpm -Uvh NessusClient-3.2.1-es4.i386.rpm



9.运行NessusClient
/opt/nessus/bin/NessusClient


点"Connect...",选"localhost", 点"Edit..."
改成正确的用户名和密码


点"Save"保存
选"localhost"，点"Connect"


连接nessus服务


如果是第一次连接，会出现确认框，选"Yes"
连上后



10. 增加扫描策略

点右边的"+",
按需编辑各种选项







点"Save"保存
选中"my_policy",点菜单"File"->"Export Policy..."，保存到文件my_policy.nessusrc
(不过好像只能export，不能import, import菜单永远是灰的???)




11. 增加扫描目标
按需增加要扫描的地址


点"Save"



12. 保存配置

选菜单"File"->"Save As...",保存配置好了的目标和策略到文件my.nessus


.nessus文件是xml格式的
比如:
....
nohostname192.168.11.16
nohostname192.168.11.15
nohostname192.168.11.10
nohostname192.168.11.12
norange10.194.132.24010.194.132.255
yesrange192.168.11.0192.168.16.255
yesnetwork192.168.30.0255.255.255.0
yesnetwork192.168.50.0255.255.255.0
yesnetwork10.194.132.0255.255.255.0
....


13. 扫描

可以点"Scan Now"在图形界面扫描，也可以在字符界面用命令扫描
运行
/opt/nessus/bin/nessus -V -x --dot-nessus /root/my.nessus --policy-name "my_policy" 127.0.0.1
1241 admin admin /root/my_report.nessus >/root/my_nessus.log 2>&1


生成html格式的报表
/opt/nessus/bin/nessus -V -x --dot-nessus /root/my_report.nessus \
-i "`/opt/nessus/bin/nessus -V -x --dot-nessus /root/my_report.nessus --list-reports|/usr/bin/tail -1|/bin/cut -c4-|/bin/sed 's/.$//g'`" \
-o /root/my_report.html


观看报表
elinks /root/my_report.html



14.定期运行脚本

[root@MGT scripts]# cat nessus.sh
#!/bin/bash
usage ()
{
cat <
Usage: $0 {help=n|y} target={sendmail=y|n}
Examples:
$0 help=y
$0
target=test
EOF
}
if [ $# -lt 1 ]; then
usage
exit 1
fi
for f; do eval $f; done
if [ x$help = xy ]; then
usage
exit 0
fi

PD=`/usr/bin/dirname $0`
WD=`(cd $PD/..;pwd)`
#NPATH=/data/nessus
NPATH=$WD
LPATH=$NPATH/logs
RPATH=$NPATH/reports
SPATH=$NPATH/scripts
P=`/bin/basename $0|/bin/cut -d. -f1`
N=${target:-test}
D=`/bin/date '+%Y%m%d'`
LOG=$LPATH/${P}_${N}_${D}.log
UMASK=`umask`
umask 0077
exec 3>&1 4>&2
exec >>$LOG 2>&1
echo "Begin at `/bin/date '+%Y%m%d%H%M%S'`"

NESSUS=/opt/nessus/bin/nessus
CFG=$SPATH/${N}.nessus
NRPT=$LPATH/report_${N}_${D}.nessus
RPT=$RPATH/report_${N}_${D}.html
PARAM="-V -x"
NSERVER="localhost 1241 admin admin"
$NESSUS $PARAM --dot-nessus $CFG --policy-name "my_policy" $NSERVER $NRPT
$NESSUS --dot-nessus $NRPT --list-reports
RPTNAME="`$NESSUS --dot-nessus $NRPT --list-reports|/usr/bin/tail -1|/bin/cut -c4-|/bin/sed 's/.$//g'`"
$NESSUS $PARAM --dot-nessus $NRPT -i "$RPTNAME" -o $RPT
if [ x$sendmail != xn ]; then
ADDR="wenxie@motorola.com"
/usr/sbin/sendmail -v $ADDR <
To: $ADDR
Subject:
Nessus Scan Report for $N - $D
Content-Type: text/html; charset="iso-8859-1"
`cat $RPT`
EOF
fi
echo "End at `/bin/date '+%Y%m%d%H%M%S'`"
echo ""
exec 1>&3 2>&4 3>&- 4>&-
umask $UMASK

[root@MGT scripts]#



[root@MGT ~]# crontab -l |grep ness
#0 2 * * * /data/nessus/scripts/nessus.sh target=wjlab sendmail=n
[root@MGT ~]#



Xie wen（谢文）
Network & Operations,
Mobile Software Solutions (MDB)
MOTOROLA Inc.
No. 108 Jian Guo Road, Chao Yang District, Beijing
100022 P. R. China
e-mail wenxie at motorola.com



-fin-