PL/SQL Security Coding Practices. Introduction to a better architecture part 1.

I have been seeing this database architecture for over thirty years and it’s high time we stopped using it. Before I go too far, let me tell you I get it, you have pressure to get the application out the door and working in a defined timeframe. I still design and develop systems and the pressure to take shortcuts can be great. This short cut is a security killer.

So what have we been doing wrong for all these decades? Put all of the database objects and application code into once schema. This is just a bad idea all around. All it takes is one security bug and the bad guy owns your database. You might as well, put pretty gold wrapping paper with a bow around it and write the bad guy a gift card. If you come to any of my talks, I’ll be happy to demonstrate owning a database, including all your source code and all your data in just a couple of easy commands. But because this is not intended on being a lesson in hacking a database, I wont go into it here.

The power and security configuration of using an API (1)

There is an architecture, that will drastically improve the security of your database. By segmenting your application code from your data and use an API to access the data. Oracle 12c has several PL/SQL enhancements that will make your code much more secure. Oracle 12c PL/SQL now allows you to assign roles to packages, procedures and functions (But you should only be using packages). PL/SQL also now allows you to white list what can execute code. For years, we granted execute to a user, but now you can define what PL/SQL package can call another PL/SQL package using the accessible by clause. We are going to leverage these new features along with authid to define a trusted path that is controllable, fast and secure.

My next several post will move through this architecture, and explaining how to implement it effectively.

The power and security configuration of using an API

Questions you may want to start with when moving to the #cloud

Last week one of my customers called me into a meeting to discuss moving a critical application to the cloud. This application is very sensitive to the customer and the data it holds is very sensitive to my customers customer. The results of this meeting turned into a list of questions forwarded the customers executive staff and also a set of questions for the cloud vendor.

This Cloud vendor is providing a COTS solution storing personally identifiable information tax and other very sensitive information. Because of this a number of the questions focus on the protection of PII and the destruction of unneeded copies of data.

I have redacted customer and vendor information from this list of questions,  these questions may serve as a baseline for your organization to come up with questions for your Cloud vendors. Point of note the answers to these questions will more likely than not cause follow-up questions.

Here is the list of questions for the customers executive staff to address.

===============================================================

As <REDACTED> moves towards cloud based computing solutions, <REDACTED> must consider the following to create standards for all cloud based systems going forward:

  • Will <REDACTED> require TLS on day 1? If not, vendor must have a plan and a deadline to get off SSL and on to TLS?
  • Will <REDACTED> require DISA STIG standards (Fed DOD standard) for all off site cloud data?
  • Will <REDACTED> require PENetration testing and at what frequency (Federal standard is 1 year)?
  • What level of data destruction is required for <REDACTED>’s secure/PII data being stored on a cloud based system controlled by non-<REDACTED> vendors?
  • Will <REDACTED> hold AES256 as the minimum encryption standard for cloud based systems?
  • Will <REDACTED> require 3DES minimum 168 bits?
  • Will <REDACTED> require a minimum of 7 wipes for secure/PII data stored on cloud based systems?
  • Will <REDACTED> require in sales contract with stated frequencies, independent audits to ensure <REDACTED>’s stated audit, encryption and data destruction plans are in effect and compliant?
  • Will <REDACTED> require internal <REDACTED> audits and/or legislative audits be performed on <REDACTED> systems?
  • The sales contract must state the “break up” plan for all <REDACTED> data including the delivery back to <REDACTED>, the destruction of the data on vendor systems and the certification that all data has been destroyed according to the <REDACTED> standards. Independent audit to verify results.
  • Will <REDACTED> require all data stay within the United States, with no data ever leaving the US?
  • What will <REDACTED> require regarding the vetting standard for cloud vendor trusted inside employees?
  • What will <REDACTED> require regarding liability insurance in the event of a security incident?

Here is a list of questions for the cloud vendor.

===================================================================

As <REDACTED> data is highly sensitive and contains a great deal of PII for each firm, the following are questions to be answered:

1.    Regarding the destruction of sensitive/PII data on <REDACTED> systems, how will you destroy unnecessary copies of data and ensure the necessary copies are encrypted and secure?

2.    Is the use of AES256 and 3DES encryption consistent throughout <REDACTED> enterprise as referenced on page 10 of the Security Management Plan? How many bits are used for 3DES?

3.    Initial Source Data/Document Load files (via sftp per <REDACTED> docs): Controls/Audit – <REDACTED> should know exactly who touched the load files and for what purpose via audit reports.

4.    Additionally, after migration is complete, <REDACTED> to certify (via independent audit) that all source data has been destroyed and no ghost data remains on servers or work stations.

5.    Cross boarder – will the data leave the United States for any reason at any time?

6.    What analytics software packages are in use to monitor account activity for our <REDACTED> employees as well as <REDACTED> trusted inside employees? How will audit reports be delivered to <REDACTED>?

7.    What does “in compliance with Cyber Security Standard” refer to as mentioned on page 6 of the <REDACTED> Security Management Plan? Is this a subset or superset of NIST?

8.    On page 7 of the <REDACTED> Security Management Plan in reference to Export Servers under System Architecture, how is the use of these Export Servers audited and after the export is no longer required, how will you certify that the data has been destroyed? If used, can an unencrypted copy of the export be made?

9.    Will all backups be encrypted with 3DES and at what bit level? How will <REDACTED> certify the destruction of old backups?

