分类目录归档：Oracle 安全

给你的dmp文件(datapump)加上密码锁

发表于 2015 年 2 月 11 日 由 惜分飞

从oracle 11.1.0.7开始oracle 支持data pump导出加密,从而实现dmp文件安全.不会因为dmp文件丢失而导致数据泄露.涉及数据泵加密参数主要有：ENCRYPTION,ENCRYPTION_ALGORITHM,ENCRYPTION_MODE,ENCRYPTION_PASSWORD几个参数.这里测试的是使用最简单方式实现datapump加密功能,如果需要更好的数据安全可以考虑实时密码钱包

ENCRYPTION
Encrypt part or all of a dump file.
Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.
ENCRYPTION为加密dmp文件加密部分，其参数值可以有ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE，
如果只有ENCRYPTION_PASSWORD指定值，那么ENCRYPTION默认值为ALL
如果ENCRYPTION_PASSWORD和ENCRYPTION均为指定，那么默认值为NONE

ENCRYPTION_ALGORITHM
Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.
ENCRYPTION_ALGORITHM是指加密算法，参数值可以有AES128, AES192 and AES256。默认值为AES128

ENCRYPTION_MODE
Method of generating encryption key.
Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].
ENCRYPTION_MODE指定加密方式，其参数值有DUAL, PASSWORD and TRANSPARENT。默认值为TRANSPARENT
DUAL表示你指定的加密的dmp文件在导入的时候可以通过密码方式或者加密钱包方式导入
PASSWORD表示指定密码方式创建dmp文件，你导入也需要提供密码
TRANSPARENT需要加密钱包方式导出和导入

ENCRYPTION_PASSWORD
Password key for creating encrypted data within a dump file.
ENCRYPTION_PASSWORD指定加密密码

创建测试表

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 11 14:52:32 2015

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


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

SQL> conn chf/xifenfei
Connected.
SQL> create table xifenfei (id number,name varchar2(50));

Table created.

SQL> insert into xifenfei values(&id,'&name');
Enter value for id: 1
Enter value for name: xifenfei
old   1: insert into xifenfei values(&id,'&name')
new   1: insert into xifenfei values(1,'xifenfei')

1 row created.

SQL> /
Enter value for id: 2
Enter value for name: www.xifenfei.com
old   1: insert into xifenfei values(&id,'&name')
new   1: insert into xifenfei values(2,'www.xifenfei.com')

1 row created.

SQL> /
Enter value for id: 3
Enter value for name: www.orasos.com
old   1: insert into xifenfei values(&id,'&name')
new   1: insert into xifenfei values(3,'www.orasos.com')

1 row created.

SQL> commit;

Commit complete.

SQL> col name for a50
SQL> set lines 100
SQL> select * from xifenfei;

        ID NAME
---------- --------------------------------------------------
         1 xifenfei
         2 www.xifenfei.com
         3 www.orasos.com

创建目录

SQL> create directory dir_xff as '/tmp';

Directory created.

不加密导出

[oracle@localhost ~]$ expdp chf/xifenfei tables=xifenfei directory=dir_xff dumpfile=none.dmp logfile=none.log

