Quote:
Originally Posted by
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 :(
Sounds strange. He basically wants to give you a database where /each/
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.
Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.Volker Hetzer wrote:
Quote:
Originally Posted by
ttt.tas@.gmail.com schrieb:
Quote:
Originally Posted by
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/...US,SQL.90).aspx
--|||Volker Hetzer (firstname.lastname@.ieee.org) writes:
Quote:
Originally Posted by
ttt.tas@.gmail.com schrieb:
Quote:
Originally Posted by
>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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog schrieb:
Quote:
Originally Posted by
Volker Hetzer (firstname.lastname@.ieee.org) writes:
Quote:
Originally Posted by
>ttt.tas@.gmail.com schrieb:
Quote:
Originally Posted by
>>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.
Out of curiosity, what would the high-budget solution have looked like?
Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.|||Volker Hetzer (firstname.lastname@.ieee.org) writes:
Quote:
Originally Posted by
Erland Sommarskog schrieb:
Quote:
Originally Posted by
>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.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog schrieb:
Quote:
Originally Posted by
Volker Hetzer (firstname.lastname@.ieee.org) writes:
Quote:
Originally Posted by
>Erland Sommarskog schrieb:
Quote:
Originally Posted by
>>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.
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.
Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.|||Volker Hetzer (firstname.lastname@.ieee.org) writes:
Quote:
Originally Posted by
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.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog schrieb:
Quote:
Originally Posted by
Volker Hetzer (firstname.lastname@.ieee.org) writes:
Quote:
Originally Posted by
>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.
Yes, en-/decryption would be done client-side. The scenario
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.
Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.|||Volker Hetzer wrote:
ttt.tas schrieb:
Quote:
Originally Posted by
Quote:
Originally Posted by
>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
No comments:
Post a Comment