This forum is currently locked. You can't register or post questions at this time. (read more)

Securing Data

All posts relating to Oracle database administration.

Moderator: Tim...

Monto
Senior Member
Posts: 195
Joined: Sat Jan 16, 2010 1:55 pm

Securing Data

Postby Monto » Sat May 05, 2012 1:22 pm

Hi Tim,

I have a requirement to store data in encrypted form in the table(all columns) not in clear text which is considered sensitive .I know this could be implemeted with Access Control(FGAC withcolumn masking)) but even DBA's should not have the acess to it.Client doesn't have licence for Vault and they're not planning to it get it either.


I have been reading about dbms_crypto which has cryptographic functions to encrypt and decrypt functions but not sure how to encrypt the entire columns of table and also the key management,the security team needs the key to be on the filesystem and locked down by master encryption key.

I have been trying to find an example of storing key on filesystem with key protected with master key but didn't find it ,can you please help me how i could achieve this with an example i would really appreciate it.

And also,how could application would make a call to the file to decrypt it?

Thanks in Adavnce.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Securing Data

Postby Tim... » Sun May 06, 2012 8:11 am

Hi.

My understand is you can't use table or tablespace encryption because you not only want the data encrypted in the file, but also when retrieved by SQL for everyone except those who have the privilege to see it. With this in mind, you need to encrypt it manually.

This sounds like a job for DBMS_CRYPTO:

http://www.oracle-base.com/articles/10g ... bms_crypto

You don't actually encrypt whole columns with this method, just individual values, so you must manually encrypt ever value before you insert it, and decrypt every value when you select it. You could do the encryption using triggers, but the best method in my opinion is to only allow inserts via a PL/SQL API and do all the work in that API.

Likewise, when you query the table, you will have to decrypt every value, so this is better done via an API also.

As you point out, key management is the killer. Why? Because if you store the key in the database, then the DBAs have access to it and can therefore decrypt the data. If it is not in the database, then someone needs to enter it when they query the data in this table (so the data can be decrypted). Depending on the usage of the data, this can be a problem, since several people my have to memorize the key, and if they forget it, the data is lost. No going back!

The column definitions also present a problem, because every column in the table must be defined as RAW, so you will need to store an indicator of the original type of the data, so you know how to convert it back, otherwise you will not know whether it is a string, number or date etc.

These issues require some thought on your part before you can start an implementation. This is why some people just pay for data vault. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Monto
Senior Member
Posts: 195
Joined: Sat Jan 16, 2010 1:55 pm

Re: Securing Data

Postby Monto » Sun May 06, 2012 12:02 pm

Thanks Tim.I needed some help with the how to store key on filesystem i looked everywhere for an example but didn't find any.

I have been trying to find an example of storing key on filesystem with key protected with master key but didn't find it ,can you please help me how i could achieve this with an example i would really appreciate it.


Regards

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Securing Data

Postby Tim... » Sun May 06, 2012 12:45 pm

Hi.

I don't see how this will help. The DBAs have access to the file system...

Let's assume you do it, how will you decide who can use the key? If it is using a role or something in the database, then there is nothing to stop a DBA altering their privileges to access the data, then change it back when they aree done.

As soon as you try to automate the use of the key you make it possible for someone to cheat and defeat the object of the exercise.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Monto
Senior Member
Posts: 195
Joined: Sat Jan 16, 2010 1:55 pm

Re: Securing Data

Postby Monto » Mon May 07, 2012 1:32 am

Thanks Tim .I have been fighting with the security team to have access control with secure role and application context instead of encryption i can't push them much .Anyways,The plan is to have the key on a filesystem not owned by oracle or on an application server itself.Then in that case how to store the key on filesystem .

Regards

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Securing Data

Postby Tim... » Mon May 07, 2012 10:08 am

Hi.

I'm not really sure what to suggest in this case, because I've never been asked to do anything quite like this.

As I mentioned before, you will be responsible for creating the API to access the data, and it will need a key sent to it. If the key is being stored on the app server and sent from there, then it is really the application developers business to decide how it will be stored. All you care about is that you get sent it.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Monto
Senior Member
Posts: 195
Joined: Sat Jan 16, 2010 1:55 pm

Re: Securing Data

Postby Monto » Mon May 07, 2012 2:08 pm

Thanks.Do i use utl_file or directory object to point to the filesystem for key if on database server as an input value to the calling code.

and also,While generating the key how do i specify it store that on the filesystem.

I would really appreciate your comments as usual.

Regards

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Securing Data

Postby Tim... » Mon May 07, 2012 2:28 pm

Hi.

It doesn't seem like you are listening to me. :)

Let's say you store it on the database servers file system and read it using UTL_FILE. Any DBA will be able to read it either from the file system directly, or via ULT_FILE, making the whole point of using encryption to hide the data from the DBAs invalid. You might as well store the key in the database, because it would be no less secure.

Unless you get the users to enter the key directly in the application, thereby proving they have the right to see the data, the whole point of encryption (the way you described it) becomes redundant. Any attempt to store the key in the database or on the database server file system makes it visible to all DBAs. If you use the file system, you make it visible to the sys admins also. What't the point?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 4 guests

cron