oracle数据加密包

以下是包的代码和说明：

包的声明：

create or replace package PG_ENCRYPT_DECRYPT is
  function GEN_RAW_KEY  return raw;
  function encrypt_data(p_text in varchar2) return varchar2;
  function encrypt_data(p_num in number) return varchar2;
  function decrypt_data(p_text varchar2) return varchar2;
end;

 

包的实现：
create or replace package body PG_ENCRYPT_DECRYPT is
/*Creating function GEN_RAW_KEY to get key*/
  function GEN_RAW_KEY  return raw
    as
    rawkey raw(240) := '';
    fhandle   utl_file.file_type;
    fp_buffer varchar2(4000);
    begin
      fhandle := utl_file.fopen ('UTL_FILE_DIR','test.abc', 'r');
      utl_file.get_line (fhandle , fp_buffer);
      rawkey :=fp_buffer;
      utl_file.fclose(fhandle);
    return rawkey;
  end;
/*Creating function encrypt_data,encrypt character*/
  function encrypt_data(p_text in varchar2) return varchar2
    as
    v_text varchar2(4000);
    v_enc varchar2(4000);
    p_key varchar2(4000);
    begin
      v_text := rpad( p_text, (trunc(length(p_text)/8)+1)*8, chr(0));
      p_key := GEN_RAW_KEY;
      sys.dbms_obfuscation_toolkit.desencrypt(
      input_string => v_text,
      key_string => p_key,
      encrypted_string=>v_enc);
    return v_enc;
  end;
/*Creating function encrypt_data,encrypt number*/
  function encrypt_data(p_num in number) return varchar2
    as
    v_text  varchar2(4000);
    v_enc   varchar2(4000);
    p_key   varchar2(4000);
    p_text  varchar2(4000);
    begin
      p_text := to_char(p_num);
      v_text := rpad( p_text, (trunc(length(p_text)/8)+1)*8, chr(0));
      p_key := GEN_RAW_KEY;
      sys.dbms_obfuscation_toolkit.desencrypt(
      input_string => v_text,
      key_string => p_key,
      encrypted_string=>v_enc);
    return v_enc;
  end;
/*Creating function decrypt_data,decrypt character*/
  function decrypt_data(p_text varchar2) return varchar2
    as
    v_text varchar2(4000);
    p_key varchar2(4000);
    begin
      p_key := GEN_RAW_KEY;
      sys.dbms_obfuscation_toolkit.desdecrypt(
      input_string => p_text,
      key_string => p_key,
      decrypted_string=> v_text);
    return rtrim(v_text,chr(0));
  end;
end;

 

下面举例
例1、字符数据加密

SQL> desc emp
Name         Type         Nullable Default Comments
------------ ------------ -------- ------- --------
ENO          VARCHAR2(10) Y                        
ENAME        VARCHAR2(64) Y                        
ENCRYPT_NAME VARCHAR2(64) Y                        

SQL> select *from emp;

ENO        ENAME                                                            ENCRYPT_NAME
---------- ---------------------------------------------------------------- ----------------------------------------------------------------
0004       c++                                                             
00001      sqlee                                                           
00002      lee                                                             
00003      oracle                                                          

SQL> update emp set ENCRYPT_NAME = PG_ENCRYPT_DECRYPT.encrypt_data(ename);

4 rows updated

SQL> commit;

Commit complete

SQL> select *from emp;