Export: Release 11.2.0.4.0 - Production on Wed Feb 11 15:29:13 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "CHF"."SYS_EXPORT_TABLE_01":  chf/******** tables=xifenfei 
directory=dir_xff dumpfile=none.dmp logfile=none.log 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "CHF"."XIFENFEI"                            5.460 KB       3 rows
Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHF.SYS_EXPORT_TABLE_01 is:
  /tmp/none.dmp
Job "CHF"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 11 15:29:37 2015 elapsed 0 00:00:14

数据字典和数据都加密导出

[oracle@localhost ~]$ expdp chf/xifenfei tables=xifenfei directory=dir_xff  ENCRYPTION=ALL dumpfile=ALL.dmp 
logfile=ALL.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=www.xifenfei.com

Export: Release 11.2.0.4.0 - Production on Wed Feb 11 15:33:06 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "CHF"."SYS_EXPORT_TABLE_01":  chf/******** tables=xifenfei directory=dir_xff ENCRYPTION=ALL 
dumpfile=ALL.dmp logfile=ALL.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=******** 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "CHF"."XIFENFEI"                            5.468 KB       3 rows
Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHF.SYS_EXPORT_TABLE_01 is:
  /tmp/ALL.dmp
Job "CHF"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 11 15:33:13 2015 elapsed 0 00:00:06

数据加密导出

[oracle@localhost ~]$ expdp chf/xifenfei tables=xifenfei directory=dir_xff  ENCRYPTION=DATA_ONLY 
dumpfile=DATA_ONLY.dmp logfile=DATA_ONLY.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=www.xifenfei.com

Export: Release 11.2.0.4.0 - Production on Wed Feb 11 15:33:52 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "CHF"."SYS_EXPORT_TABLE_01":  chf/******** tables=xifenfei directory=dir_xff ENCRYPTION=DATA_ONLY 
dumpfile=DATA_ONLY.dmp logfile=DATA_ONLY.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=******** 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "CHF"."XIFENFEI"                            5.468 KB       3 rows
Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHF.SYS_EXPORT_TABLE_01 is:
  /tmp/DATA_ONLY.dmp
Job "CHF"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 11 15:33:57 2015 elapsed 0 00:00:04

数据字典加密导出

[oracle@localhost tmp]$ expdp chf/xifenfei tables=xifenfei directory=dir_xff  ENCRYPTION=METADATA_ONLY 
dumpfile=METADATA_ONLY.dmp logfile=METADATA_ONLY.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=www.xifenfei.com

Export: Release 11.2.0.4.0 - Production on Wed Feb 11 15:50:00 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "CHF"."SYS_EXPORT_TABLE_01":  chf/******** tables=xifenfei directory=dir_xff ENCRYPTION=METADATA_ONLY 
dumpfile=METADATA_ONLY.dmp logfile=METADATA_ONLY.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=******** 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "CHF"."XIFENFEI"                            5.460 KB       3 rows
Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHF.SYS_EXPORT_TABLE_01 is:
  /tmp/METADATA_ONLY.dmp
Job "CHF"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 11 15:50:04 2015 elapsed 0 00:00:03

删除测试表

SQL> drop table xifenfei purge;

Table dropped.

导入数据未输入密码

[oracle@localhost tmp]$ impdp chf/xifenfei tables=xifenfei directory=dir_xff 
dumpfile=METADATA_ONLY.dmp logfile=xifenfei.log

Import: Release 11.2.0.4.0 - Production on Wed Feb 11 16:03:13 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39174: Encryption password must be supplied.

导入数据密码错误

[oracle@localhost tmp]$ impdp chf/xifenfei tables=xifenfei directory=dir_xff   
dumpfile=METADATA_ONLY.dmp logfile=METADATA_ONLY.log ENCRYPTION_PASSWORD=www.orasos.com

Import: Release 11.2.0.4.0 - Production on Wed Feb 11 16:05:46 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39176: Encryption password is incorrect.

导入数据密码正确

[oracle@localhost tmp]$ impdp chf/xifenfei tables=xifenfei directory=dir_xff   
dumpfile=METADATA_ONLY.dmp logfile=METADATA_ONLY.log ENCRYPTION_PASSWORD=www.xifenfei.com

Import: Release 11.2.0.4.0 - Production on Wed Feb 11 16:06:00 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "CHF"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "CHF"."SYS_IMPORT_TABLE_01":  chf/******** tables=xifenfei directory=dir_xff 
dumpfile=METADATA_ONLY.dmp logfile=METADATA_ONLY.log ENCRYPTION_PASSWORD=******** 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "CHF"."XIFENFEI"                            5.460 KB       3 rows
Job "CHF"."SYS_IMPORT_TABLE_01" successfully completed at Wed Feb 11 16:06:04 2015 elapsed 0 00:00:02

验证数据

[oracle@localhost tmp]$ sqlplus chf/xifenfei

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 11 16:06:09 2015

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


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

SQL> set lines 150
SQL> col name for a50
SQL> select * from xifenfei;

        ID NAME
---------- --------------------------------------------------
         1 xifenfei
         2 www.xifenfei.com
         3 www.orasos.com
发表在 Oracle 安全 | 标签为 , , , | 评论关闭

给你的rman备份集加上密码锁

发表于 2015 年 1 月 29 日 由 惜分飞

数据的安全越来越重要,不是说你的生产库安全，你的数据就一定安全了，rman备份也是泄露数据的一个重要地方,如果别人拿到了你的备份集,一样等同入侵了你的生产库。为了rman备份的安全，最简单方式就是使用set encryption方式在rman备份过程中设置密码,需要版本为10.2及其以后企业版版,另外如果需要备份到带库只能使用oracle自己的osb(Oracle Secure Backup),注意rman只有backupset可以加密,copy无法进行加密
数据库版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0

支持rman加密算法

SQL> select ALGORITHM_NAME
  2  from V$RMAN_ENCRYPTION_ALGORITHMS;

ALGORITHM_NAME
----------------------------------------------------------------
AES128
AES192
AES256

调整加密算法

RMAN> show  ENCRYPTION ALGORITHM;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES256';

new RMAN configuration parameters:
CONFIGURE ENCRYPTION ALGORITHM 'AES256';
new RMAN configuration parameters are successfully stored

RMAN> show ENCRYPTION ALGORITHM;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE ENCRYPTION ALGORITHM 'AES256';

创建新测试数据文件
我们这里测试的是对新创建的5号文件进行加密备份和还原

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf

SQL> create tablespace rman_xifenfei datafile 
  2 '/u01/app/oracle/oradata/orcl/xifenfei01.dbf' size 100M;

Tablespace created.

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/orcl/system01.dbf
         2 /u01/app/oracle/oradata/orcl/sysaux01.dbf
         3 /u01/app/oracle/oradata/orcl/undotbs01.dbf
         4 /u01/app/oracle/oradata/orcl/users01.dbf
         5 /u01/app/oracle/oradata/orcl/xifenfei01.dbf

SQL> create table chf.t_xifenfei tablespace rman_xifenfei 
  2  as select * from dba_objects;

Table created.

SQL> select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     86721

rman加密备份

RMAN> set encryption on identified by 'www.xifenfei.com' only;

executing command: SET encryption

RMAN> backup datafile 5;

Starting backup at 28-JAN-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/xifenfei01.dbf
channel ORA_DISK_1: starting piece 1 at 28-JAN-15
channel ORA_DISK_1: finished piece 1 at 28-JAN-15
piece handle=/u01/2015_01_28/o1_mf_nnndf_TAG20150128T230115_bdkyfvlw_.bkp tag=TAG20150128T230115 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-JAN-15

准备恢复测试

RMAN> sql 'alter database datafile 5 offline';

sql statement: alter database datafile 5 offline

[oracle@localhost ~]$ rm /u01/app/oracle/oradata/orcl/xifenfei01.dbf
[oracle@localhost ~]$ ls /u01/app/oracle/oradata/orcl/xifenfei01.dbf
ls: /u01/app/oracle/oradata/orcl/xifenfei01.dbf: No such file or directory

rman恢复测试

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jan 28 23:02:24 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1378620768)

RMAN> list backup of datafile 5;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    10.94M     DISK        00:00:01     28-JAN-15      
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20150128T230115
        Piece Name: /u01/2015_01_28/o1_mf_nnndf_TAG20150128T230115_bdkyfvlw_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5       Full 54057180   28-JAN-15 /u01/app/oracle/oradata/orcl/xifenfei01.dbf

--未输入密码
RMAN> restore datafile 5;

Starting restore at 28-JAN-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=492 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/xifenfei01.dbf
channel ORA_DISK_1: reading from backup piece /u01/2015_01_28/o1_mf_nnndf_TAG20150128T230115_bdkyfvlw_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/28/2015 23:02:52
ORA-19870: error while restoring backup piece /u01/2015_01_28/o1_mf_nnndf_TAG20150128T230115_bdkyfvlw_.bkp
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open

--设置错误密码
RMAN> SET DECRYPTION IDENTIFIED BY 'www.orasos.com';

executing command: SET decryption

RMAN> restore datafile 5;

Starting restore at 28-JAN-15
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/xifenfei01.dbf
channel ORA_DISK_1: reading from backup piece /u01/2015_01_28/o1_mf_nnndf_TAG20150128T230115_bdkyfvlw_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/28/2015 23:03:31
ORA-19870: error while restoring backup piece /u01/2015_01_28/o1_mf_nnndf_TAG20150128T230115_bdkyfvlw_.bkp
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open

--设置正确密码
RMAN> SET DECRYPTION IDENTIFIED BY 'www.xifenfei.com';

executing command: SET decryption

RMAN> restore datafile 5;

Starting restore at 28-JAN-15
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/xifenfei01.dbf
channel ORA_DISK_1: reading from backup piece /u01/2015_01_28/o1_mf_nnndf_TAG20150128T230115_bdkyfvlw_.bkp
channel ORA_DISK_1: piece handle=/u01/2015_01_28/o1_mf_nnndf_TAG20150128T230115_bdkyfvlw_.bkp tag=TAG20150128T230115
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 28-JAN-15

验证数据还原

RMAN> recover datafile 5;

Starting recover at 28-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=7 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 28-JAN-15

RMAN> sql 'alter database datafile 5 online';

sql statement: alter database datafile 5 online

RMAN> exit

Recovery Manager complete.
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 28 23:05:55 2015

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


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

SQL> select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     86721

至此我们可以看到,最简单的rman加密备份和加密恢复测试完成,在使用set encryption加密后,如果不输入或者错误的输入密码无法使用备份集,从而确保了备份集的安全.

发表在 Oracle 安全 | 标签为 , , | 评论关闭

通过DBMS_CRYPTO包对表敏感字段进行加密

发表于 2015 年 1 月 28 日 由 惜分飞

在安全越来越重视的近体，我们不少时候需要对数据库中的某个表的敏感列数据(银行卡,身份证号码,金额等)进行加密，方式数据泄密,在11.2.0.4中可以通过dbms_crypto包方式实现,增加oracle的加密效率,本文提供处理思路,其他可以根据需求尽情发挥
数据库版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
[/shell]
<strong>创建加密函数</strong>
1
SQL> create or replace function f_Encrypt_number(number_in in varchar2) return raw is
  2  number_in_raw RAW(128):=UTL_I18N.STRING_TO_RAW(number_in,'AL32UTF8');
  3  key_number number(32):=32432432343243279898;
  4  key_raw RAW(128):=UTL_RAW.cast_from_number(key_number);
  5  encrypted_raw RAW(128);
  6  begin
  7  encrypted_raw:=dbms_crypto.Encrypt(src=>number_in_raw,typ=>DBMS_CRYPTO.DES_CBC_PKCS5,key=>key_raw);
  8  return encrypted_raw;
  9  end;
 10  /

Function created.

测试加密函数

SQL> select f_Encrypt_number('wwww.xifenfei.com') from dual;

F_ENCRYPT_NUMBER('WWWW.XIFENFEI.COM')
--------------------------------------------------------------------------------
003CB89CB77F6644C93AE2CF7810B0E3E3B10B8C60B54058

创建解密函数

SQL> create or replace function f_decrypt_number (encrypted_raw IN RAW)
  2  return varchar2 is
  3  decrypted_raw raw(48);
  4  key_number number(32):=32432432343243279898;
  5  key_raw RAW(128):=UTL_RAW.cast_from_number(key_number);
  6  begin
  7  decrypted_raw := DBMS_CRYPTO.DECRYPT
  8  (
  9  src => encrypted_raw,
 10  typ => DBMS_CRYPTO.DES_CBC_PKCS5,
 11  key => key_raw
 12  );
 13  return UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');
 14  END;
 15  /

Function created.

测试解密函数

SQL> select f_decrypt_number('003CB89CB77F6644C93AE2CF7810B0E3E3B10B8C60B54058') from dual;

F_DECRYPT_NUMBER('003CB89CB77F6644C93AE2CF7810B0E3E3B10B8C60B54058')
--------------------------------------------------------------------------------
wwww.xifenfei.com

创建表综合测试

SQL> create table xifenfei_crypto
  2  (id number, name varchar2(20),en_name raw(128)) ;

Table created.

SQL> insert into xifenfei_crypto (id,name) select object_id,object_name from dba_objects where rownum<10;

9 rows created.

SQL> commit;

Commit complete.

SQL> select * from xifenfei_crypto;

        ID NAME                 EN_NAME
---------- -------------------- ------------------------------
        20 ICOL$
        46 I_USER1
        28 CON$
        15 UNDO$
        29 C_COBJ#
         3 I_OBJ#
        25 PROXY_ROLE_DATA$
        41 I_IND1
        54 I_CDEF2

9 rows selected.

SQL> update xifenfei_crypto set en_name=f_Encrypt_number(name);

9 rows updated.

SQL> commit;

Commit complete.

SQL> select * from xifenfei_crypto;

        ID NAME                 EN_NAME
---------- -------------------- --------------------------------------------------
        20 ICOL$                FE17B031331839A9
        46 I_USER1              FEF96765B1E2C53C
        28 CON$                 0283FCE900ACED5C
        15 UNDO$                20DD92762F199436
        29 C_COBJ#              A0CB43E2EA6BA889
         3 I_OBJ#               F2DE1B9C8A39AA3D
        25 PROXY_ROLE_DATA$     62B99C02EBD4B250311E4490207FEF18CBD8CD8FBA1BFD81
        41 I_IND1               3F4C3C186F8E2F52
        54 I_CDEF2              CA23D202802BD3AC

9 rows selected.

SQL> select id,name,f_decrypt_number(EN_NAME) de_name,en_name from  xifenfei_crypto;

        ID NAME                 DE_NAME                        EN_NAME
---------- -------------------- ------------------------------ --------------------------------------------------
        20 ICOL$                ICOL$                          FE17B031331839A9
        46 I_USER1              I_USER1                        FEF96765B1E2C53C
        28 CON$                 CON$                           0283FCE900ACED5C
        15 UNDO$                UNDO$                          20DD92762F199436
        29 C_COBJ#              C_COBJ#                        A0CB43E2EA6BA889
         3 I_OBJ#               I_OBJ#                         F2DE1B9C8A39AA3D
        25 PROXY_ROLE_DATA$     PROXY_ROLE_DATA$               62B99C02EBD4B250311E4490207FEF18CBD8CD8FBA1BFD81
        41 I_IND1               I_IND1                         3F4C3C186F8E2F52
        54 I_CDEF2              I_CDEF2                        CA23D202802BD3AC

9 rows selected.
发表在 Oracle 安全 | 标签为 , , , | 评论关闭