By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,299 Members | 1,210 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,299 IT Pros & Developers. It's quick & easy.

Database security (crosspost)

P: n/a
Sorry, the prior message was multi-posted.
Here's a cross-posted version. Please disregard the other one.
Is there a way to create and encrypted database file?

What do people do when data security is important at the file level? In
other words, you don't want anyone to be able to take the database file (or
files) and extract data from them.

Ideally, I want a file the is absolutely encrypted on disk and that is
decrypted for data access. The problem, obviously, is that this would be a
very costly (cpu time) approach as you couldn't create a decrypted image on
disk (this would expose the data).

Are all database systems then, non-secure?

Thanks,

-Martin
Dec 8 '05 #1
Share this Question
Share on Google+
29 Replies


P: n/a
Martin (0_******@pacbell.net) writes:
Is there a way to create and encrypted database file?

What do people do when data security is important at the file level? In
other words, you don't want anyone to be able to take the database file
(or files) and extract data from them.

Ideally, I want a file the is absolutely encrypted on disk and that is
decrypted for data access. The problem, obviously, is that this would
be a very costly (cpu time) approach as you couldn't create a decrypted
image on disk (this would expose the data).

Are all database systems then, non-secure?


Everything is relative. For a small database, < 50 MB, it may be possible
to encrypt it entirely and only hold the decrypted version in main
memory. Then again, memory can be dumped.

For a 1 TB databae, encryption will have to be selective. Basically,
anything you want to search on, cannot be decrypted unless you are
prepared to take very long response times.

In SQL Server 2005, there are a lot of encryption enhancements, so that
you can encrypt individual columns with asymmetric and symmettric keys.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 8 '05 #2

P: n/a
> Everything is relative. For a small database, < 50 MB, it may be possible
to encrypt it entirely and only hold the decrypted version in main
memory. Then again, memory can be dumped.


I've experimented with a small database flat-file database (non SQL, non
Access) and in-memory tables. The process is horribly honerous. In memory
tables, at least what I've been able to get to using something like VB6, are
not really setup to be full blown databases but rather a place to hold
temporary data it seems. As such, there doesn't seem to be a way to load a
small database (consisting of a number of tables) into memory and have it
act as a database.

What occured to me is that, perhaps, one can create a RAM-disk on the fly,
tell it to be encrypted and move/copy the DB into it on application start.
Then the OS would take care of the matter? On application termination the
RAM disk would be destroyed.

My application would more than likely be single user. It made me wonder how
high-security DB's are handled. It seems to me that data is, for the most
part, fully exposed.

-Martin
Dec 8 '05 #3

P: n/a
Martin wrote:
What do people do when data security is important at the file level? In
other words, you don't want anyone to be able to take the database file (or
files) and extract data from them.


IMHO, the only way to protect a database in this way is not to give the
database to persons whom you don't want to have it.

For example, you could put the database on a hosted site that your
clients can access using only prescribed methods. Create your own
authenticated SOAP web service on your site to give access only to data
to which the client is authorized. Then you can use technology like
firewalls and HTTPS protocol to protect the database. In other words,
features aside from the RDBMS itself.

Encrypting the database is a kind of barrier to crackers, but unless you
use one-way encryption (which precludes reading the data), you simply
can't protect the data from a brute-force attack if you give the
database to the attacker. If they are motivated enough to get into that
data, they can use unlimited computational resources to crack your
encryption.

Regards,
Bill K.
Dec 8 '05 #4

P: n/a
Martin (0_******@pacbell.net) writes:
What occured to me is that, perhaps, one can create a RAM-disk on the fly,
tell it to be encrypted and move/copy the DB into it on application start.
Then the OS would take care of the matter? On application termination the
RAM disk would be destroyed.
Yes, but will the RAM disk is active, the data is accessible unencrypted.
My application would more than likely be single user. It made me wonder
how high-security DB's are handled. It seems to me that data is, for
the most part, fully exposed.


