How to Use Dbms_Crypto. Encrypt Function in Oracle
I Want to Encrypt Password Column in the Database and I Am Trying to Use Encrypt Function Present Inside Dbms_Crypto Package (Have Given Execute Access from...
I want to encrypt password column in the database and I am trying to use encrypt function present inside DBMS_CRYPTO package (have given execute access from sys account to the current user) but I am getting the following error.
Please give me some examples of how to use this function:
select DBMS_CRYPTO.encrypt('12345', dbms_crypto.DES_CBC_PKCS5, 'A1') from dual;
Error:
ORA-06553: PLS-221: 'DES_CBC_PKCS5' is not a procedure or is undefined 06553. 00000 - "PLS-%s: %s"
3 Answers
The constant dbms_crypto.DES_CBC_PKCS5 is referenceble on PL/SQL only - not from SQL.
You must replace it with literal value in the SELECT statement.
To get the value use PL/SQL block
begin
dbms_output.put_line(dbms_crypto.DES_CBC_PKCS5);
end;
/
.
4353
You must also use a longer key
select DBMS_CRYPTO.encrypt(UTL_RAW.CAST_TO_RAW ('ABCDEFGH12345'), 4353 /* = dbms_crypto.DES_CBC_PKCS5 */, 'A1A2A3A4A5A6CAFE') from dual;
9320CBCBD25E8721BD04990A0EAEAF00
Encrypt
select DBMS_CRYPTO.encrypt(UTL_RAW.CAST_TO_RAW('ABCDEFGH12345'), 4353 /* = dbms_crypto.DES_CBC_PKCS5 */, UTL_RAW.CAST_TO_RAW ('A1A2A3A4A5A6CAFE')) from dual;
Decrypt
select UTL_RAW.CAST_TO_varchar2(DBMS_CRYPTO.decrypt('80AA4DEA59B77C433A2142AE9CDD235A', 4353, UTL_RAW.CAST_TO_RAW ('A1A2A3A4A5A6CAFE'))) from dual;
The answer above is fine, I add just some information about 4353. I saw this request used as is and I noticed this value is not really understood.
4353 is an addition of three informations about encryption used (des, aes and so on), block cipher mode of operation (ecb or cbc) and padding mode.
So, 4353 stands for 1 (des) + cbc mode (256) + pkcs5 padding (4096)
If you prefer aes 256, you have to use 4356
4358 stands for aes 128
and so on.
The Oracle page which describes the different parameters is here.
Hope this additional information helps everyone understand better DBMS_CRYPTO.
Robert Thorne covers electric vehicle innovations, autonomous driving systems, global mobility trends, and automotive engineering developments.
