Solved

Example of DBMS_CRYPTO.ENCRYPT_AES256

Posted on 2010-09-02
6
5,036 Views
Last Modified: 2013-12-07
Hello,

I am currently using DBMS_OBFUSCATION_TOOLKIT.MD5 for encryption and decryption. I want to impliment DBMS_CRYPTO.ENCRYPT_AES256, but I'm unsure how to do so. I'm unable to find appropriate examples online.

I currently have a package that entales a function that gets the hash, a procedure to add user, change_password, validate user, and to select a user. I want to use the same type of setup and the less change the better.

My DBMS_OBFUSCATION_TOOLKIT is located within my get_hash function.

Please help

Thank you
This is located within my package body:



FUNCTION get_hash (username  IN  VARCHAR2,

                     password  IN  VARCHAR2)

    RETURN VARCHAR2 AS

  BEGIN

    RETURN DBMS_OBFUSCATION_TOOLKIT.MD5(

      input_string => password);

  END get_hash;

Open in new window

0
Comment
Question by:lulubell-b
  • 3
  • 2
6 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 33593862
I have found one example on AskTom, I hope it can help you:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2886797089063
0
 

Author Comment

by:lulubell-b
ID: 33596436
I dont want something with dbms_obfuscation_toolkit.DESDecrypt. I need examples of DBMS_CRYPTO.ENCRYPT_AES256.
0
 
LVL 22

Accepted Solution

by:
Helena Marková earned 500 total points
ID: 33608948
I have thought that this example from December 9, 2009 - 12am Central time zone could be useful (I have copied it):

DECLARE
  2     input_string       VARCHAR2 (200) :=  'Secret Message';
  3     output_string      VARCHAR2 (200);
  4     encrypted_raw      RAW (2000);             -- stores encrypted binary text
  5     decrypted_raw      RAW (2000);             -- stores decrypted binary text
  6     num_key_bytes      NUMBER := 256/8;        -- key length 256 bits (32 bytes)
  7     key_bytes_raw      RAW (32);               -- stores 256-bit encryption key
  8     encryption_type    PLS_INTEGER :=          -- total encryption type
  9                              DBMS_CRYPTO.ENCRYPT_AES256
 10                            + DBMS_CRYPTO.CHAIN_CBC
 11                            + DBMS_CRYPTO.PAD_PKCS5;
 12  BEGIN
 13     DBMS_OUTPUT.PUT_LINE ( 'Original string: ' || input_string);
 14     key_bytes_raw := UTL_I18N.STRING_TO_RAW ( 'passwordpasswordpasswordpassword' );
 15     encrypted_raw := DBMS_CRYPTO.ENCRYPT
 16        (
 17           src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8'),
 18           typ => encryption_type,
 19           key => key_bytes_raw
 20        );
 21      -- The encrypted value "encrypted_raw" can be used here
 22   DBMS_OUTPUT.PUT_LINE ( 'Encrypted string: ' || encrypted_raw);
 23     decrypted_raw := DBMS_CRYPTO.DECRYPT
 24        (
 25           src => encrypted_raw,
 26           typ => encryption_type,
 27           key => key_bytes_raw
 28        );
 29     output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');
 30
 31     DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || output_string);
 32  END;
 33  /
Original string: Secret Message
Encrypted string: E13D59102175696EFD2C1B0A7064FB47
Decrypted string: Secret Message
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:lulubell-b
ID: 33622130
OK, I was able to get the data into the encrypted data into the table. Now I want to be able to decrypt the data. Please see code.

I'm recieving the below errors
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
ORA-06512: at "CDB.AP_PASSWORD_ENTRY_DECRYPT", line 24
ORA-06512: at line 7

Thanks
create or replace

PROCEDURE              AP_PASSWORD_ENTRY_DECRYPT

(PARAM_1 IN NUMBER, 

 PARAM_2 OUT VARCHAR2

)

AS

 input_string       VARCHAR2 (200) :=  PARAM_2;

 output_string      VARCHAR2 (200);

 encrypted_raw      RAW (2000);             -- stores encrypted binary text

 decrypted_raw      RAW (2000);             -- stores decrypted binary text

 num_key_bytes      NUMBER := 256/8;        -- key length 256 bits (32 bytes)

 key_bytes_raw      RAW (32);               -- stores 256-bit encryption key

 encryption_type    PLS_INTEGER :=          -- total encryption type

                          DBMS_CRYPTO.ENCRYPT_AES256

                          + DBMS_CRYPTO.CHAIN_CBC

                          + DBMS_CRYPTO.PAD_PKCS5;

  p_username varchar2(200);

  p_password RAW(2000);

  

 BEGIN

   SELECT USERNAME, PASSWORD INTO p_username, p_password from USERS where ID = PARAM_1;

 

  key_bytes_raw := UTL_I18N.STRING_TO_RAW ( 'passwordpasswordpasswordpassword' );



  decrypted_raw := SYS.DBMS_CRYPTO.DECRYPT

  (

     src => UTL_I18N.RAW_TO_CHAR (p_password,  'AL32UTF8'),

     typ => encryption_type,

     key => key_bytes_raw

  );

  PARAM_2 := decrypted_raw;



END AP_PASSWORD_ENTRY_DECRYPT;

Open in new window

0
 

Author Comment

by:lulubell-b
ID: 33622213
nevermind I figured it out. Thank you

0
 

Expert Comment

by:DALASSI
ID: 38710905
Hi,

I am using Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production. I can execute the following sql statement:

INSERT INTO HR.ENCRYPTED_EMP VALUES(2,
DBMS_CRYPTO.ENCRYPT(UTL_RAW.CAST_TO_RAW('700'),1 + 256 + 4096,UTL_RAW.cast_to_raw('password')),
DBMS_CRYPTO.ENCRYPT(UTL_RAW.CAST_TO_RAW('SomeNameHere'),1 + 256 + 4096,UTL_RAW.cast_to_raw('password')),
'Address location etc.', null, 'M')

-- I got these magic numebrs above (1+256+4096) from another question posted on this forum.

However, when I try to substitute the 1 + 256 + 4096 which stands for DES + CBC + NoPadding with AES256 + CBC + PKCS5Padding, Oracle complains about the following Static values as invalid procedures:

DBMS_CRYPTO.ENCRYPT_AES256 (supposed to be integer)
DBMS_CRYPTO.CHAIN_CBC (integer)
DBMS_CRYPTO.PAD_PKCS5

These Static value are mentioned and recommended in the  Oracle documentation

Is the Express version of my Oracle the problem? Or something else is wrong?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now