As I indicated, the key is to encrypt discriminantly.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 8 '05 #5

P: n/a
- but if high level security really does matter please be aware of the
easily accessible password recovery tools on the web!
For high level confidential data, some other database might be the solution
to consider.

Best regards
Niels K

"Martin" <0_******@pacbell.net> skrev i en meddelelse
news:bK*****************@newssvr21.news.prodigy.co m...
Sorry, the prior message was multi-posted.
Here's a cross-posted version. Please disregard the other one.
Is there a way to create and encrypted database file?

What do people do when data security is important at the file level? In
other words, you don't want anyone to be able to take the database file
(or files) and extract data from them.

Ideally, I want a file the is absolutely encrypted on disk and that is
decrypted for data access. The problem, obviously, is that this would be
a very costly (cpu time) approach as you couldn't create a decrypted image
on disk (this would expose the data).

Are all database systems then, non-secure?

Thanks,

-Martin

Dec 8 '05 #6

P: n/a
Martin wrote:
It made me wonder how
high-security DB's are handled. It seems to me that data is, for the most
part, fully exposed.

-Martin


High security means at all levels, including physical access. Your bank
doesn't give you their confidential data on a disk does it? That's why
they don't need to encrypt it in the database - because they can secure
and authenticate the access to the data.

--
David Portas
SQL Server MVP
--

Dec 8 '05 #7

P: n/a

"David Portas" <RE****************************@acm.org> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
Martin wrote:
It made me wonder how
high-security DB's are handled. It seems to me that data is, for the most part, fully exposed.

-Martin


High security means at all levels, including physical access. Your bank
doesn't give you their confidential data on a disk does it? That's why
they don't need to encrypt it in the database - because they can secure
and authenticate the access to the data.

--
David Portas
SQL Server MVP
--


Ahh, but I bet they don't encrypt the backups. They should. Oracle 10G2
allows this.
Jim
Dec 9 '05 #8

P: n/a
> Is there a way to create and encrypted database file?

There are plenty of ways; they commonly don't actually provide any
meaningful form of security.
What do people do when data security is important at the file level?
In other words, you don't want anyone to be able to take the
database file (or files) and extract data from them.
The only realistic method is to encrypt the data before submitting it
to the database.

Anything else that they try to sell you is snakeoil.
Ideally, I want a file the is absolutely encrypted on disk and that
is decrypted for data access. The problem, obviously, is that this
would be a very costly (cpu time) approach as you couldn't create a
decrypted image on disk (this would expose the data).

Are all database systems then, non-secure?


If they claim that their database system can manage the cryptography
for you, then yes.

Look for the book _Translucent Databases_ which has examples in
various languages for various databases.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/emacs.html
"Now, suddenly, I _am_ the expanding Russian Frontier" -- Commander
Ivanova
"But with very nice borders..." -- Dr Franklin
Dec 9 '05 #9

P: n/a
> High security means at all levels, including physical access. Your bank
doesn't give you their confidential data on a disk does it? That's why
they don't need to encrypt it in the database - because they can secure
and authenticate the access to the data.


You are thinking about databases in their, shall I say,
traditionalcorporate/business application.

Let's say that you develop an application. This application does some work
and needs to allow the user to save it to files. Much like MS Word or
Photoshop let you save files. Now, the application can be best written if
it can use database technology while doing its job. However, the resulting
files need to be encrypted for security reasons. There are many possible
reasons for this. One may be that you don't want your competition to
benefit from your work product and extract data from your files.

The choices, as I see them, are:

1- Create a completely custom file format, re-invent the wheel and roll out
your own db-style code. The file would be encrypted upon storage and
decrypted upon loading.

2- Use some sort of db system that is encryption-capable. Something like
SQL server isn't a possibility because customer machines will not have this
available.

Yes, of course, with enough time and effort almost anything can be cracked.
And, of course, the astute might even be smart enough to dig through memory
for the data. Those I see as extreme cases. I am more interested in the
old "keeping honest people honest" scenario rather than the high-IQ hacker
scenario.

