ANBOB™

Oracle 10g 引入了 DBMS_CRYPTO 包来加密和解密存储的数据。它支持多种行业标准加密和哈希算法，包括高级加密标准 (AES)。我在这里使用该包将纯文本转换为加密形式并存储在数据库中。应用程序用户可以解密它并显示给用户。替换8I,9I前的DBMS_OBFUSCATION_TOOLKIT 包，这个包在10G以前的版本是没有的，它提供了更多的加密算法应用更方便。

* Cryptographic algorithms – DES, 3DES, AES, RC4, 3DES_2KEY
* Padding forms – PKCS5, zeroes
* Block cipher chaining modes – CBC, CFB, ECB, OFB
* Cryptographic hash algorithms – MD5, SHA-1, MD4
* Keyed hash (MAC) algorithms – HMAC_MD5, HMAC_SH1
* Cryptographic pseudo-random number generator – RAW, NUMBER, BINARY_INTEGER
* Database types – RAW, CLOB, BLOB

包的创建方法
sql>@{ORACLE_HOME}/rdbms/admin/dbmsobtk.sql

SET LONG 10000
select dbms_metadata.get_ddl('PACKAGE','DBMS_CRYPTO') FROM DUAL;

DBMS_METADATA.GET_DDL('PACKAGE','DBMS_CRYPTO')
--------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE "SYS"."DBMS_CRYPTO" AS
---------------------------------------------------------------------------
--
-- PACKAGE NOTES
--
-- DBMS_CRYPTO contains basic cryptographic functions and
-- procedures. To use correctly and securely, a general level of
-- security expertise is assumed.
--
-- VARCHAR2 datatype is not supported. Cryptographic operations
-- on this type should be prefaced with conversions to a uniform
-- character set (AL32UTF8) and conversion to RAW type.
--
-- Prior to encryption, hashing or keyed hashing, CLOB datatype is
-- converted to AL32UTF8. This allows cryptographic data to be
-- transferred and understood between databases with different
-- character sets, across character set changes and between
-- separate processes (for example, Java programs).
--
---------------------------------------------------------------------------

-------------------------- ALGORITHM CONSTANTS ----------------------------
-- The following constants refer to various types of cryptographic
-- functions available from this package. Some of the constants
-- represent modifiers to these algorithms.
---------------------------------------------------------------------------

-- Hash Functions
HASH_MD4 CONSTANT PLS_INTEGER := 1;
HASH_MD5 CONSTANT PLS_INTEGER := 2;
HASH_SH1 CONSTANT PLS_INTEGER := 3;

-- MAC Functions
HMAC_MD5 CONSTANT PLS_INTEGER := 1;
HMAC_SH1 CONSTANT PLS_INTEGER := 2;

-- Block Cipher Algorithms
ENCRYPT_DES CONSTANT PLS_INTEGER := 1; -- 0x0001
ENCRYPT_3DES_2KEY CONSTANT PLS_INTEGER := 2; -- 0x0002
ENCRYPT_3DES CONSTANT PLS_INTEGER := 3; -- 0x0003
ENCRYPT_AES CONSTANT PLS_INTEGER := 4; -- 0x0004
ENCRYPT_PBE_MD5DES CONSTANT PLS_INTEGER := 5; -- 0x0005
ENCRYPT_AES128 CONSTANT PLS_INTEGER := 6; -- 0x0006
ENCRYPT_AES192 CONSTANT PLS_INTEGER := 7; -- 0x0007
ENCRYPT_AES256 CONSTANT PLS_INTEGER := 8; -- 0x0008

-- Block Cipher Chaining Modifiers
CHAIN_CBC CONSTANT PLS_INTEGER := 256; -- 0x0100
CHAIN_CFB CONSTANT PLS_INTEGER := 512; -- 0x0200
CHAIN_ECB CONSTANT PLS_INTEGER := 768; -- 0x0300
CHAIN_OFB CONSTANT PLS_INTEGER := 1024; -- 0x0400

-- Block Cipher Padding Modifiers
PAD_PKCS5 CONSTANT PLS_INTEGER := 4096; -- 0x1000
PAD_NONE CONSTANT PLS_INTEGER := 8192; -- 0x2000
PAD_ZERO CONSTANT PLS_INTEGER := 12288; -- 0x3000
PAD_ORCL CONSTANT PLS_INTEGER := 16384; -- 0x4000

-- Stream Cipher Algorithms
ENCRYPT_RC4 CONSTANT PLS_INTEGER := 129; -- 0x0081

