Oracle through the DBMS

Let's take a look at Oracle's encryption of table-sensitive fields through the Dbms_crypto package, a friend who needs to know.

In the security more and more attention to the near body, we often need to a table in the Database sensitive column data (bank card, ID number, amount, etc.) to encrypt, way data leakage, in the 11.2.0.4 can be implemented through the Dbms_crypto package, to increase the encryption efficiency of Oracle, This article provides the processing idea, other may according to the demand to play heartily
Database version
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> Create cryptographic functions </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.
Testing cryptographic functions
Sql> Select F_encrypt_number (' Wwww.111cn.net ') from dual;

F_encrypt_number (' wwww.111cn.net ')
--------------------------------------------------------------------------------
003cb89cb77f6644c93ae2cf7810b0e3e3b10b8c60b54058
To create a decryption function
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,
Ten Typ => dbms_crypto. DES_CBC_PKCS5,
One key => Key_raw
12);
Return utl_i18n. Raw_to_char (Decrypted_raw, ' Al32utf8 ');
End;
15/

Function created.
Test decryption function
Sql> Select F_decrypt_number (' 003cb89cb77f6644c93ae2cf7810b0e3e3b10b8c60b54058 ') from dual;

F_decrypt_number (' 003cb89cb77f6644c93ae2cf7810b0e3e3b10b8c60b54058 ')
--------------------------------------------------------------------------------
Wwww.111cn.net
Create a table synthesis test
Sql> CREATE TABLE Xifenfei_crypto
2 (ID number, name varchar2 (), 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
---------- -------------------- ------------------------------
icol$
I_user1
con$
undo$
c_cobj#
3 i_obj#
proxy_role_data$
I_ind1
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
---------- -------------------- --------------------------------------------------
icol$ fe17b031331839a9
I_user1 fef96765b1e2c53c
con$ 0283fce900aced5c
undo$ 20dd92762f199436
c_cobj# a0cb43e2ea6ba889
3 i_obj# F2de1b9c8a39aa3d
proxy_role_data$ 62b99c02ebd4b250311e4490207fef18cbd8cd8fba1bfd81
I_ind1 3f4c3c186f8e2f52
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
---------- -------------------- ------------------------------ --------------------------------------------------
icol$ icol$ fe17b031331839a9
I_user1 I_user1 fef96765b1e2c53c
con$ con$ 0283fce900aced5c
undo$ undo$ 20dd92762f199436
c_cobj# c_cobj# a0cb43e2ea6ba889
3 i_obj# i_obj# F2de1b9c8a39aa3d
proxy_role_data$ proxy_role_data$ 62b99c02ebd4b250311e4490207fef18cbd8cd8fba1bfd81
I_ind1 I_ind1 3f4c3c186f8e2f52
I_cdef2 I_cdef2 Ca23d202802bd3ac

9 rows selected.