Makes sense?

-Martin
Dec 9 '05 #10

P: n/a
Martin wrote:
Let's say that you develop an application. This application does some work
and needs to allow the user to save it to files. Much like MS Word or
Photoshop let you save files. Now, the application can be best written if
it can use database technology while doing its job. However, the resulting
files need to be encrypted for security reasons. There are many possible
reasons for this. One may be that you don't want your competition to
benefit from your work product and extract data from your files.
Attempting to protect proprietary data distributed with your
application may be reasonable enough. Locking away the *customer's*
data to prevent interop with competitor apps is not in the customer's
best interests however. I have come across one or two cases of that
over the years and if I were a customer I would not want to consider
purchasing such a system.
The choices, as I see them, are:

1- Create a completely custom file format, re-invent the wheel and roll out
your own db-style code. The file would be encrypted upon storage and
decrypted upon loading.
But the customer still needs to possess the decryption key. If the key
is in the software this is merely obfuscation rather than security. The
price is the performance impact.

2- Use some sort of db system that is encryption-capable. Something like
SQL server isn't a possibility because customer machines will not have this
available.


You can distribute SQL Server with your app, either Express Edition or
with a runtime licence. Why else are you posting in a SQL Server group?

--
David Portas
SQL Server MVP
--

Dec 9 '05 #12

P: n/a
> Why else are you posting in a SQL Server group?

To learn about the general topic of database security rather than one
specific hypothetical example I concocted that is admittedly too narrow.

In general terms, the link I found reveals part of the problem a little.
The issue will be an important one to consider with computing in the palm of
your hand. These days databases might find themselves being carried around
on laptop or palm machines. In the not-too-distant future, terabyte-class
portable storage might not be too far fetched an idea. Databases are sure
to go mobile. With this will come serious security issues as the files
themselves could fall into the wrong hands very easily.

Yet another hypothetical scenario is a travelling salesman having a database
of clients, notes and general business intelligence on a notebook computer.
It is more than likely that, today, stealing that data would just take a
very quick copy of the database file/s. I'd venture to say that most db's
are not designed to have strong security at the file level. I understand
why (cpu and system load in managing constant encrypt/decrypt processing)
but it is disturbing nevertheless.

-Martin
Dec 9 '05 #13

P: n/a
Martin skrev:
The issue will be an important one to consider with computing
in the palm of your hand. These days databases might find themselves
being carried around on laptop or palm machines. In the
not-too-distant future, terabyte-class portable storage might not be
too far fetched an idea. Databases are sure to go mobile. With this
will come serious security issues as the files themselves could fall
into the wrong hands very easily.


In my opinion, that would be comparable to carrying your entire fortune
in small cash in your wallet. Why would anybody do that? That's exactly
what banks are for. And in the not-too-distant future, *I* for one
expect to be carrying around a small device that will easily let me
maintain a database through something resembling a Web interface. The
backend and storage should be situated in a safe place, and certainly
not in my pocket. For crying out loud.
--
Leif Biberg Kristensen
http://solumslekt.org/
Dec 9 '05 #14

P: n/a
Martin wrote:
I'd venture to say that most db's
are not designed to have strong security at the file level. I understand
why (cpu and system load in managing constant encrypt/decrypt processing)
but it is disturbing nevertheless.


Yes, and in addition :

a) DBMS vendors do not necessarily have expertise with security
implementation. They're focused on data integrity, ACID transactions,
performance, scalability, API support, etc. Security is important and
worthwhile, but is not top of the list of DBMS development priorities.

b) The environments in which a DBMS operates are so variable
(mobile/laptop/desktop/enterprise) that we can't expect the DBMS to
contain the appropriate security solution that is the best for all these
environments.

I think you're better off using the platform's security solution to
restrict access to the database (for instance, firewalls), or else only
put pre-encrypted data into the database, as Christopher Browne
suggested, and handle the encryption & decryption at the application
level. Then you can also be selective about which data are truly
sensitive, and encrypt only those tables or columns.