-- Convenience Constants for Block Ciphers
DES_CBC_PKCS5 CONSTANT PLS_INTEGER := ENCRYPT_DES
+ CHAIN_CBC
+ PAD_PKCS5;

DES3_CBC_PKCS5 CONSTANT PLS_INTEGER := ENCRYPT_3DES
+ CHAIN_CBC
+ PAD_PKCS5;

AES_CBC_PKCS5 CONSTANT PLS_INTEGER := ENCRYPT_AES
+ CHAIN_CBC
+ PAD_PKCS5;

...

测试

dbms_crypto.decrypt(
src IN RAW,
typ IN PLS_INTEGER,
key IN RAW,
iv  IN RAW DEFAULT NULL) RETURN RAW;

set serveroutput on

set linesize 121

DECLARE
 l_credit_card_no VARCHAR2(19) := '1234-5678-9012-3456';
 l_ccn_raw RAW(128) := utl_raw.cast_to_raw(l_credit_card_no);
 l_key     RAW(128) := utl_raw.cast_to_raw('anbob.com');

 l_encrypted_raw RAW(2048);
 l_decrypted_raw RAW(2048);
BEGIN
  dbms_output.put_line('Original : ' || l_credit_card_no);
  
   dbms_output.put_line('Key : ' || utl_raw.cast_to_varchar2(l_key));

  l_encrypted_raw := dbms_crypto.encrypt(l_ccn_raw,
  dbms_crypto.des_cbc_pkcs5, l_key);

  dbms_output.put_line('Encrypted : ' ||
  RAWTOHEX(utl_raw.cast_to_raw(l_encrypted_raw)));

  l_decrypted_raw := dbms_crypto.decrypt(src => l_encrypted_raw,
  typ => dbms_crypto.des_cbc_pkcs5, key => l_key);

  dbms_output.put_line('Decrypted : ' ||
  utl_raw.cast_to_varchar2(l_decrypted_raw));
END;
/

SQL>

Original : 1234-5678-9012-3456
Key : anbob.com
Encrypted : 373037393732333945383935463138364641344231353635454245423846413744453632453243444145433144383337
Decrypted : 1234-5678-9012-3456

PL/SQL procedure successfully completed.



DECLARE
 enc_val   RAW(2000);
 l_key     RAW(2000);
 l_key_len NUMBER := 128/8; -- convert bits to bytes
 l_mod     NUMBER := dbms_crypto.ENCRYPT_AES128 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_ZERO;

BEGIN
  l_key := dbms_crypto.randombytes(l_key_len);

  enc_val := dbms_crypto.encrypt(
  utl_i18n.string_to_raw('1234-5678-9012-3456', 'AL32UTF8'),
  l_mod, l_key);

  dbms_output.put_line(enc_val);
END;
/
F865E0438DEA93A8E73C5986AFB611F714DCE282067482102C6458BAB6E8881C

PL/SQL procedure successfully completed.


CREATE OR REPLACE PACKAGE encrypt_decrypt
AS
   FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC;
   FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC;
END;
/

CREATE OR REPLACE PACKAGE BODY encrypt_decrypt
AS
     encryption_type    PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES
                                     + DBMS_CRYPTO.CHAIN_CBC
                                     + DBMS_CRYPTO.PAD_PKCS5;
     encryption_key     RAW (32) := UTL_RAW.cast_to_raw('anbob.com');

	 
     FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC
     IS
        encrypted_raw      RAW (2000);
     BEGIN
        encrypted_raw := DBMS_CRYPTO.ENCRYPT
        (
           src => UTL_RAW.CAST_TO_RAW (p_plainText),
           typ => encryption_type,
           key => encryption_key
        );
       RETURN encrypted_raw;
     END encrypt;
	 
     FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC
     IS
        decrypted_raw      RAW (2000);
     BEGIN
        decrypted_raw := DBMS_CRYPTO.DECRYPT
        (
            src => p_encryptedText,
            typ => encryption_type,
            key => encryption_key
        );
        RETURN (UTL_RAW.CAST_TO_VARCHAR2 (decrypted_raw));
     END decrypt;
END;
/

SQL> select encrypt_decrypt.encrypt('anbob') from dual;

ENCRYPT_DECRYPT.ENCRYPT('ANBOB')
-------------------------------------------------------------------------------------------------------------------------
059F611EC2A5395D

SQL> select encrypt_decrypt.decrypt('059F611EC2A5395D') from dual;

ENCRYPT_DECRYPT.DECRYPT('059F611EC2A5395D')
-------------------------------------------------------------------------------------------------------------------------
anbob

打赏

微信扫一扫，打赏作者吧～