Wednesday, April 13, 2022

DBMS CRYPTO is a PLSQL function that allows you to encrypt or decrypt sensitive data

     No comments


Transparent Data Encryption was introduced in Oracle 10g, and it is concerned with storing data in data files that are physically encrypted. The data is always created and retrieved by the users in plain text format. Users give plain text when data is generated, but Oracle automatically turns that plain text into encrypted form and saves it in the data files, and whenever users access that data, Oracle decrypts the data and displays it to the users, as described in the next section. The users have total transparency into the encryption and decryption processes. They are completely unaware that the data has been encrypted. The whole purpose of transparent encryption is to keep the sensitive information contained inside the data files secure.

Encrypting sensitive data for users or apps is very different from Transparent Data Encryption and has no resemblance to it. If you want a particular column in the table to be encrypted when it is shown to the users, you will have to encrypt it manually at the time of data entry. And if you want to make use of that information, you'll have to decode it first. For example, Oracle includes utilities to execute encryption and decryption. For example, DBMS OBFUSCATION KIT is available in Oracle 8i and 9i. This package was deprecated in favor of DBMS CRYPTO in Oracle 10g, which is simpler to use and has a greater number of cryptographic methods.

This tutorial will make use of a "users" table that has the field "password." When the password field is queried, it is expected to provide an encrypted value; however, when the application requests the true password value (decrypted), the field should return the genuine password value (decrypted).

$ sqlplus scott/tiger

 

CREATE TABLE users (

   userid       NUMBER,

   username     VARCHAR2(30),

   userlocation VARCHAR2(30),

   password     VARCHAR2(200),

   CONSTRAINT users_pk PRIMARY KEY (userid)

);

 

insert into users

values (1,'JAMES','TEXAS','james123');

 

insert into users

values (2,'JONES','TEXAS','jones001');

 

insert into users

values (3,'ALLEN','TEXAS','allen789');

 

commit;

 

exit;

 

We just had to build the database and a few plain text passwords to get started. Develop an encryption/decryption method for the password field at this point in time.

 

$ sqlplus / as sysdba
 
CREATE OR REPLACE PACKAGE enc_dec
AS
   FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC;
   FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC;
END;
/
 
CREATE OR REPLACE PACKAGE BODY enc_dec
AS
     encryption_type    PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES
                                     + DBMS_CRYPTO.CHAIN_CBC
                                     + DBMS_CRYPTO.PAD_PKCS5;
     /*
       ENCRYPT_DES is the encryption algorithem. Data Encryption Standard. Block cipher. 
       Uses key length of 56 bits.
       CHAIN_CBC Cipher Block Chaining. Plaintext is XORed with the previous ciphertext 
       block before it is encrypted.
       PAD_PKCS5 Provides padding which complies with the PKCS #5: Password-Based 
       Cryptography Standard
     */
     encryption_key     RAW (32) := UTL_RAW.cast_to_raw('MyEncryptionKey');
     -- The encryption key for DES algorithem, should be 8 bytes or more.
 
     FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC
     IS
        encrypted_raw      RAW (2000);
     BEGIN
        encrypted_raw := DBMS_CRYPTO.ENCRYPT
        (
           src => UTL_RAW.CAST_TO_RAW (p_plainText),
           typ => encryption_type,
           key => encryption_key
        );
       RETURN encrypted_raw;
     END encrypt;
     FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC
     IS
        decrypted_raw      RAW (2000);
     BEGIN
        decrypted_raw := DBMS_CRYPTO.DECRYPT
        (
            src => p_encryptedText,
            typ => encryption_type,
            key => encryption_key
        );
        RETURN (UTL_RAW.CAST_TO_VARCHAR2 (decrypted_raw));
     END decrypt;
END;
/
 
grant execute on enc_dec to scott;
create public synonym enc_dec for sys.enc_dec;
 
exit;
 

In order to provide the same results for the same input parameters, the functions "encrypt" and "decrypt" must use the same encryption technique and key. For instance, if you pass in the value ABC, the function will always return the same encrypted value. As a result, it makes a great deal of sense to write these functions in deterministic form. Once a deterministic function has been created and is being executed a second time against the same input parameters, Oracle does not actually execute the function the second time, but instead uses the results of its previous execution against the same input parameters, resulting in a significant increase in overall performance.

Due to the fact that the encryption or decryption of VARCHAR2 does not function directly with DBMS CRYPTO, I changed it to RAW before encrypting it.

See the following link for additional information on cryptographic algorithms: DBMS CRYPTO Algorithms.

 
$ sqlplus scott/tiger
 
select enc_dec.encrypt('Hello World') encrypted 
from dual;
 
ENCRYPTED
----------------------------------
89738046FA0CFDD2581198FBF98DE2C5
 