Besides, if a given DBMS said it contained "security", would you feel
comfortable trusting their implementation, if your data was truly that
sensitive?

Regards,
Bill K.
Dec 9 '05 #15

P: n/a
Martin wrote:
I am more interested in the
old "keeping honest people honest" scenario rather than the high-IQ hacker
scenario.


SQL-level privileges (GRANT/REVOKE) are adequate to this goal.

Regards,
Bill K.
Dec 9 '05 #16

P: n/a
Martin wrote:
I'd venture to say that most db's
are not designed to have strong security at the file level. I understand
why (cpu and system load in managing constant encrypt/decrypt processing)
but it is disturbing nevertheless.

-Martin


SQL Server 2005 has strong encryption built in to the database. The
user decides whether that applies to all of the database or just
selected data.

--
David Portas
SQL Server MVP
--

Dec 9 '05 #17

P: n/a
Martin (0_******@pacbell.net) writes:
Let's say that you develop an application. This application does some
work and needs to allow the user to save it to files. Much like MS Word
or Photoshop let you save files. Now, the application can be best
written if it can use database technology while doing its job. However,
the resulting files need to be encrypted for security reasons. There
are many possible reasons for this. One may be that you don't want your
competition to benefit from your work product and extract data from your
files.


I'm not sure that I understand what you're getting at, but an application
cannot on its own save data in an encrypted format, and then decrypt it.
It can save the data in an obfusticated format, to make it more difficult
to access. But to truely encrypt, you need an encryption key that must
come from elsewhere, and the same applies when you decrypt. And the keys
also will have to be protected, typically by a passphrase entered by a
user.

From this follows that it's useful to encrypt for the user's benefit, so
he can protect the data. But it is not equally useful to protect for
the benefit of the application and its author.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 9 '05 #18

P: n/a
Leif B. Kristensen wrote:
For crying out loud.
See below. Go back twenty years. "For crying out loud"?. I am sure there
will be many scenarios where the DB will go mobile for lots of good reasons.
Security will be just as important at the bank and in your hand.
*I* for one
expect to be carrying around a small device that will easily let me
maintain a database through something resembling a Web interface. The
backend and storage should be situated in a safe place, and certainly
not in my pocket.

Thanks for all input. All interesting.

-Martin
Dec 9 '05 #19

P: n/a
On Fri, 09 Dec 2005 10:22:19 -0800, Bill Karwin <bi**@karwin.com>
wrote:

[snip]
Besides, if a given DBMS said it contained "security", would you feel
comfortable trusting their implementation, if your data was truly that
sensitive?


If they do not say that it does, I would be even less
comfortable. Trust but verify?

Sincerely,

Gene Wirchenko

Dec 9 '05 #20

P: n/a
> Martin wrote:
I'd venture to say that most db's
are not designed to have strong security at the file level. I understand
why (cpu and system load in managing constant encrypt/decrypt processing)
but it is disturbing nevertheless.

-Martin


SQL Server 2005 has strong encryption built in to the database. The
user decides whether that applies to all of the database or just
selected data.


Unfortunately, that means that you have to trust the database engine
with the cryptographic keys.

That means the DB engine is free to do whatever it likes with them,
which is an inherent, vast, gaping security hole.

It's so gaping that it obviates any value to the use of encryption.
--
output = ("cbbrowne" "@" "ntlug.org")
http://linuxdatabases.info/info/lsf.html
Rules of the Evil Overlord #114. "I will never accept a challenge from
the hero." <http://www.eviloverlord.com/>
Dec 10 '05 #21

P: n/a
Christopher Browne (cb******@acm.org) writes:
Unfortunately, that means that you have to trust the database engine
with the cryptographic keys.

That means the DB engine is free to do whatever it likes with them,
which is an inherent, vast, gaping security hole.


