谢太数据库: 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-

谢太数据库

谢太的最爱

Blog Archive

Wednesday, April 1, 2009

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

No comments:

Followers