ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle对数据进行加密&解密处理

Oracle对数据进行加密&解密处理

原创 Linux操作系统 作者:尛样儿 时间:2010-04-01 11:38:24 0 删除 编辑
root@ythdc:/# su - oracle
oracle@ythdc:/home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Apr 1 12:30:32 2010

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, OLAP, Data Mining and Real Application Testing options

SQL> @?/rdbms/admin/dbmsobtk.sql

Library created.


No errors.

Library created.

No errors.

Package created.

Synonym created.

Package created.

No errors.

Synonym created.

Grant succeeded.

No errors.

Package created.

No errors.

Synonym created.

SQL> CREATE OR REPLACE FUNCTION return_encrypted_data(data IN VARCHAR)
  2    RETURN RAW IS
  3    key             VARCHAR(16) := 'A1B2C3D4E5F6G7H8';
  4    encryption_mode NUMBER := DBMS_CRYPTO.ENCRYPT_AES128 +
  5                              DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
  6  BEGIN
  7    RETURN DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW(data, 'AL32UTF8'),
  8                               encryption_mode,
  9                               UTL_I18N.STRING_TO_RAW(key, 'AL32UTF8'));
 10  END;
 11  /

Function created.

SQL> CREATE OR REPLACE FUNCTION return_decrypted_data(data IN RAW)
  2    RETURN VARCHAR IS
  3    key             VARCHAR(16) := 'A1B2C3D4E5F6G7H8';
  4    encryption_mode NUMBER := DBMS_CRYPTO.ENCRYPT_AES128 +
  5                              DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
  6  BEGIN
  7    RETURN UTL_RAW.CAST_TO_VARCHAR2(DBMS_CRYPTO.DECRYPT(data,
  8                                                        encryption_mode,
  9                                                        UTL_I18N.STRING_TO_RAW(key,
 10                                                                               'AL32UTF8')));
 11  END;
 12  /

Function created.

SQL> select return_encrypted_data('xiaoyang') from dual;

RETURN_ENCRYPTED_DATA('XIAOYANG')
--------------------------------------------------------------------------------
369117C7BDCE806C60243E53F90AD136

SQL> select return_decrypted_data('369117C7BDCE806C60243E53F90AD136') from dual;

RETURN_DECRYPTED_DATA('369117C7BDCE806C60243E53F90AD136')
--------------------------------------------------------------------------------
xiaoyang

SQL>

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

请登录后发表评论 登录
全部评论
Oracle数据库管理员,Oracle数据库系统构架员;2012年7月出版《构建最高可用Oracle数据库系统:Oracle 11gR2 RAC管理、维护与性能优化》一书;Oracle 10g OCM。

注册时间:2010-01-05

  • 博文量
    483
  • 访问量
    5654672