Eh? The DB engine does not have a mind of its own. Of course, any piece
of software can be spyware that leaks secrets behind your backs, but
if you are that paranoid, you should not use computers at all.

Or could you care to elaborate what you actually mean?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 10 '05 #22

P: n/a
Christopher Browne wrote:
Martin wrote:
I'd venture to say that most db's
are not designed to have strong security at the file level. I understand
why (cpu and system load in managing constant encrypt/decrypt processing)
but it is disturbing nevertheless.

-Martin


SQL Server 2005 has strong encryption built in to the database. The
user decides whether that applies to all of the database or just
selected data.


Unfortunately, that means that you have to trust the database engine
with the cryptographic keys.

That means the DB engine is free to do whatever it likes with them,
which is an inherent, vast, gaping security hole.

It's so gaping that it obviates any value to the use of encryption.
--


You mean to say that you only run open source and you inspect every
line of code that runs on your hardware? If not, how can you trust any
of it?

--
David Portas
SQL Server MVP
--

Dec 10 '05 #23

P: n/a
> Christopher Browne (cb******@acm.org) writes:
Unfortunately, that means that you have to trust the database
engine with the cryptographic keys.

That means the DB engine is free to do whatever it likes with them,
which is an inherent, vast, gaping security hole.


Eh? The DB engine does not have a mind of its own. Of course, any
piece of software can be spyware that leaks secrets behind your
backs, but if you are that paranoid, you should not use computers at
all.

Or could you care to elaborate what you actually mean?


If risk to be mitigated is that you do not wish to trust the system
administrators with the data, then you must not give the keys to any
component that system administrators can control.

That obviously includes the database engine.
--
output = ("cbbrowne" "@" "ntlug.org")
http://linuxdatabases.info/info/lsf.html
Rules of the Evil Overlord #114. "I will never accept a challenge from
the hero." <http://www.eviloverlord.com/>
Dec 11 '05 #24

P: n/a
> Christopher Browne wrote:
> Martin wrote:
>> I'd venture to say that most db's
>> are not designed to have strong security at the file level. I understand
>> why (cpu and system load in managing constant encrypt/decrypt processing)
>> but it is disturbing nevertheless.
>>
>> -Martin
>
> SQL Server 2005 has strong encryption built in to the database. The
> user decides whether that applies to all of the database or just
> selected data.


Unfortunately, that means that you have to trust the database engine
with the cryptographic keys.

That means the DB engine is free to do whatever it likes with them,
which is an inherent, vast, gaping security hole.

It's so gaping that it obviates any value to the use of encryption.


You mean to say that you only run open source and you inspect every
line of code that runs on your hardware? If not, how can you trust any
of it?


That would the sort of process required to validate comprehensively that
a system is secure.

That is not my goal; I merely need to indicate the obvious *holes*.

If crypto keys are passed into the database system, there are several
blatantly obvious places where modifications could be done to capture
crypto keys.

1. If they are passed across some interface like SQL-CLI, then the
query parser will have the key in plaintext form.

Capturing crypto keys might well be as simple, in this case, as
turning on query logging, which is a pretty standard database feature.

2. It is more than likely that there will be a single,
well-identifiable crypto library to which the key must be passed.
Know the address, inside the library, and you can find the code that
uses it. And hence you can either modify the library, or add a
wrapper library, in between (e.g. - on Unix, via a mechanism like
LD_LIBRARY_PATH), which can capture the key.

These are blatantly obvious mechanisms for capturing keys; supposing
implementors try to be clever, it can become less easy, but even so,
someone with anything near to physical access to the hardware can
examine memory contents, sniff network traffic, and the like, making
it extremely difficult to *truly* hide the keys from the system
operators.

There are hardware-based crypto cards which have special provisions
for how *their* keys are populated which can, if competently operated,
in a secure physical environment, provide pretty strong tamper
resistance.