10.   What is the plan for <REDACTED>’s system using TLS?

11.   Does <REDACTED> harden sqlserver to DISA STIG standards? If not, is it a superset or a subset?

12.   What is the end of contract plan for all <REDACTED> data including the delivery back to <REDACTED>, the destruction of the data on <REDACTED> systems and the certification that all data has been destroyed according to the <REDACTED> standards.

13.   In the event of a security incident, does <REDACTED> have liability insurance to cover associated losses?

14.    How are your trusted inside employees are vetted (DBA’s, System Admins, Network Admins, etc)?

15.   If you perform PEN testing, what is the frequency of the testing and will <REDACTED> get a redacted copy of the results of each test?

Encrypt those backups

April 2005 Ameratrade loses a backup tape containing information on 200,000 customers.

February 2005 Bank of America loses backup tapes containing information on 1.2 million charge cards.

September 2011, SAIC loses backup tapes of 4.9 Million members of the military who sought medical treatment in the San Antonio area. The data contained name, social security numbers, phone numbers and medical information. This data was not encrypted.

SAIC made the following statement: “Retrieving data on the tapes, which were stolen from a company employee’s car is not likely to happen because doing so requires knowledge of and access to specific hardware and software and knowledge of the system and data structures.” Excuse me if this does not make me feel better. I can get on eBay to get the hardware needed and download the software from any number of vendors to do the restore. Yes if the backup was done from Oracle or DB2 or MS SQL Server then you would need the software from the vendor. What if this theft was targeted and the thief knew what they were after?

I can go on and on about backup tapes that are lost out of the back seat of an employees’ car. And to be honest; I have transported tapes in my car too. However; when I reflect on transporting critical information in my car, I now get the hebegebes. Now we use a bonded courier to transport backup tapes.

Backup tapes are also being shipped to someplace like Iron Mountain. But lets face it, the people who are handling your backup tapes are low paid employees who could be influenced to look the other way. If someone really wants your backup tapes there is a way for someone to get your backup tape.

What are the options for encrypting backups.

  1. Use rman encryption.

  2. Encrypt the backup files on the OS.

For option 1, using rman to encrypt. There are a few options you can use a password to encrypt the backup or you can use a wallet to encrypt the backup.

If the backup is being sent offsite, using a password to encrypt the backup may be your better option.

If the backup is being sent to a Disaster Recovery site to build a standby database, using the wallet may be the better option.

Right now we are addressing sending a backup offsite so lets walk through the process of building an encrypted backup using a password.

First find out what encryption algorithms are supported.

SQL> select ALGORITHM_NAME, ALGORITHM_DESCRIPTION

2 from V$RMAN_ENCRYPTION_ALGORITHMS;

ALGORITHM_ ALGORITHM_DESCRIPTION

———- —————————————————————-

AES128 AES 128-bit key

AES192 AES 192-bit key

AES256 AES 256-bit key

SQL>

Of the algorithms that are available, AES256 is the strongest one available. So we are going to select AES256 for our encryption.

RMAN> set encryption algorithm ‘aes256’ identified by A_Passphrase_that_you_select;

executing command: SET encryption

using target database control file instead of recovery catalog

Using “set encryption algorithm’ command we did two things. One we set the algorithm that will be used for the backup and we set the passphrase that we need to decrypt the backup.

Next we are going to run the backup like we would normally do.

RMAN> backup as compressed backupset database format ‘/home/oracle/backup/encrypted_with_password%u%d.backup’;

Starting backup at 02-AUG-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00003 name=/opt/oracle/oradata/orcl/sysaux01.dbf

input datafile file number=00001 name=/opt/oracle/oradata/orcl/system01.dbf

input datafile file number=00002 name=/opt/oracle/oradata/orcl/example01.dbf

input datafile file number=00004 name=/opt/oracle/oradata/orcl/undotbs01.dbf

input datafile file number=00006 name=/opt/oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1: starting piece 1 at 02-AUG-12

channel ORA_DISK_1: finished piece 1 at 02-AUG-12

piece handle=/home/oracle/backup/encrypted_with_password0dnhl9n6ORCL.backup tag=TAG20120802T170333 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:35

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 02-AUG-12

channel ORA_DISK_1: finished piece 1 at 02-AUG-12

piece handle=/home/oracle/backup/encrypted_with_password0enhl9s1ORCL.backup tag=TAG20120802T170333 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 02-AUG-12

RMAN>

How do we decrypt the backup when we need to restore. It’s that simple.

RMAN> set decryption identified by A_Passphrase_that_you_select;

executing command: SET decryption

using target database control file instead of recovery catalog

RMAN> restore database;

Starting restore at 02-AUG-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

skipping datafile 1; already restored to file /opt/oracle/oradata/orcl/system01.dbf

skipping datafile 2; already restored to file /opt/oracle/oradata/orcl/example01.dbf

skipping datafile 3; already restored to file /opt/oracle/oradata/orcl/sysaux01.dbf

skipping datafile 4; already restored to file /opt/oracle/oradata/orcl/undotbs01.dbf

skipping datafile 6; already restored to file /opt/oracle/oradata/orcl/users01.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 02-AUG-12

RMAN>

Okay, I did not need to restore the database, but it’s good to know that this works.

Now you don’t have an excuse not encrypt your backups.