/* A simple value encrypted using the package we just created. */
 
select enc_dec.decrypt('89738046FA0CFDD2581198FBF98DE2C5') decrypted
from dual;
 
DECRYPTED
------------------
Hello World
 
/* The same value decrypted using the package we just created. */
 
column username format a10
column userlocation format a10
column password format a10
select * from users;
 
    USERID USERNAME   USERLOCATI PASSWORD
---------- ---------- ---------- ----------
         1 JAMES      TEXAS      james123
         2 JONES      TEXAS      jones001
         3 ALLEN      TEXAS      allen789
 
/* We can see Password data in plain text from above. */
 
SQL> update users
  2  set password = enc_dec.encrypt (password);
 
3 rows updated.
 
SQL> commit;
 
Commit complete.
 
/* 
   We just encrypted the password data using the algorithm and key specified in the 
   package ENC_DEC.
   We also need to make sure any newly created record has Password value encrypted using 
   the package ENC_DEC.
*/
 
column password format a32
select * from users;
 
    USERID USERNAME   USERLOCATI PASSWORD
---------- ---------- ---------- --------------------------------
         1 JAMES      TEXAS      D705C2186A64B1A6FF3B6E6220746731
         2 JONES      TEXAS      98DDCC4DAB5F13140C8D657D381E05FC
         3 ALLEN      TEXAS      D9A656AD83B7ADC7443D6BECD173715E
 
/* All existing passwords are now encrypted */
 
SQL> insert into users
  2  values (4,'SCOTT','TEXAS',enc_dec.encrypt('scott456'));
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from users;
 
    USERID USERNAME   USERLOCATI PASSWORD
---------- ---------- ---------- --------------------------------
         1 JAMES      TEXAS      D705C2186A64B1A6FF3B6E6220746731
         2 JONES      TEXAS      98DDCC4DAB5F13140C8D657D381E05FC
         3 ALLEN      TEXAS      D9A656AD83B7ADC7443D6BECD173715E
         4 SCOTT      TEXAS      41D69256E23E7A3D2AFEFF2E5C082FFD
 
/* The newly created record of user SCOTT has its password encrypted. */
 
column decrypted_password format a10
column encrypted_password format a32
select username ,                                              
       enc_dec.decrypt (password) decrypted_password,
       password encrypted_password
from users;
 
USERNAME   DECRYPTED_ ENCRYPTED_PASSWORD
---------- ---------- --------------------------------
JAMES      james123   D705C2186A64B1A6FF3B6E6220746731
JONES      jones001   98DDCC4DAB5F13140C8D657D381E05FC
ALLEN      allen789   D9A656AD83B7ADC7443D6BECD173715E
SCOTT      scott456   41D69256E23E7A3D2AFEFF2E5C082FFD
 
SQL> grant select on users to hr;
 
Grant succeeded.
 

Users who have EXECUTE access to the ENC DEC package are the only ones who can view the encrypted values contained inside the package. The data can only be decrypted using the same key and algorithm that was used to encrypt it in the first place. Since all of these password values must be decrypted using the KEY and ALGORITHEM given in the package ENC DEC, there is no other way to decode them.

HR has been allowed access to the SELECT function on the table "users." Check out the results of his querying of data from users' tables to see what he discovers.

 
SQL> conn hr/hr
Connected.
column username format a10
column userlocation format a10
column password format a32
select * from scott.users;
 
    USERID USERNAME   USERLOCATI PASSWORD
---------- ---------- ---------- --------------------------------
         1 JAMES      TEXAS      D705C2186A64B1A6FF3B6E6220746731
         2 JONES      TEXAS      98DDCC4DAB5F13140C8D657D381E05FC
         3 ALLEN      TEXAS      D9A656AD83B7ADC7443D6BECD173715E
         4 SCOTT      TEXAS      41D69256E23E7A3D2AFEFF2E5C082FFD
 
SQL> select enc_dec.decrypt(password) from scott.users;
select enc_dec.decrypt(password) from scott.users
       *
ERROR at line 1:
ORA-00904: : invalid identifier
 
 
SQL> desc enc_dec
ERROR:
ORA-04043: object "SYS"."ENC_DEC" does not exist
 
 

Because the user HR does not have access to the ENC DEC package, he will not be able to see the encrypted data.

Keep your encrypted data secure from hackers by using a password.

Keep your encryption algorithm and key secret is the most important thing you can do. If they are made public, anybody may decode your encrypted data and see it in its entirety.. When using the ENC DEC package, the key and algorithm are both kept inside the ENC DEC package.

 
 