But I'd take a guess that the cost of establishing that "competently
operated secure physical environment" is on the order of hundreds of
thousands of dollars, which isn't affordable for terribly many users.
--
output = ("cbbrowne" "@" "ntlug.org")
http://linuxdatabases.info/info/lsf.html
Rules of the Evil Overlord #114. "I will never accept a challenge from
the hero." <http://www.eviloverlord.com/>
Dec 11 '05 #25

P: n/a
Christopher Browne (cb******@acm.org) writes:
If risk to be mitigated is that you do not wish to trust the system
administrators with the data, then you must not give the keys to any
component that system administrators can control.

That obviously includes the database engine.


In SQL 2005, all keys requires a password to open them. The password does
have to be known by the admin staff.

However, since there is no protection again brute-force attacks, an admin
that wants to crack the key, can easily do so. Sniffing the passwords sent
over the wire can also be done, although the connection may be encrypted.

Despite these weaknesses, one should write of this sort of encryption
suppport as entirely useless. First, there is the trivial case that the
person who wants to proect the data, is actually the sole admin himself.

But assume the case of a corporate server, and there is data in one
database which is sensitive and you don't want the admin to see, for
instance the column employees.salary. No matter, if the column is
encrypted or not, the admin is breaching his instructions if he looks
at that data. But if the column is unencrypted, he may peak at it by
mistake, because he needs to troubleshoot some other problem with that
table. If the column is encrypted, and he does not have the password,
there is no dispute that he is tresspassing where he shouldn't, and
if his deeds are revealed, he would be fired on the spot.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 11 '05 #26

P: n/a
On Thu, 08 Dec 2005 21:08:32 +0000, Martin wrote:

My application would more than likely be single user. It made me wonder how
high-security DB's are handled. It seems to me that data is, for the most
part, fully exposed.


Don't know if it would suit your needs, but have you looked at sqlite?
(http://sqlite.org)

There is a non-free version (from the same author) that has support for
encrypted databases. (http://www.hwaci.com/sw/sqlite/prosupport.html)

Rgds,
Mark.

Dec 12 '05 #27

P: n/a
"Mark" <ma****@tiscali.nl> wrote in message
Don't know if it would suit your needs, but have you looked at sqlite?
(http://sqlite.org)

There is a non-free version (from the same author) that has support for
encrypted databases. (http://www.hwaci.com/sw/sqlite/prosupport.html)

Very interesting.
Dec 12 '05 #28

P: n/a

Martin wrote:
Why else are you posting in a SQL Server group?


To learn about the general topic of database security rather than one
specific hypothetical example I concocted that is admittedly too narrow.

In general terms, the link I found reveals part of the problem a little.
The issue will be an important one to consider with computing in the palm of
your hand. These days databases might find themselves being carried around
on laptop or palm machines. In the not-too-distant future, terabyte-class
portable storage might not be too far fetched an idea. Databases are sure
to go mobile. With this will come serious security issues as the files
themselves could fall into the wrong hands very easily.

Yet another hypothetical scenario is a travelling salesman having a database
of clients, notes and general business intelligence on a notebook computer.
It is more than likely that, today, stealing that data would just take a
very quick copy of the database file/s. I'd venture to say that most db's
are not designed to have strong security at the file level. I understand
why (cpu and system load in managing constant encrypt/decrypt processing)
but it is disturbing nevertheless.

-Martin


If you want a broader view, you might want comp.databases.theory. I'm
posting from comp.databases BTW.

It comes down to where you build the wall. Preventing access to the
machine would be the first and best line of defense. Protecting the
data within the DB (encrypted fields) is the last line of defense. Some
situations require multiple levels of defense.

Bottom line is that there is no simple solution. If a hacker has access
to the data files, you've already lost some major security battles.

Dec 14 '05 #29

P: n/a
> If you want a broader view, you might want comp.databases.theory. I'm
posting from comp.databases BTW.


Thanks, I'll browse that NG and might post after studying the matter furter.

-Martin
Dec 15 '05 #30

This discussion thread is closed

Replies have been disabled for this discussion.