Data Encryption and Decryption in Oracle

1)DBMS_OBFUSCATION_TOOLKIT package:

Encryption Function:

SQL> CREATE OR REPLACE FUNCTION encrypt (p_text IN VARCHAR2, p_key VARCHAR2 )
 RETURN RAW
 IS
 lc_text VARCHAR2(32767) := p_text;
 lr_key RAW(255) := UTL_RAW.cast_to_raw(p_key);
 lt_enc_text RAW(32767);
BEGIN
lc_text := RPAD( lc_text, (TRUNC(LENGTH(lc_text)/8)+1)*8, CHR(0) );
DBMS_OBFUSCATION_TOOLKIT.desencrypt(input => UTL_RAW.cast_to_raw(lc_text),
                                      key => lr_key,
                           encrypted_data => lt_enc_text);
RETURN lt_enc_text;
END;
 /

Function created.

SQL> SELECT ENCRYPT('HELLO WORLD !','1234567890') from dual;

ENCRYPT('HELLOWORLD!','1234567890')
----------------------------------------------
F9D853C25CAB740D930D7DCA709D0994

SQL>

Decryption Function:

SQL> CREATE OR REPLACE FUNCTION decrypt (p_raw IN RAW, p_key VARCHAR2 )
RETURN VARCHAR2 IS
 lc_decrypted VARCHAR2(32767);
 lc_return_dec VARCHAR2(32767);
 lr_key RAW(255) := UTL_RAW.cast_to_raw(p_key);
 BEGIN
  DBMS_OBFUSCATION_TOOLKIT.desdecrypt(input => p_raw,
                                        key => lr_key,
                             decrypted_data => lc_decrypted);
   lc_return_dec := UTL_RAW.cast_to_varchar2(lc_decrypted);
  RETURN RTRIM( lc_return_dec, CHR(0) );
END; 
/

Function created.


SQL> SELECT decrypt('F9D853C25CAB740D930D7DCA709D0994','1234567890')
     FROM   dual;

DECRYPT('F9D853C25CAB740D930D7DCA709D0994','1234567890')
--------------------------------------------------------------
HELLO WORLD !

2) DBMS_CRYPTO Package:

set serveroutput on;
DECLARE
  l_text VARCHAR2(32767) :='1234-5678-9012-3456';
  l_key RAW(128) := UTL_I18N.STRING_TO_RAW('0123456789', 'AL32UTF8');
  l_encrypted_raw RAW(2048);
  l_decrypted_raw RAW(2048);
BEGIN
  dbms_output.put_line('Original : ' || l_text );
  l_encrypted_raw := dbms_crypto.encrypt(
            src =>  UTL_I18N.STRING_TO_RAW (l_text , 'AL32UTF8'),
            typ =>  dbms_crypto.des_cbc_pkcs5,
            key =>  l_key
                                        );
dbms_output.put_line('Encrypted ( CHAR ) : ' ||
                UTL_I18N.RAW_TO_CHAR(l_encrypted_raw, 'AL32UTF8'));
 
dbms_output.put_line('Encrypted ( HEX ) : ' ||
                RAWTOHEX(l_encrypted_raw));
 
 l_decrypted_raw := dbms_crypto.decrypt(
                                src => l_encrypted_raw,
                                typ => dbms_crypto.des_cbc_pkcs5,
                                key => l_key);

 dbms_output.put_line('Decrypted : '|| 
                UTL_I18N.RAW_to_CHAR(l_decrypted_raw,'AL32UTF8'));
 END;

   /

Original : 1234-5678-9012-3456
Encrypted ( CHAR ) : .n?k.z????L??E??AmdM
Encrypted ( HEX ) : 2E1F6EAE186B2E7A9DBCFA834C3FBD45A6B0C3806D64044D
Decrypted : 1234-5678-9012-3456

PL/SQL procedure successfully completed.

DBMS_CRYPTO Block Cipher Suites

