Jul 21, 2017

Encrypt / Decrypt customers sensitive data via custom define procedure and functions

Some times customer request to encrypt his sensitive data and decrypt it back when ever required. This may be the customers security policy.

With the below mentioned tested methodology, when data is inserted, the user actually provides the plain text and oracle automatically converts the plain text into encrypted form and stores it in the data files. Now, whenever users access that data, Oracle decrypts the data and show it to the users. This encryption/decryption is completely transparent to users. So the whole point behind transparent encryption is to keep the sensitive data in data files safe.

Summary of steps:
1.     Create or point out the table with sensitive information (developers)
2.     Create encryption/decryption package (DBA)
3.     Assign permission on package created in step 2 to user/schema with sensitive data (DBA)
4.     Use different DML scenario to encrypt and decrypt info (developers)

STEP 1:
I am using scott/tiger and a table ‘USERS’ with ‘password’ column containing sensitive info

1.    

$ sqlplus scott/tiger
SQL> select * from users;
   USERID USERNAME          PASSWORD
---------- ----------- ------------------------
         1 GOURANGA                  GOURANGA123
         2 JUSTEEN                   JUSTEEN001
         3 HUSSAIN                   HUSSAIN980

STEP 2:

Connect to sysdba user create an encryption/decryption mechanism for the password field.

$ sqlplus / as sysdba
SQL>
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;
/


STEP 3: 
Grant execution permissions on the above mentioned package to user scott.

$ sqlplus / as sysdba
SQL> grant execute on enc_dec to scott;
SQL> create public synonym enc_dec for sys.enc_dec;


Now connect to sqlplus scott/tiger and test out the encryption/decription using following scenarios

CASE 1: 

SQL> select enc_dec.encrypt('Hello World') encrypted from dual;
ENCRYPTED
----------------------------------
43718046FA0CFDD2581198FBF98DE2C5

/* A simple value encrypted using the package we just created. */

select enc_dec.decrypt('43718046FA0CFDD2581198FBF98DE2C5') decrypted
from dual;

DECRYPTED
------------------
Hello World

CASE 2:

select * from users;

   USERID USERNAME                       PASSWORD
---------- ----------------- ----------------------------
         1 GOURANGA                     GOURANGA123
         2 JUSTEEN                      JUSTEEN001
         3 HUSSAIN                      HUSSAIN980

SQL>  update users
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.
*/

SQL> column password format a32
SQL> select * from users;
USERID USERNAME                       PASSWORD
---------- ---------------------- -----------------------
1 GOURANGA                         03077889420F4348EEA75EDA4DA3F088
2 JUSTEEN                          E357A4E178A115FAF254EC08C0F97DE4 
3 HUSSAIN                          67EB9262394146787485B7C51F3E2889

/* All existing passwords are now encrypted */
Now add one more record with encrypted password

SQL> insert into users values (4,'Regis',enc_dec.encrypt('kU_Fi_4383'));

USERID USERNAME                       PASSWORD
---------- ------------------------------ -----------------------------------
         1 GOURANGA                       03077889420F4348EEA75EDA4DA3F088
         2 JUSTEEN                        E357A4E178A115FAF254EC08C0F97DE1
         3 HUSSAIN                        67EB9262394146787485B7C51F3E2889
         4 Regis                          C94F447042C428723B2F97393191DE65

In order to decrypt the password

SQL> select userid,username,enc_dec.decrypt(password) from users;

Note: You can use this policy to encrypt credit card information, bank a/c details, any personal data etc.

 You can test yourself...

How to recover of a dropped tablespace?


This scenario may be unusual, but if this situation will come, then you may feel lots of trouble. But if you have valid backups OR dataguard environments, then you are 100% safe to restore the database.

In this case, the Tablespace Point In Time Recovery (TSPITR) method cannot be used.

When you drop a tablespace, the controlfile will then no longer have any records of the tablespace which has been dropped. Attempts to use the RMAN RECOVER TABLESPACE command will return the 

RMAN error RMAN-06019 – “could not translate tablespace name” as shown below.

e.g, to drop a tablespace,

SQL> drop tablespace SAMPLE  including contents and datafiles;

Tablespace dropped.

When you will try to recover below error will come:

RMAN> restore tablespace SAMPLE;
....
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/07/2017 13:17:45
RMAN-20202: tablespace not found in the recovery catalog

So to recover from a dropped tablespace, we have two options:

1) Do a point in time recovery of the whole database until the time the tablespace was dropped.
2) Create a clone of the database from a valid backup, export the required tables from the tablespace which has been dropped, recreate the tablespace and then import the tables from the clone.

The first option will require an outage of the entire database and the entire database will be rolled back in tine in order to recover the tablespace. The second option can be peformed online, but we will need to factor in the disk space requirements to create a clone of the database from which the tablespace has been dropped.

Let us examine the first option using the example shown below:

In this example, CONTROLFILE AUTOBACKUP has been turned on and Flashback has been enabled for the database.
With Flashback enabled, the db_recovery_file_dest will have a sub-directory ‘autobackup’ as shown below for each day.

When we drop the tablespace we are changing the structure of the database and since controlfile autobackup has been turned on, we see another backup file has been created in the autobackup location in the flash recovery area on disk.

SQL> drop tablespace EXAMPLE including contents and datafiles;

Tablespace dropped.

We then shutdown the database, startup in nomount mode and attempt to restore the controlfile from autobackup.

The most recent controlfile autobackup has been restored, but since this has been taken after the tablespace was dropped, the tablespace which has been dropped (ARUL) is not referenced in the control file that we just restored. If we try to restore and recover the database, the dropped tablespace will not be restored.

SQL> startup nomount;

RMAN> restore controlfile from autobackup;

RMAN> alter database mount;

RMAN> report schema;
-- here you will not fine any EXAMPLE tablespace.


We will need to restore a backup of the controlfile which contains records for the tablespace ARUL. We use the RESTORE CONTROLFILE FROM command to restore a specific controlfile autobackup.

RMAN>  restore controlfile from '/FRA/oracle/testdb/TESTDB/autobackup/20017_07_04/o1_mf_s_893930026_87f0fbo2_.bkp';
  
RMAN> report schema;
.....

-- here you able to see your dropped tablespace. 

The alert log will also show the time when the tablespace was dropped. We can also see that a controlfile autobackup has taken place after the tablespace was dropped.

Now that we know the time the tablespace was dropped, we can do a point in time recovery of the DATABASE in order to recover the tablespace which has been dropped.

RMAN> run {
2> set until time "to_date('04-JUL-2017 13:45:00','DD-MON-YYYY HH24:Mi:SS')";
3> restore database;
4> recover database;
5> }

RMAN> alter database open resetlogs;

database opened

We can now see that the tablespace which has been dropped has been recovered

SQL> select file_name,bytes from dba_data_files where tablespace_name='EXAMPLE';


From Oracle 11g on wards you can do Tablespace point-in-time recovery.

Click here to read about Performing RMAN Tablespace Point-in-Time Recovery (TSPITR) 



Translate >>