encryption_key     RAW (32) := UTL_RAW.cast_to_raw('MyEncryptionKey');
encryption_type    PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES
                                + DBMS_CRYPTO.CHAIN_CBC
                                + DBMS_CRYPTO.PAD_PKCS5;
 
 

Anyone with DBA credentials has the ability to see the source code of the package and leak out the encryption technique and key used by the package. Before we can generate or ship the package with the application, we must wrap the code in order to conceal the content that is in the code. Here's how to encapsulate your PL/SQL code so that it is not visible to users.

I placed the CREATE PACKAGE statements in a file called create enc dec package.sql and then ran the wrap tool to wrap the code into a new file named create enc dec package.wrp. I then ran the wrap utility to wrap the code into a new file named create enc dec package.wrp.

 
$ wrap iname=create_enc_dec_package.sql oname=create_enc_dec_package.wrp
 
PL/SQL Wrapper: Release 10.2.0.1.0- Production on Sun Aug 15 06:44:32 2010
 
Copyright (c) 1993, 2004, Oracle.  All rights reserved.
 
Processing create_enc_dec_package.sql to create_enc_dec_package.wrp
 
 

Check out the contents of this new file create enc dec package.wrp to see what it contains.

 
$ more create_enc_dec_package.wrp
CREATE OR REPLACE PACKAGE enc_dec wrapped 
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
9
a6 b2
ceYtyd1wwstfJ/3xbNlo4sobVxYwg0xHAMusaS/pOPYrUgzeeSYbTuJ789ScOKWw4LWYL191
ERLxTlyzbW7nRf8Cg4W0plfc4t7qD8d69uAPwYNtQpv3U6F9kwZQZnVeV+a5FlnUcEgL7J8k
hQZIhcYLQoTZ/irf0ixRnEj+4VqG1c4=
 
/
CREATE OR REPLACE PACKAGE BODY enc_dec wrapped 
a000000
1
abcd
abcd
abcd
abcd
abcd
 
 
The wrap tool has, in fact, encrypted the PL/SQL code contained inside the.sql file, rendering it illegible to anybody else. To create the package, open the create enc dec package.wrp file and double-click it. Keep your.sql source file safe since you will need it later on if you wish to make modifications to the package, which will require you to make changes to the.sql file, wrap it again, and construct the package from scratch.
 
$ sqlplus / as sysdba
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
SQL> @create_enc_dec_package.wrp
 
Package created.
 
 
Package body created.
 
SQL> column text format a70
SQL> select text from dba_source where name = 'ENC_DEC';
 
TEXT
----------------------------------------------------------------------
PACKAGE enc_dec wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
9
a6 b2
ceYtyd1wwstfJ/3xbNlo4sobVxYwg0xHAMusaS/pOPYrUgzeeSYbTuJ789ScOKWw4LWYL1
91
ERLxTlyzbW7nRf8Cg4W0plfc4t7qD8d69uAPwYNtQpv3U6F9kwZQZnVeV+a5FlnUcEgL7J
8k
hQZIhcYLQoTZ/irf0ixRnEj+4VqG1c4=
 
PACKAGE BODY enc_dec wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
40a 1de
e5uq2Fhk2UgpK5ktxFEVOrE3zyowg5DxLdxqfC9AWE6OGeznw1zpVqLpwIrvVN522Z83WR
wv
HQw142Mg0KQxSHaso6WOT7ud5P5VvVmrcR3le4Pvj9tpyogriMDGGQGWIR5T3g4s5tMka+
Qj
TA4FsoMpOy3+bK/y/VW+u8+zHHC1m0LOziMSmnhkB+nM+U1jEvvRFGGXfOJrOSmXs+VcyV
r8
pyIFRQgr3JDZotwcfIZAw10k4Dcm87LMeBk6c0q2wdqgqcA422/awXKrAODetRti870jST
pn
46w5MWX/ickZHdrfBh6mMttQ8x4jDaNEcZR3X7VRdReUt05S6/LToL4T/VwlYFIqbzH7rb
OR
kaEYBQchlWDg5n3hRBahHVLvEeOuoQVsdBqMwA55PfP1yqqsYWSBW4Mm4OYFJP/ry1NJYb
bA
wVAA/SBw965bdu5doXjpf6y7D5dHh5dtIOL9uUA=
 
SQL>
 

Because the package source code is unreadable by anybody, even the package owner, when it is created from a sql script that has been wrapped, it is recommended that you use this method. We will be able to entirely conceal the encryption logic from everyone in this manner.

Download

 Download script to create Encryprion/Decryprion package
I hope it's helpful for you. If you have any queries, don't hesitate to contact me.
Name : Muhammad Sahal Qasim
E-mail : s.m.sahal789@gmail.com
Thank you.

0 comments:

Post a Comment

If you have any doubts, please let me know. I will help you.