NameDescription
DES_CBC_PKCS5ENCRYPT_DES + CHAIN_CBC+ PAD_PKCS5
DES3_CBC_PKCS5ENCRYPT_3DES + CHAIN_CBC + PAD_PKCS5

Cryptographic Hash Functions in Oracle

1) ORA_HASH() Function.

ORA_HASH( expr, max_bucket, seed_value)

  • The optional max_bucket argument determines the maximum bucket value returned by the hash function. You can specify any value between 0 and 4294967295. The default is 4294967295.
  • The optional seed_value argument enables Oracle to produce many different results for the same set of data. Oracle applies the hash function to the combination of expr and seed_value. You can specify any value between 0 and 4294967295. The default is 0.

ORA_HASH function returns a NUMBER value.

SQL>  select ora_hash('Hello World !') from dual;

ORA_HASH('HELLOWORLD!')
-----------------------
             3256043700

2) STANDARD_HASH ( expr, ‘method’), which is available from 12.1 onward.

Valid method algorithms are SHA1SHA256SHA384SHA512, and MD5. If you omit this argument, then SHA1 is used.

SQL> select STANDARD_HASH ( 'Hello World !') from dual;

STANDARD_HASH('HELLOWORLD!')
----------------------------------------
F95BE4341EDDB92DDE5F14D045978F9A4EC6C1A6

SQL> select STANDARD_HASH ( 'Hello World !', 'SHA1') from dual;

STANDARD_HASH('HELLOWORLD!','SHA1')
----------------------------------------
F95BE4341EDDB92DDE5F14D045978F9A4EC6C1A6

SQL> select STANDARD_HASH ( 'Hello World !', 'MD5') from dual;

STANDARD_HASH('HELLOWORLD!','MD5
--------------------------------
B9BE3EF4018BE19F248F6F8E63B9E006

SQL> select STANDARD_HASH ( 'Hello World !', 'SHA256') from dual;

STANDARD_HASH('HELLOWORLD!','SHA256')
----------------------------------------------------------------
07F2BDEF34ED16E3A1BA0DBB7E47B8FD981CE0CCB3E1BFE564D82C423CBA7E47

SQL> select STANDARD_HASH ( 'Hello World !', 'SHA384') from dual;

STANDARD_HASH('HELLOWORLD!','SHA384')
--------------------------------------------------------------------------------
67E60F9CE837CAA3CA82550F0DFCBDE1B8B8A7C1605FA8D115BCC2314204FD95F5F607306622C38C
0205DE7DF6D426D8


SQL> select STANDARD_HASH ( 'Hello World !', 'SHA512') from dual;

STANDARD_HASH('HELLOWORLD!','SHA512')
--------------------------------------------------------------------------------
FEAB0028F1142D420A1425D1DD5B518225B4523AA1CFF63385ECE3411318819F5EC83042CCB79D81
F20E4A243866886CA3AE3026153ACFF8E126C0E89631502E

3) DBMS_OBFUSCATION_TOOLKIT.MD5() function.

SQL> CREATE OR REPLACE FUNCTION toolkit_md5 (v_input VARCHAR2)
RETURN dbms_obfuscation_toolkit.varchar2_checksum
AS
BEGIN
      RETURN dbms_obfuscation_toolkit.md5(input_string => v_input);
END toolkit_md5;
/

Function created.

SQL> select
RAWTOHEX( UTL_RAW.CAST_TO_RAW( toolkit_md5('Hello World !'))) from dual;

RAWTOHEX(UTL_RAW.CAST_TO_RAW(TOOLKIT_MD5('HELLOWORLD!')))
--------------------------------------------------------------------------------
B9BE3EF4018BE19F248F6F8E63B9E006

4) DBMS_CRYPTO.HASH() function.

 The predefined constants HASH_MD4, HASH_MD5 and HASH_SH1 were already available in previous Oracle releases. In Oracle 12c, three additional constants for different lengths of SHA-2 (256, 384 and 512 bits) were added:
HASH_MD4   CONSTANT PLS_INTEGER := 1;
HASH_MD5   CONSTANT PLS_INTEGER := 2;
HASH_SH1   CONSTANT PLS_INTEGER := 3;
HASH_SH256 CONSTANT PLS_INTEGER := 4;
HASH_SH384 CONSTANT PLS_INTEGER := 5;
HASH_SH512 CONSTANT PLS_INTEGER := 6;
CREATE OR REPLACE 
FUNCTION CRYPTO_HASH (v_input VARCHAR2, v_hash_typ BINARY_INTEGER) 
RETURN RAW DETERMINISTIC
AS
   PRAGMA UDF;
BEGIN
   RETURN dbms_crypto.hash(utl_raw.cast_to_raw(v_input), v_hash_typ);
END CRYPTO_HASH ;
/

Function created.

-- MD4
--
SQL>  select CRYPTO_HASH('Hello World !',1) from dual;

CRYPTO_HASH('HELLOWORLD!',1)
--------------------------------------------------------------------------------
BBA87E44C59377A32F26033CC0A9203B


-- MD5
-- 
SQL>  select CRYPTO_HASH('Hello World !',2) from dual;

CRYPTO_HASH('HELLOWORLD!',2)
--------------------------------------------------------------------------------
B9BE3EF4018BE19F248F6F8E63B9E006

-- SH1
SQL>  select CRYPTO_HASH('Hello World !',3) from dual;

CRYPTO_HASH('HELLOWORLD!',3)
--------------------------------------------------------------------------------
F95BE4341EDDB92DDE5F14D045978F9A4EC6C1A6

-- SH256
--

SQL> select CRYPTO_HASH('Hello World !',4) from dual;

CRYPTO_HASH('HELLOWORLD!',4)
--------------------------------------------------------------------------------
07F2BDEF34ED16E3A1BA0DBB7E47B8FD981CE0CCB3E1BFE564D82C423CBA7E47

-- SH384
--

SQL>  select CRYPTO_HASH('Hello World !',5) from dual;

CRYPTO_HASH('HELLOWORLD!',5)
--------------------------------------------------------------------------------
67E60F9CE837CAA3CA82550F0DFCBDE1B8B8A7C1605FA8D115BCC2314204FD95F5F607306622C38C
0205DE7DF6D426D8

-- SH512
--

SQL>  select CRYPTO_HASH('Hello World !',6) from dual;

CRYPTO_HASH('HELLOWORLD!',6)
--------------------------------------------------------------------------------
FEAB0028F1142D420A1425D1DD5B518225B4523AA1CFF63385ECE3411318819F5EC83042CCB79D81
F20E4A243866886CA3AE3026153ACFF8E126C0E89631502E

How do I change my Oracle user password ?

c:\>
C:\>sqlplus testuser/Password@//ractest-scan.ractest.local:1521/service

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 31 11:44:13 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options


SQL> password

Changing password for TESTUSER
Old password:
New password:
Retype new password:
Password changed

SQL>

ORA-24247: network access denied by access control list (ACL)

While executing PL/SQL procedure to send mails by using UTL_SMTP, it fails with error “ORA-24247” in 11g databases.

SOLUTION

ACL needs to be created with following instructions.

DECLARE
acl_path VARCHAR2(4000);
BEGIN
SELECT acl INTO acl_path FROM dba_network_acls
WHERE host = '*' AND lower_port IS NULL AND upper_port IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,'SCOTT','connect')
IS NULL
THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'SCOTT', TRUE, 'connect');
END IF;
EXCEPTION
WHEN no_data_found THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('utl_mail.xml','ACL description', 'SCOTT', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('utl_mail.xml','*');
END;

COMMIT;
SQL> select ACL,PRINCIPAL,PRIVILEGE,IS_GRANT 
from DBA_NETWORK_ACL_PRIVILEGES;

ACL PRINCIPAL PRIVILEGE IS_GRANT
---------------------- ---------- --------- --------------------
/sys/acls/utl_mail.xml SCOTT connect true

ORA-24247 Trying To Send Email Using UTL_SMTP from 11gR1 (11.1.0.6) or higher (Doc ID 557070.1)