Lock up your data warehouse - Part 1

Does anyone take data security within their warehouse seriously? This is not a trick or facetious question because, according to news posting on the web a few weeks ago most of the major systems, including the data warehouse, of a well-known Telco company were hacked. This made me wonder: how secure are most data warehouses?

In general, when analysts and consultants take about data security the emphasis, or perceived emphasis, seems to be on the operational systems especially those, which are available to customers and partners. Everybody who shops online knows they should always look for a secure connection icon or some indication that their personal and credit card details will be securely managed when they submit purchase. For example, my local online bike store recently added this logo to their home page which gives me considerable piece of mind when ordering bike parts from their website:


But what happens to all that operational data? Usually, most of it is cleaned and scrubbed and moved into a central repository - a data warehouse. In fact you could argue the data in a data warehouse is even more valuable to a hacker since it has all been nicely cleaned and validated. Most importantly it is all in one place. Therefore, as more and more data is pushed into the data warehouse and organizations open up this information to more and more people the chances of someone extracting (illegally) data must also increase.

In reality most data warehouse probably have multiple data entry and exit points when you take into consideration factors such as ETL operations to move data, dashboards, reports that are sent to iPhones/Blackberries, adhoc query tools, email alerts. The list is almost endless. The ways in which data can be hacked are numerous but, with careful planning, it is possible to secure a data warehouse.

So, where do you start? There are four basic areas to consider:

• Access Control
• Data Protection
• User Management
• Monitoring

Oracle Database provides features to support the requirements of each of these four areas. For example, for implementing access control Oracle provides Label Security, Virtual Private Databases and Database Vault.

At the moment all the analysts are talking about data security projects as being the types of projects most likely to get funding in the current economic climate. Which means now is the best time to review security within your data warehouse, put together a project plan to fully secure your data warehouse and take it to the board. If the analyst community is right, you should get the go-ahead very quickly. Most importantly, you will be protecting your most precious data asset from both internal and external hackers.


As the subject of data security is such a broad subject I am going to break it down into a series of blog posts with the first, this one, looking at how Oracle can help you encrypt network traffic going to and from the data warehouse.

But before we consider locking down the data within the database we need to consider how data is moved, or transmitted around, the organization. Encryption of network data provides data privacy so that unauthorized parties are not able to view plaintext data as it passes over the network. I would argue this is becoming increasingly important as the data warehouse is linked directly into the operational systems to provide additional intelligence. While the value of the process that is moving data around is limited, i.e. these are not monetary transactions that can be high jacked and used to move money to another location, each process could still be transmitting valuable data such as social security numbers, date of birth, zip code, credit balance, account numbers etc. that could be used outside of the organization for other illegal purposes. While there are many tools to make sure only authorized people access specific types of data, many companies freely broadcast this type of data around their networks with little or no encryption to protect it from hackers using sniffers.

With so many processes, both internal and external, accessing the data warehouse the need to encrypt the network traffic carrying data to and from the data warehouse is becoming increasingly important. Every customer is moving towards active data warehousing. In reality you need to move to a secure active data warehouse.

How can Oracle help secure the transmission of data?
Oracle Advanced Security includes support for configuring native Oracle Net Services data encryption and integrity. Oracle Advanced Security provides the Advanced Encryption Standard (AES), DES, 3DES, and RC4 symmetric cryptosystems for protecting the confidentiality of Oracle Net Services traffic. This means that all Net Services traffic including ETL jobs generated by Warehouse Builder, manually coded PL/SQL ETL jobs, BI reports, dash board widgets etc are encrypted into unintelligible cipher text based on a key, in such a way that it is very hard (computationally infeasible) to convert cipher text back into its corresponding plaintext without knowledge of the correct key.

How do I tell if Advanced Security is installed?
The easiest way, apart from running the installer, to check if Advanced Security has been fully installed is to run the program $ORACLE_HOME/bin/adapters. This will generate a list of all the supported connection and security drivers. Running the command on my Linux environment generated the following:


How do you set up network data encryption?
Normally, the network or security administrator who sets up the encryption and integrity configuration parameters manages the configuration process. Encryption and integrity parameters are defined by modifying the sqlnet.ora file on the clients and the servers on the network. It is possible to configure any or all of the available Oracle Advanced Security encryption algorithms, and either or both of the available integrity algorithms. However, Only one encryption algorithm and one integrity algorithm are used for each connect session.

A network connection can support more than one encryption algorithm and more than one integrity algorithm. When a connection is made, the server selects which algorithm to use based on the list of algorithms specified in the sqlnet.ora files. The server searches for a match between the algorithms listed on both the client and the server. It then picks the first algorithm in its own list that also appears in the client list. If one side of the connection does not specify an algorithm list, all the algorithms installed on that side are acceptable.

First run the Oracle Net Manager utility. This is located in the $ORACLE_HOME/bin directory. This will automate the setting of the required parameters in the various configuration files.

Once you have started Net Manager, select the ‘Profile’ and then use the pull down menu (“Naming” is selected by default) to select the “Oracle Advanced Security”.


Once Oracle Advanced Security is correctly installed, it should be possible to access this screen:


In this case we are going to focus on just the Integrity and the Encryption features. Selecting the Encryption tab reveals a list of available methods. Which method should you use? As always it depends on the level of encryption you need and in some cases, it depends in which part of the world you are based because due to limitations imposed by the US Government. For data encryption customers have five choices:

Option 1 : Advanced Encryption Standard (AES)
This is the new Federal Information Processing Standard (FIPS) encryption algorithm. It can be used by all U.S. government organizations and businesses to protect sensitive data over a network.

Option 2: the Data Encryption Standard (DES)
DES has been a U.S. government standard for many years and is sometimes mandated in the financial services industry. Because it has been a standard for so long, DES is deployed throughout the world for use in a wide variety of applications.

Option 3: Triple-DES Support (3DES)
3DES encrypts message data with three passes of the DES algorithm and provides a high degree of message security. However, there is a performance penalty. 3DES typically takes three times as long to encrypt a data block when compared to the standard DES algorithm. This will obviously be of concern to data warehouse DBAs where data is moved across the network loaded in real/near-real time.

Option 4: DES40
The DES40 was designed to provide DES-based encryption to customers outside the U.S. and Canada at a time when the U.S. export laws were more restrictive. Now, in Oracle Advanced Security 11g Release 1 (11.1), DES40, DES, and 3DES are all available for export. DES40 is still supported to provide backward-compatibility for international customers.

Option 5: RSA RC4
The RC4 algorithm, developed by RSA Data Security Inc., has become the international standard for high-speed data encryption. RC4 is a variable key-length stream cipher that operates at several times the speed of DES, making it possible to encrypt large, bulk data transfers with minimal performance consequences. This is probably the ideal choice for data warehousing since it has minimal performance implications.

Each of these methods comes in a variety of flavors offering differing levels of key length. Therefore, in the list of available methods you will see values such as AES256, AES128, 3DES168 etc etc.

Once you have decided the method that best suites your own specific requirements you need to determine how the encryption will be work. For the server and client there are four possible methods:

• Rejected
• Accepted
• Requested
• Required

Because this applies on both sides of the connection you get a pay-off matrix that determines if the connection is allowed or is forced to fail. The most severe choice is to use ‘Required’ on both the client side and the server side.

The last step is to set the seed. Some of the encryption methods require a seed that must be 10 to 70 characters in length, however, it changed at any time.


Once you are happy with the encryption settings use the ‘File->Save Network Settings’ menu option to write these changes to the configuration files.

The SQLNET.ORA file on the server should now look like this:


SQLNET.CRYPTO_SEED = '04402314687768'

Next amend the SQLNet.ora file on the client side so it looks like this (this can be done either by copying over the SQLNet.ora file from the server and taking out the SERVER related entries or you use the NetManager utility that is installed as part of the database client installation process):

SQLNET.CRYPTO_SEED= '04402314687768'


The TRACE_LEVEL_CLIENT setting will allow us to check if the data is being encrypted.

So how do you then know if this is working as expected? If you have a line sniffer then run a query from SQLPlus before you enable encryption, then enable encryption and the packets of data should now be scrambled. Alternatively, you can check the SQLNet trace file on the client side and search for the following:

2009-07-01 14:35:59.236239 : na_tns:Secure Network Services is available.
2009-07-01 14:35:59.236298 : nau_adi:entry
2009-07-01 14:35:59.236354 : nau_adi:exit
2009-07-01 14:35:59.236411 : na_tns:authentication is not active
2009-07-01 14:35:59.236470 : na_tns:encryption is active, using RC4_256
2009-07-01 14:35:59.236530 : na_tns:crypto-checksumming is not active

Another way to check this is to set the SQLNET.ENCRYPTION_CLIENT parameter to rejected in the client SQLNET.ORA file and this will stop SQLPlus from connecting and the following error message will be displayed:

TNS-12660: Encryption or crypto-checksumming parameters incompatible

This tells you that encryption is in operation and working as expected. Just remember to return the settings to their original values before proceeding.

How do you set up network data integrity checking?
Now that all data transmissions going via SQLNet are now fully encrypted, it is possible to take this a step further and protect the data warehouse from two other forms of attack:

• Data Modification attack
• Data Replay Attack

Personally, I am not convinced either of these types of attacks is likely within a data warehouse. These are usually targeted at OLTP systems that are processing transactions where for example money transfers are being processed. In this case the ability to modify the transaction, change a deposit transaction from $100 to $10,000 and replay the transaction multiple times. At the moment (and I am open to persuasion on this) there seems little value in adding protection from these types of attacks. The only scenario I could construct is where the data warehouse is used to determine the credit risk of a customer. In this case, I suppose a data modification attack could change a “bad” rating to a “good” rating?

Even so, in my opinion you can never have too much security. Therefore, for the sake of a few mouse clicks it makes sense to add data integrity checking. For managing data integrity Oracle provides two encryption solutions:

• SHA1 – Secure Hash Algorithm. SHA-1 is part of a family of secure hash algorithms required by law for use in certain U.S. Government applications.
• MD5 – Message Digest Algorithm 5. It is widely used cryptographic hash function with a 128-bit hash value. As an Internet standard, MD5 has been used in a wide variety of security applications and is commonly used to check the integrity of files.

Now, I am not a security expert so I am not in a position to recommend one version over the other. Each has its own strengths and weaknesses’ and I would recommend either using Google or talking to your in-house security expert before selecting a specific method.

Once you have decided on an encryption method run the NetMgr configuration tool and select the Integrity tab to set the various options:


In this example I have selected the MD5 method and set the checksum level to ‘required’.


The same settings are then applied to the client configuration.


Finally, save this new configuration to the SQLNet.ora file using the ‘File->Save Network Configuration’ menu option. Four new lines will have been added to the SQLNet.ora file:


The same changes need to be made on the client side either by copying the SQLNet.ora file from the server and amending it as stated earlier or by using the NetManager client utility. On the client SQLNet.ora file there will be two additional entries:


As a result of these additions if we now use the SQLNet trace feature on the client side to trace a SQLPlus session we can see the following information in the trace file:

2009-07-03 11:13:32.135135 : na_tns:Secure Network Services is available.
2009-07-03 11:13:32.135194 : nau_adi:entry
2009-07-03 11:13:32.135251 : nau_adi:exit
2009-07-03 11:13:32.135308 : na_tns:authentication is not active
2009-07-03 11:13:32.135368 : na_tns:encryption is active, using RC4_256
2009-07-03 11:13:32.135427 : na_tns:crypto-checksumming is active, using MD5

the last line as shown above tells us the data integrity checking is enabled. Therefore, we now have secured SQLNet data transmissions. Any client that uses SQLNet to access the database will use encrypt all data transmissions. If a client tries to us an incorrect SQLNet configuration then the connection will be refused and a TNS-12660 error will be returned to the client.

In the next posting I will look at how to make sure SQLDeveloper and Java applications connect to the database using network data encryption. For SQLDeveloper the process is relatively straightforward. For java applications in general there are some additional libraries you need to bundle with your application. But more on this later….


Post a Comment:
Comments are closed for this entry.

The data warehouse insider is written by the Oracle product management team and sheds lights on all thing data warehousing and big data.


« June 2016