Why Warehouse security?
Warehouse is a huge collection of data. So unlike other databases, its security is more important from both internal and external threats. 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.
Warehouse is the integrated, time variant, nonvolatile collection of data which is used for decision making processes by management. The normal ETL process uses 3 tier architecture to load data in the warehouse. It takes data from OLTP systems (entered by users), after cleaning and passing through transformation logic is loaded into a central repository Ã¢‚¬€œ a data warehouse.
The fact is 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. Also in the past data warehouse of many well known companies (like Telco few weeks ago) were hacked.
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 mobiles, adhoc query tools, email alerts etc. The ways in which data can be hacked are numerous but, with careful planning, it is possible to secure a data warehouse.
How to secure a data warehouse?
As the subject of data security is such a broad subject, in this blog post we will look at how Oracle can help you encrypt network traffic going to and from the data warehouse.
There are four basic areas to consider:
- Access Control Ã¢‚¬€œ Oracle database vault and label security
- Data Protection Ã¢‚¬€œ Oracle advanced security and backup access
- Monitoring Ã¢‚¬€œ Oracle audit vault and EM configuration pack
- User Management Ã¢‚¬€œ Identity management and user security
Oracle Database provides features to support the requirements of each of above four areas. For example, for implementing access control Oracle provides Label Security, Virtual Private Databases and Database Vault.
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. 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 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 organization is moving towards active data warehousing. According to me in reality they need to move to a secure active data warehouse.
Secure transmission of data with Oracle
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.
To check if the advanced is installed in your system is quite simple. Just run the program $ORACLE_HOME/bin/adapters. This will generate a list of all the supported connection and security drivers.
Data network encryption setup
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 Net Manager is started, 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 encryption screen.
Selecting the integration tab gives the list of available methods. Choice depends on the level of encryptions required and in some cases geographical location as there are limitations imposed by many governments.
For data encryptions, there are five choices:
1) Advanced Encryption Standard (AES):
This is the new Federal Information Processing Standard (FIPS) encryption algorithm. It can be used by all businesses to protect sensitive data over a network.
2) 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.
3) Triple DES Support:
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.
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.
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 various values such as AES256, AES128, 3DES168 etc can be seen.
Once the method is selected, next step is to determine how encryption will work. There are four possible methods:
The last step is to set the seed. Seed is symmetric key cipher operating on fixed length group of bits with an unvarying transformation. Some of the encryption methods require a seed that must be 10 to 70 characters in length; however, it changed at any time.
Once the setting is done save them to write these changes to the configuration files.
The SQLNET.ORA file on the server should now look like this:
SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)
SQLNET.ENCRYPTION_CLIENT = required
SQLNET.ENCRYPTION_SERVER = required
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):
The TRACE_LEVEL_CLIENT setting will allow us to check if the data is being encrypted.
To check if the working is expected, take a line sniffer and run a query from SQLPlus before you enable encryption, then enable encryption and the packets of data should now be scrambled. Any client that uses SQLNet to access the database will use encrypts to 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.