a simple question regarding AES
ttt...@gmail.com
I want to encrypt data in an already defined database.
My question is using the AES algorithm is there a way I can make sure
that the encrypted value doesn't exceed the columns maximum length?
For example the defined Database has Table Emp with column
Employee_Salary of width 10, I want to encrypt this column is there a
way to make sure that the encrypted value is only 10 characters?
Thanks a lot,
Joseph Ashwood
news:1165232893.9...@j44g2000cwa.googlegroups.com...
For encryption ONLY (this is broken for many purposes), and assuming you
have a non-repeating value available (e.g. line number that won't change),
look into counter mode (CTR).
Joe
Peter van Liesdonk
> My question is using the AES algorithm is there a way I can make sure
> that the encrypted value doesn't exceed the columns maximum length?
>
> For example the defined Database has Table Emp with column
> Employee_Salary of width 10, I want to encrypt this column is there a
> way to make sure that the encrypted value is only 10 characters?
My first guess would be: no.
AES works on blocks of 128 bits, which is 16 bytes. Thus if you want to
use the AES algorithm, you need to store the cipher text in columns
that are a multiple of 16 bytes.
But maybe someone who has more experience with databases can shed a
light on it.
regards,
Peter
Volker Hetzer
In particular, the length information stays visible. Examples
where this can go wrong are TRUE/FALSE and YES/NO. There are probably
more. Also, how do you intend to encrypt NULLs?
Database encryption is not trivial. What do you want to defend
against? Other users? Hardware thiefs (including the police)?
The dba?
How is the data to be legitimately accessed? Does the client
software decrypt the data or is it to be done in the database?
Btw, you /do/ know that by encrypting data you lose a lot of
query optimizations?
And what kind of encryption services does your database offer
natively?
Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.
ttt...@gmail.com
actually i just need my software to
1- read DB content
2- encrypt the DB columns
3- re-enter the content in the same tables, overwriting the old values
and then after some time, i will re-read it and then decrypted back
so i need some encryption algorithm, that will produce the cipher text
of the same size as the input or plain text
any ideas?? can any of the AES modes do so??
Volker Hetzer
> Thanx all :)
>
> actually i just need my software to
> 1- read DB content
> 2- encrypt the DB columns
> 3- re-enter the content in the same tables, overwriting the old values
You know, most databases already offer encryption.
>
> and then after some time, i will re-read it and then decrypted back
Thus brings to mind a virus that holds user data hostage until a fee
is paid.
>
> so i need some encryption algorithm, that will produce the cipher text
> of the same size as the input or plain text
Can your database cope with binary data in character or number columns?
If not, you've lost anyway.
Even if it can, I'd forget about it.
I'd add a binary-capable column of the smallest multiple of 16 (bytes)
that is larger than the plaintext column size.
Then I'd encrypt the data into that column, using, for instance, LRW mode
and set the plaintext columns to NULL. You might need a second column
for the tweak.
Do you want to be able to detect tampering? Then you need a checksum
column too.
> any ideas?? can any of the AES modes do so??
CTR, but it needs serious bookkeeping.
LRW is not a standardized mode but it's designed to solve the class
of problems yours appear to belong into.
But again, unless you're using a real toy database, it's likely that
your database vendor has solved that problem for you already much
better than you can ever hope to do.
ttt...@gmail.com
actually, i'll be using MSSQL for my DB.
the problem is as follow, i want to develop some small exe file that
will read the fields of unencrypted DB and encrypt it field by field.
actually i'll get this DB from a client and he doesn't want me to view
the DB content, its already an exisiting one, so i should develop him
some exe file that he will run on this DB and will encrypt all its
fields, and not the DB as a whole, so i can then take this DB and work
on the encrypted fields instead.
this is the whole issue :(
i have no control on the DB schema or so or even on the way data is
entered to the DB.
i just need to encrypt fields and generate cipher text of same size
ignoring null cases.
will CTR or LRW help me in such a thing, any other ideas or solutions
????????//
thanx for help :) and ur quick reply :)
Tom St Denis
> actually, i'll be using MSSQL for my DB.
> the problem is as follow, i want to develop some small exe file that
> will read the fields of unencrypted DB and encrypt it field by field.
> actually i'll get this DB from a client and he doesn't want me to view
> the DB content, its already an exisiting one, so i should develop him
> some exe file that he will run on this DB and will encrypt all its
> fields, and not the DB as a whole, so i can then take this DB and work
> on the encrypted fields instead.
And how does he know the EXE doesn't just fire off the DB entries over
the net to your machine?
> this is the whole issue :(
>
> i have no control on the DB schema or so or even on the way data is
> entered to the DB.
Well the schema could remain similar (e.g. similar columns in the
tables) but why must the types remain the same?
> i just need to encrypt fields and generate cipher text of same size
> ignoring null cases.
Without an IV, CTR mode is weak, and LRW doesn't support partial
blocks.
Maybe you should tell your client that cryptography isn't magical fairy
dust that you can just sprinkle on as an afterthought to make all your
booboos go away. My suggestion is to re-specify the columns to be
wider, and then just LRW or XEX encrypt the entries.
I dunno if MSSQL has a built-in security schema but it's worth
investigating.
Tom
Volker Hetzer
> Thanx Volker fo rteh gr8 help and time :D
>
> actually, i'll be using MSSQL for my DB.
> the problem is as follow, i want to develop some small exe file that
> will read the fields of unencrypted DB and encrypt it field by field.
> actually i'll get this DB from a client and he doesn't want me to view
> the DB content, its already an exisiting one, so i should develop him
> some exe file that he will run on this DB and will encrypt all its
> fields, and not the DB as a whole, so i can then take this DB and work
> on the encrypted fields instead.
>
> this is the whole issue :(
field is encrypted, i.e. a database full of nonsense?
Why can't he just give you the table structure and let you fill
it with your own test data?
SQLServer surely can export a schema definition?
In any case I suggest you ask in comp.databases.ms-sqlserver. This is
more a database problem than an AES problem. SQLserver has an encryption
API and can do the whole thing (if this is what you really want) at SQL
level. So your client can use a small sql script or transact sql file
to do that.
A further advantage of that approach is that, if something goes wrong,
it was definitely the action of the client and not one of your programs.
I've added comp.databases.ms-sqlserver to the group list so my
posting should show up there too.
As for encryption, I fear there is no easy way to encrypt safely
under the conditions you want. Stream ciphers don't extend the
plaintext but are totally unsafe (i.e. you can easily find out
your clients data) if the stream is reused. If the stream is
not reused, all foreign key relations in your database will break.
So, you'd need to manage the reuse on a per-relation base and this
takes about as much effort as just copying the table structure and
writing a small program to generate test data.
Block ciphers either extend the data or are unsafe.
And both generate binary data which messes up your character and number
columns, bot to mention enumerations. They will also break constraints
if there are any and likely betray to you every field that is NULL.
In short: get the schema definition and write a small program that fills
it with test data.
David Portas
> ttt...@gmail.com schrieb:
> > Thanx Volker fo rteh gr8 help and time :D
> >
> > actually, i'll be using MSSQL for my DB.
> > the problem is as follow, i want to develop some small exe file that
> > will read the fields of unencrypted DB and encrypt it field by field.
> > actually i'll get this DB from a client and he doesn't want me to view
> > the DB content, its already an exisiting one, so i should develop him
> > some exe file that he will run on this DB and will encrypt all its
> > fields, and not the DB as a whole, so i can then take this DB and work
> > on the encrypted fields instead.
> >
What work do you need to do with the encrypted database? For most
purposes I'd say that what you have proposed is impractical and
probably impossible. If you encrypt the database in its entirity:
You won't be able to create or enforce constraints
You won't be able to index it effectively
You won't get any sensible performance metrics
You won't be able to create accurate test cases
In short, you won't really have a database to work with. So if you need
to do any development work I suggest you create some representative
test data for yourself instead.
However, SQL Server 2005 does have encryption built in to the engine,
including support for AES.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Erland Sommarskog
> ttt...@gmail.com schrieb:
>> actually, i'll be using MSSQL for my DB.
>> the problem is as follow, i want to develop some small exe file that
>> will read the fields of unencrypted DB and encrypt it field by field.
>> actually i'll get this DB from a client and he doesn't want me to view
>> the DB content, its already an exisiting one, so i should develop him
>> some exe file that he will run on this DB and will encrypt all its
>> fields, and not the DB as a whole, so i can then take this DB and work
>> on the encrypted fields instead.
Actually, we had this sort of a problem with one of our customers, and
we developed a very cheesy low-budget solution. To our defense, I should
add that it was the customer's own idea.
In our case, the problem is that the customer cannot let us into the
database for support cases, if their customer data is visible, due the
regulations on financial secrecy in the country where they are active.
What they do when they need us to access the database, is that they
pull a handle (that is, they run a small application), that copies
all sensitive customer information to a database we do not have access
to, and then they replace this data with a string of question marks.
Once they are done, they copy the real data back.
That could serve as inspiration for ttt.tas's problem. Rather than
encrypting the entire database, just overwrite the sensitive information
with nonsense, and save the real database locally at the client.
Provided that there is a need to merge back at all. If there is no
need to merge back, then you can be more frivolous with destroying
the current information.
Note that depending on the purpose of getting a local copy, the
operation may be more or less successful. If the purpose is to
examine performance problems, replacing a lot of data can change
presumption, resulting in problems in reproducing performance
issues.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Carlos Moreno
> But again, unless you're using a real toy database, it's likely that
> your database vendor has solved that problem for you already much
> better than you can ever hope to do.
I'm curious about how these encryption features work (in particular,
how do they address all the questions you raised in your previous
post?)
What about key management?
I always found that the only "natural" way to combine encryption
with databases is with public-key cryptography; if I store data
encrypted (encrypted by the client, not by the DB backend) with a
public-key, then there's no issue with key management --- the data
is read and then decrypted with the corresponding private key,
which may be in a different physical host (of course, there's
the key management issue in that the privaet key has to be kept
in a secured place --- but that's an implementation/housekeeping
detail, and not a fundamental problem).
What encryption features are typically available in these DB
systems, and how do they work? (I'm curious about a brief
description of how they work and how do they provide the
encryption capabilities and how do they address the key
management issues).
Thanks,
Carlos
--
Volker Hetzer
> Volker Hetzer (firstname...@ieee.org) writes:
>> ttt...@gmail.com schrieb:
>>> actually, i'll be using MSSQL for my DB.
>>> the problem is as follow, i want to develop some small exe file that
>>> will read the fields of unencrypted DB and encrypt it field by field.
>>> actually i'll get this DB from a client and he doesn't want me to view
>>> the DB content, its already an exisiting one, so i should develop him
>>> some exe file that he will run on this DB and will encrypt all its
>>> fields, and not the DB as a whole, so i can then take this DB and work
>>> on the encrypted fields instead.
>
> Actually, we had this sort of a problem with one of our customers, and
> we developed a very cheesy low-budget solution. To our defense, I should
> add that it was the customer's own idea.
Volker Hetzer
> Volker Hetzer wrote:
>
>> But again, unless you're using a real toy database, it's likely that
>> your database vendor has solved that problem for you already much
>> better than you can ever hope to do.
>
> I'm curious about how these encryption features work (in particular,
> how do they address all the questions you raised in your previous
> post?)
>
> What about key management?
I can only speak for oracle and about that not much even. But
all the information is public and at www.oracle.com. You might
need a (free) otn account.
So, here's the little bit that I know:
Oracle provides two solutions for encrypting data within the database:
- transparent encryption. This keeps data encrypted on disk but
decrypts it when a user does a select on it. Ditto
for inserts and updates. You can encrypt with or without salt, if
you encrypt without salt, equality operations won't be slowed down.
You specify a password upon database startup and that's basically it.
Then you can create and access encrypted columns. The documentation
mentions lots of stuff about a thing called "wallet" and encryption
modules but the point is that it was designed to comply with regulatory
requirements while still having an usable system. About the only case
this protects against is if somebody steals the server or the backups.
http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14268/toc.htm
- dbms_crypto. This is a package that lets you encrypt and decrypt
data on your own.
http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14266/apdvncrp.htm#i1007112
Generally, there's a whole lot of guidelines to secure a database, encryption
being only a very small part of it:
http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14266/toc.htm
However, all the oracle suggested guidelines assume that the database hasn't
been hacked (i.e. modified for instance to encrypt not at all or so).
dbms_crypto protects against the administrator, provided he doesn't replace
the package.
Personally, I'd begin with a secure location for the server, learning
to use the broom on my own and only after that I'd start to bother about
the fancy stuff.
> I always found that the only "natural" way to combine encryption
> with databases is with public-key cryptography; if I store data
> encrypted (encrypted by the client, not by the DB backend) with a
> public-key, then there's no issue with key management
At that point there is no issue with the database either.
> What encryption features are typically available in these DB
> systems, and how do they work?
To the end user they typically offer symmetric encryption only.
Public key stuff is used to encrypt the data in transit or to
authenticate the database user.
Most of database security is different anyway.
You authenticate the user with whatever is there, like
certificates, kerberos, radius or plain passwords and then
control what the user sees (or can do) with permissions.
In such scenarios, physical theft is the only thing encryption
protects against.
Erland Sommarskog
> Erland Sommarskog schrieb:
>> Actually, we had this sort of a problem with one of our customers, and
>> we developed a very cheesy low-budget solution. To our defense, I should
>> add that it was the customer's own idea.
>
> Out of curiosity, what would the high-budget solution have looked like?
The initial idea was to use the new encryption facilities in SQL 2005,
but I do not really like that, since it would require the users to work
with multiple passwords. And the customer wanted to go live with a version
of our product that does not support SQL 2005, so encryption was not an
option at that stage anyway.
Instead my suggestion was to have a second database on a second server.
This database would have all the sensitive information. The few functions
that needs to access it would connect to that database with integrated
security (so the users would not need any extra passwords). If the user
is not authorised to that database, the GUI would just display the dummy
data from the main database. It would fall on the business layer to make
that second connection; it would not be in the stored procedures.
Volker Hetzer
> Volker Hetzer (firstname...@ieee.org) writes:
>> Erland Sommarskog schrieb:
>>> Actually, we had this sort of a problem with one of our customers, and
>>> we developed a very cheesy low-budget solution. To our defense, I should
>>> add that it was the customer's own idea.
>> Out of curiosity, what would the high-budget solution have looked like?
>
> The initial idea was to use the new encryption facilities in SQL 2005,
> but I do not really like that, since it would require the users to work
> with multiple passwords.
encrypted for each user separately, with the users normal password.
Then the user (or his program anyway) could look up the encrypted
password for the user and decrypt it with the users password.
Erland Sommarskog
> Hm. One could have /one/ password for the set of authorized users,
> encrypted for each user separately, with the users normal password.
> Then the user (or his program anyway) could look up the encrypted
> password for the user and decrypt it with the users password.
I'm not sure that works with SQL Server encryption, but I would need to
think both twice and thrice to say for sure. Then again,
encryption/descryption could also be done client-side.
Volker Hetzer
> Volker Hetzer (firstname...@ieee.org) writes:
>> Hm. One could have /one/ password for the set of authorized users,
>> encrypted for each user separately, with the users normal password.
>> Then the user (or his program anyway) could look up the encrypted
>> password for the user and decrypt it with the users password.
>
> I'm not sure that works with SQL Server encryption, but I would need to
> think both twice and thrice to say for sure. Then again,
> encryption/descryption could also be done client-side.
above just makes sure that someone who hacked himself into
the database without a legitimate password cannot access
the data.
The normal access would go like this:
given a table keys
(
table_name varchar2(32),
column_name varchar2(32),
user_name varchar2(32),
encrypted_key <some binary>
);
Access would be like this:
- select encrypted_key from keys
where
table_name='XXX'
and column_name='YYY'
and user_name='myself';
- client decrypts key with login password
- client has the key to en-/decrypt the columns
Key change presents a problem.
David R. Tribble
ttt.tas schrieb:
>> actually, i'll be using MSSQL for my DB.
>> the problem is as follow, i want to develop some small exe file that
>> will read the fields of unencrypted DB and encrypt it field by field.
>> actually i'll get this DB from a client and he doesn't want me to view
>> the DB content, its already an exisiting one, so i should develop him
>> some exe file that he will run on this DB and will encrypt all its
>> fields, and not the DB as a whole, so i can then take this DB and work
>> on the encrypted fields instead.
>
I'm not clear on what you're saying.
If you're encrypting the live data to create test data (a one-way
trip of the data), you only need to use a method that takes
printable n-byte field values and hashes them into printable n-byte
values. This is fairly easy to by hashing each n-byte field,
truncating to n bytes, then converting to printable characters.
This assumes that the encrypted data does not need to be
decrypted.
On the other hand, if you want the encrypt the live data, then
process it on your end, then decrypt it back on the user's end,
perhaps your best bet is for the user to create a second database
containing all the live field values in his dB, indexed by unique
random n-byte values (which can be generated in various ways).
He then creates a copy of his live database, substituting the random
values for the live values. You operate on this copy dB, then return
it to him. He then reverses the process, replacing the random field
values with the old live data values. Obviously the replacement
process should only be applied to alphanumeric fields that you
do not intend to modify (e.g., names, addresses, SSNs, credit
card numbers, etc.).
-drt