Data security in 11g 2

[lewisp]

Can someone explain how, if at all, Oracle 11g data is secured in a tablespace? By this I mean, if I store some data in a table, then some 'person' removes the hard drive from the server to examine the raw data on the drive, would they be able to read the data directly?

If so, whats the best method of encryption? Should I use DBMS_OBFUSCATION_TOOLKIT or some other?

TIA

 

[SantaMufasa]

Lewis,

Perhaps the best answer to your excellent question is to go out to one of your tablespaces and "observe" the data without benefit of Oracle tools. You'll see that, yes, the data is discernable, but one must know how Oracle stores the data to make wholesale use of the data. (And another "yes"...Oracle's storage algorithms are available via Googling.)

So, an obfuscation methodology would help to protect against third-party observation of your data.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[lewisp]

Thanks Dave. In that case my preferred method will be to store the tablespace files within a secured encrypted volume.

 

[Thargy]

lewis,

why would you not just use Oracle's encrypted tablespace facility? That way you don't have to encrypt all your data in an encrypted volume, just the sensitive stuff. Also, if you use Oracle's built in features, then RMAN can also do encrypted backps for you.

At first glance, it seems to me that an encrypted volume might be a backward step.

Regards

T

 

[lewisp]

Brilliant! A bit of googling and I found this. Thats the info I needed to know - thanks thargtheslayer.

Step 1: In order to encrypt a tablespace, the initialization parameter ‘COMPATIBLE’ should be set to 11.1.0 or higher.

Step 2: Since Oracle uses transparent data encryption feature we have to create an oracle wallet to store the master encryption of the database and it should be opened. Let us create wallet along with the key.

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “DeciPher”;

Step 3: Once above two steps are performed, now is the time to create encrypted tablespace.

CREATE TABLESPACE secdec
DATAFILE ‘C:/app/oradata/orcl/secdec01.dbf’ SIZE 100M
ENCRYPTION USING ‘AES256?
DEFAULT STORAGE(ENCRYPT);

With thanks to Decipher Information Systems

http://decipherinfosys.wordpress.com/2008/02/05/encrypted-tablespaces-in-oracle-11g/.

 

[karluk]

The problem with using encrypted tablespace, or other features of Oracle's transparent data encryption, is that it requires an Advanced Security Option license. Whenever I've looked into ASO, I've loved the features it offers but been put off by the price tag. So, if you're willing to spend some serious money, ASO is for you. However, if your budget is limited, you should look into using dbms_crypto or dbms_obfuscation_toolkit to encrypt only the table columns that absolutely must be secure from this type of eavesdropping.

 

[Thargy]

Lewis,

karl makes a good point, I paid no heed to cost. However, since your company obviously needs encryption in one form or another (otherwise why did you post?) the issue of cost must be faced.

It is almost unheard of for custom solutions to be cheaper than paying the licence fee for a standard solution. For example, if you use your own encryption, all your RMAN backups are useless. You must therefore factor the cost of writing your own version of RMAN into your costs.

If oracle upgrades, say to version 12 which finally delivers streamlined patches (fantasising wildly here, I know) then you must test and develop all your solutions to be backwards compatible, fully documented, and comprehensible by outside consultants who may be called in to fix and use them.

In other words, if cost is not an overriding criterion, you'd have to be an idiot not to use Oracle's facilities. If it is, then consider Karl's point carefully.

Over to you, but please do let us know how this one turns out. I'd love to see manglers (sorry managers) making a sound decision for once.

Regards

T

 

[lewisp]

Thanks for all your excellent replies as usual, guys.

Our company requires that the entire database (read: tablespace(s)) is encrypted. ASO would be the ultimate solution, but we will need to take a look at the pricing very carefully. If DBMS_CRYPTO is like obfuscation toolkit, it is not sufficient to encrypt certain colums only. There must be no access to any part of the database without the key.

The way our encrypted volumes work is that the volume is opened for read and write using a key. When the volume is unopened it appears as a single data file, the contents of which are encrypted. Once the file is open using the encryption control software and the correct key, the data is accessible as if the encrypted file were mounted as a new volume (eg. E:\). Individual files can then be accessed from within it like any other file, totally transparent of the encryption software. Our tablespace files are stored in this volume, so that all normal functions of Oracle work as expected. If the volume is taken off line again, all DBF files contained within it are encrypted.

Is there any reason why this method would not be good practise?

 

[Thargy]

Yes,

several spring to mind.

First, you have lost fine-grained control over what is and is not encrypted. It's volume based, instead of tablespace based.

Second, you have introduced an unwanted mode of failure. If you use oracle's built in facilities, Oracle naturally certifies and guarantees its software, and will respond to a call for assistance on metalink.

If you reveal that third-party software is in the mix, Oracle may well turn round and say "Sorry, we can't help, because as far as we can see, the problem is not within Oracle." You then place a call to the encryption provider, and they are equally certain that their software is in order, and have you raised a call on metalink? After three rounds of telephone table tennis, you will be the one bouncing up and down a lot.

Oracle uses ASSM and LMT by default. If you are using OMF and/or ASM to boot, how is that interfacing correctly to your encrypted volume? Do you know that all of this technology works because of a successful test, or are you just hoping?

If a tablespace uses many DBF's, and the encrypted volume reaches capacity, will you be able to just use a convenient unencrypted disk, whilst another volume is prepared, or will undesirable side effects occur?

If due to space requirements you have several volumes, how will the db manage all its keys, so that it can do its job properly, and open these several volumes?

How are you guaranteeing that your back-ups are encrypted. If you just use RMAN and rely on the encrypted volume approach, then you will run out of space fairly smartly. If you (quite sensibly) move old archive logs from disk to tape, how will you restore them later, when an encrypted volume is full, and you have nowhere to which the tapes can be loaded. This of course assumes that the tapes can fully interoperate with decrypted and/or encrypted volumes.

I may be wrong in some of my assertions, but really I can summarise what I'm trying to say in one golden oldie
"KISS - keep it stupidly simple".

Regards

T

 

[lewisp]

Hi thargtheslayer, thanks for the very useful post.

I hear you about the third party software issue, which is fair to say. But we have completed quite a lot of testing with this setup and I have to say it seems pretty robust.

To be honest, I don't see the volume space being an issue because if it fills up we just create a new larger volume (it takes less than 2 minutes to administer). We then shutdown the database, move the DBF files into the new volume, mount the volume as the same drive as the old one and away we go. We don't see that its any different to storing DBFs on different volumes like we do when we use partitioning. This doesnt happen very often as we create volumes large enough for our requirements - the encryption software allows us to encrypt entire HDDs.

Backups do not necessarily have to be encrypted, but they are stored securely and definitely off-line.

One thing I don't know yet, is how the performace using this method compares to using Oracle ASO. Theres got to be overhead in both methods but I havent installed ASO to compare.

However, I am open to suggestions for improvement and you, with others, have given me some very interesting things to think about.

 

[lewisp]

I should also add we use quite a lot of external tables, usually CSV files, and these are also neastly stored on the encrypted volume.