ENO        ENAME                                                            ENCRYPT_NAME
---------- ---------------------------------------------------------------- ----------------------------------------------------------------
0004       c++                                                              膉??.?
00001      sqlee                                                            鍀DkR徤a
00002      lee                                                              F{蘱j0?
00003      oracle                                                           ?(?嫄?

例2、字符数据解密

SQL> update emp set ENCRYPT_NAME = PG_ENCRYPT_DECRYPT.decrypt_data(ENCRYPT_NAME);

4 rows updated

SQL> commit;

Commit complete

SQL> select *from emp;

ENO        ENAME                                                            ENCRYPT_NAME
---------- ---------------------------------------------------------------- ----------------------------------------------------------------
0004       c++                                                                 c++
00001      sqlee                                                            sqlee
00002      lee                                                                 lee
00003      oracle                                                           oracle

例3、数字数据加密和解密

SQL> desc emp;
Name             Type         Nullable Default Comments
---------------- ------------ -------- ------- --------
ENO              VARCHAR2(10) Y                        
ENAME            VARCHAR2(64) Y                        
ENCRYPT_NAME     VARCHAR2(64) Y                        
ACCOUNTS         NUMBER       Y                        
ENCRYPT_ACCOUNTS VARCHAR2(64) Y                        

SQL> select *from emp;

ENO        ENAME                                                            ENCRYPT_NAME                                                       ACCOUNTS ENCRYPT_ACCOUNTS
---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
00005      123                                                              ?D泭磈?                                                                  
00004      c++                                                              膉??.?                                                              1000
00001      sqlee                                                            鍀DkR徤a                                                                   
00002      lee                                                              F{蘱j0?                                                                   
00003      oracle                                                           ?(?嫄?                                                                  

SQL> update emp set ENCRYPT_ACCOUNTS = pg_encrypt_decrypt.encrypt_data(ACCOUNTS) where eno ='00004';

1 row updated

SQL> select *from emp;

ENO        ENAME                                                            ENCRYPT_NAME                                                       ACCOUNTS ENCRYPT_ACCOUNTS
---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
00005      123                                                              ?D泭磈?                                                                  
00004      c++                                                              膉??.?                                                              1000 p.湇R?N
00001      sqlee                                                            鍀DkR徤a                                                                   
00002      lee                                                              F{蘱j0?                                                                   
00003      oracle                                                           ?(?嫄?                                                                  

SQL> update emp set ENCRYPT_ACCOUNTS = pg_encrypt_decrypt.decrypt_data(ENCRYPT_ACCOUNTS) where eno ='00004';

1 row updated

SQL> select *from emp;

ENO        ENAME                                                            ENCRYPT_NAME                                                       ACCOUNTS ENCRYPT_ACCOUNTS
---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
00005      123                                                              ?D泭磈?                                                                  
00004      c++                                                              膉??.?                                                              1000 1000
00001      sqlee                                                            鍀DkR徤a                                                                   
00002      lee                                                              F{蘱j0?                                                                   
00003      oracle                                                           ?(?嫄?                                                                  

SQL> commit;

Commit complete

SQL> update emp set ACCOUNTS = 168.50 where eno = '00004';

1 row updated

SQL> commit;

Commit complete

SQL> update emp set ENCRYPT_ACCOUNTS = pg_encrypt_decrypt.encrypt_data(ACCOUNTS) where eno ='00004';

1 row updated

SQL> select *from emp;

ENO        ENAME                                                            ENCRYPT_NAME                                                       ACCOUNTS ENCRYPT_ACCOUNTS
---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
00005      123                                                              ?D泭磈?                                                                  
00004      c++                                                              膉??.?                                                             168.5 玴灹.??
00001      sqlee                                                            鍀DkR徤a                                                                   
00002      lee                                                              F{蘱j0?                                                                   
00003      oracle                                                           ?(?嫄?                                                                  

SQL> update emp set ENCRYPT_ACCOUNTS = pg_encrypt_decrypt.decrypt_data(ENCRYPT_ACCOUNTS) where eno ='00004';

1 row updated

SQL> select *from emp;

ENO        ENAME                                                            ENCRYPT_NAME                                                       ACCOUNTS ENCRYPT_ACCOUNTS
---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
00005      123                                                              ?D泭磈?                                                                  
00004      c++                                                              膉??.?                                                             168.5 168.5
00001      sqlee                                                            鍀DkR徤a                                                                   
00002      lee                                                              F{蘱j0?                                                                   
00003      oracle                                                           ?(?嫄?                                                                  

SQL> update emp set ENCRYPT_ACCOUNTS = pg_encrypt_decrypt.encrypt_data(ACCOUNTS) where eno ='00004';

1 row updated

SQL> rollback;

Rollback complete

SQL> update emp set ACCOUNTS = 10000.250 where eno = '00004';

1 row updated

SQL> commit;

Commit complete

SQL> update emp set ENCRYPT_ACCOUNTS = pg_encrypt_decrypt.encrypt_data(ACCOUNTS) where eno ='00004';

1 row updated

SQL> commit;

Commit complete

SQL> select *from emp;

ENO        ENAME                                                            ENCRYPT_NAME                                                       ACCOUNTS ENCRYPT_ACCOUNTS
---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
00005      123                                                              ?D泭磈?                                                                  
00004      c++                                                              膉??.?                                                          10000.25 4c訕&靾熾

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12158104/viewspace-214910/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12158104/viewspace-214910/