Oracle10.2.0.1 Data Encryption

This is my first blog in csdn and I hope to help anyone in need.

I want to introduce the data encryption processing of Oracle10g, that is, the encryption processing of common passwords,

The decrypt method is used to encrypt data decryption and encrypt.

1. First, log on to the Oracle database through the Sys user, because we need to set some advanced permissions through this user.

Grant execute on dbms_crypto to username;

This is to allow users to use the DBMS method, so that we can directly and simply call SQL statements. Otherwise, we will not be able to use encryption and decryption, and the database will report an error, however, I forgot what the error was. username is what you passedProgramThe username used to access the database,

2. Create a table. In this table, a field is used to store a key value. This key value is the fixed value used in the method to be created later.

Create Table keyinfomtbl (
Keycode raw (32) not null,
Constraint keyinfomtbl_p primary key (
Keycode)
Using Index
)
/
Insert into keyinfomtbl values (dbms_crypto.randombytes (32 ))

Use these two SQL statements to create a table and insert a piece of data into the table. Note that the field type is raw, which is also the field type used for Oracle database encryption.

3. It is necessary to establish a method to encrypt and process the data. This is also a key part. I have seen manyArticleThis process is explained, but it is not easy to use after trying a lot. The examples I have provided are completely useful, which is also used in my current project.

Create or replace function Encrypt
(
Input_string varchar2
)
Return raw
Is
Encrypted_raw raw (100 );
Key_bytes_raw raw (32 );
Encryption_type pls_integer: =
Dbms_crypto.encrypt_aes256
+ Dbms_crypto.chain_cbc
+ Dbms_crypto.pad_pkcs5;
Begin
Select keycode into key_bytes_raw from keyinfomtbl;
Encrypted_raw: = dbms_crypto.encrypt
(
Src => utl_i18n.string_to_raw (input_string, 'al32utf8 '),
Typ => encryption_type,
Key => key_bytes_raw
);
Return encrypted_raw;
End;

Why is this method written in this way? I am not very familiar with Oracle experts! Hey

4. Create a decryption method,

Create or replace function decrypt
(
input_raw raw
)
return varchar2
is
output_string varchar2 (100 );
decrypted_raw raw (100);
key_bytes_raw raw (32);
encryption_type pls_integer: =
cipher
+ dbms_crypto.chain_cbc
+ cipher;
begin
select keycode into key_bytes_raw from keyinfomtbl;
decrypted_raw: = dbms_crypto.decrypt
(
src => input_raw,
typ => encryption_type,
key => key_bytes_raw
);
output_string: = encrypt (decrypted_raw, 'al32utf8');

Return output_string;
End;
/
5. Now I want to provide a small example that can apply the two encryption and decryption methods. We can clearly see how to apply the two encryption and decryption methods.

You can use this SQL statement to check the field value stored in the keyinfomtbl table.
Select utl_i18n.raw_to_char (keycode, 'al32utf8') from keyinfomtbl;

Create Table testpw
(
Password raw (20)
)
/

You only need to use encrypt () to encrypt your string when inserting it.

Insert into testpw values (encrypt ('nvgwngw383 $ '));

 

You can view the encrypted data through this SQL statement.

Select utl_i18n.raw_to_char (password, 'al32utf8') from testpw;

 

You can use the following SQL statement to decrypt the data.
Select decrypt (password) from testpw;

Completed