Secure Database Systems

[Sarah Tanembaum]

I was wondering if it is possible to create a secure database system
using RDBMS(MySQL, Oracle, SQL*Server, PostgreSQL etc) and web
scripting/programming language(Perl, PHP, Ruby, Java, ASP, etc) combination?

I have the following in mind:

I wanted to store all my( and my brothers and sisters) important
document
information such as birth certificate, SSN, passport number, travel
documents, insurance(car, home, etc) document, and other important
documents
imagined in the database.

The data will be entered either manually and/or scanned(with OCR). I
need to
be able to search on all the fields in the database.

We have 10 computers(5bros, 4sisters, and myself) plus 1 server with I
maintained. The data should be synchronize/replicate between those
computers.

Well, so far it is easy, isn't it?

Here's my question:

a) How can I make sure that it secure so only authorized person can
modify/add/delete the information? Beside transaction logs, are there
any
other method to trace any transaction(kind of paper trail)?

Assuming there are 3 step process to one enter the info e.g:
- One who enter the info (me)
- One who verify the info(the owner of info)
- One who verify and then commit the change!
How can I implement such a process in RDBMS and/or PHP or any other web
language?

b) How can I make sure that no one can tap the info while we are
entering
the data in the computer? (our family are scattered within US and
Canada)

c) Is it possible to securely synchronize/replicate between our
computers
using VPN? Does RDBMS has this functionality by default?

d) Other secure method that I have not yet mentioned.

Anyone has good ideas on how to implement such a systems?

Thanks

 

[Carl Youngblood]

Even though this is probably off-topic for a Ruby forum, you have
merely to learn how to use the RDBMS technologies you mentioned to
realize that they all provide security mechanisms that are sufficient
for what you want to do.

 

[Carl Youngblood]

Sorry, I should probably have given you more suggestions of where to look next:

a) Write an password-based authentication system to keep unauthorized
users from accessing data in the database
b) Require your users to access your interface through SSL
c) Some of the RDBMS systems you mentioned allow for replication over
SSL (encrypted) connections, but you probably don't need a distributed
database for the system you are talking about. One server will do
just fine.
d) http://www.bookpool.com/.x/ocyx4nms4m/sm/067232525X

If you want a platform that is a little more tried and true for web
development, I suggest PHP with MySQL or maybe even Sqlite for the
backend database. I love Ruby. It is a lot more pure and pristine
than PHP, but it is still doesn't provide a web development platform
that is as comprehensive (batteries included) as PHP. You have to
roll a lot more of your own code and understand web development more
thoroughly to be able to do it in Ruby. Just my opinion.

Carl

 

[Sarah Tanembaum]

Hi Carl, thanks for your kind advice.

One thing I've learned just yesterday that there are such thing called -
Translucent Database - whereas you can
encrypt and put security(password or group passwd control) on each field.

Is there such a thing out there that mask out non authorized users(even
though if the user is an SA or DBA) from reading sensitive information. The
field itself is not encrypted/protected, just the information stored in the
field is encrypted/protected. Its kind of /etc/passwd database for the
unix/linux systems.

My problem with the regular RDBMS is that if you are DBA/SA, you own the
data/information. Perhaps I'd like to put some control on who can
read/modify/add/delete the data.

Is the control should be in the database or the programming language such as
Java, Ruby, PHP, ASP, PERL, etc?

Thanks again.

Sarah

 

[Carl Youngblood]

What you are referring to is actually a book called TRANSLUCENT
DATABASES written by Peter Wayner. Read more about it here:
http://www.wayner.org/books/td/. I've read it, and although it was a
bit cheaply edited, it is a good book worth reading and has some
pretty good advice. One thing Wayner does little to consider is the
ease-of-use and the performance factors. You can encrypt individual
fields in your database with a password known only to the user, but if
the user forgets his/her password, that's all she wrote. The data is
for all intents and purposes gone. Wayner does suggest a variety of
methods, but most of them rely on secret information in the user's
possession. I would argue that most applications favor usability over
security--i.e., most people don't want to lose all their data if they
forget their password, so most applications are designed with a
database where data is stored in the clear or at least where it is
only obfuscated by a password that is recoverable. The performance
factor is also a big deal. You don't want to have to decrypt every
row of a 100,000 row result set.

TRANSLUCENT DATABASES advocates practices that can be used on any
database, so you don't need to worry about whether or not your RDBMS
"supports" this "feature" or not.

If you are concerned about someone being able to see your data then I
would say don't use a shared hosting solution. Go for something like
escapebox.net or linode.com. Then you _are_ the administrator and you
have total control over your data.

To answer your last question, I'm not sure I understand what you mean
by "control," but if you're saying what I think you're saying then I
believe you should use whatever security measures are available to you
at the various levels of your system, such as verifying file ownership
settings are secure, setting up a decent user scheme on the database
layer that doesn't allow users to do more than they need to, requiring
SSL access to your web app, and using a user authentication system for
the front end.

Carl

 

[Randy Lawrence]

I was wondering if it is possible to create a secure database system
using RDBMS(MySQL, Oracle, SQL*Server, PostgreSQL etc) and web
scripting/programming language(Perl, PHP, Ruby, Java, ASP, etc) combination?

I have the following in mind:

I wanted to store all my( and my brothers and sisters) important
document
information such as birth certificate, SSN, passport number, travel
documents, insurance(car, home, etc) document, and other important
documents
imagined in the database.

The data will be entered either manually and/or scanned(with OCR). I
need to
be able to search on all the fields in the database.

We have 10 computers(5bros, 4sisters, and myself) plus 1 server with I
maintained. The data should be synchronize/replicate between those
computers.

Well, so far it is easy, isn't it?

Here's my question:

a) How can I make sure that it secure so only authorized person can
modify/add/delete the information? Beside transaction logs, are there
any
other method to trace any transaction(kind of paper trail)?

Assuming there are 3 step process to one enter the info e.g:
- One who enter the info (me)
- One who verify the info(the owner of info)
- One who verify and then commit the change!
How can I implement such a process in RDBMS and/or PHP or any other web
language?

b) How can I make sure that no one can tap the info while we are
entering
the data in the computer? (our family are scattered within US and
Canada)

c) Is it possible to securely synchronize/replicate between our
computers
using VPN? Does RDBMS has this functionality by default?

d) Other secure method that I have not yet mentioned.

Anyone has good ideas on how to implement such a systems?

Thanks

Some suggestions:

1. Use 2-factor authentication. So if a password gets stolen, it isn't
enough by itself to access data.

2. Use 128-bit encryption for storage using secure algorithm (blowfish,
twofish or AES.

3. Use 128-bit encryption for communication.

4. Use secure key exchange protocols.

5. Use secure key generation algorithm.

6. Use digital signatures and public-key encryption where appropriate.
Client should authenticate server and server should authenticate
client to eliminate man-in-the-middle attacks.

7. Take a look at Groove.net products to see how they designed their
secure p2p shared-workspace applications.

8. Use firewalls to restrict access to communication ports based on IP
addresses or range of addresses or ip countries. Why allow access from
Chinese or Russian IP addresses if all your family members are in USA?

IMHO, the openssl interface in Ruby 1.8.1-snapshot makes this fairly
easy to do compared to other scripting languages.

 

[Randy Lawrence]

I was wondering if it is possible to create a secure database system
using RDBMS(MySQL, Oracle, SQL*Server, PostgreSQL etc) and web
scripting/programming language(Perl, PHP, Ruby, Java, ASP, etc) combination?

I have the following in mind:

I wanted to store all my( and my brothers and sisters) important
document
information such as birth certificate, SSN, passport number, travel
documents, insurance(car, home, etc) document, and other important
documents
imagined in the database.

The data will be entered either manually and/or scanned(with OCR). I
need to
be able to search on all the fields in the database.

We have 10 computers(5bros, 4sisters, and myself) plus 1 server with I
maintained. The data should be synchronize/replicate between those
computers.

Well, so far it is easy, isn't it?

Here's my question:

a) How can I make sure that it secure so only authorized person can
modify/add/delete the information? Beside transaction logs, are there
any
other method to trace any transaction(kind of paper trail)?

Assuming there are 3 step process to one enter the info e.g:
- One who enter the info (me)
- One who verify the info(the owner of info)
- One who verify and then commit the change!
How can I implement such a process in RDBMS and/or PHP or any other web
language?

b) How can I make sure that no one can tap the info while we are
entering
the data in the computer? (our family are scattered within US and
Canada)

c) Is it possible to securely synchronize/replicate between our
computers
using VPN? Does RDBMS has this functionality by default?

d) Other secure method that I have not yet mentioned.

Anyone has good ideas on how to implement such a systems?

Thanks

One more thing...ssh tunneling is your best friend. If the
communication protocols available in your RDBMS server or client aren't
sufficiently secure, simply using SSH tunneling.

 

[Sean O'Dell]

Is the control should be in the database or the programming language such
as Java, Ruby, PHP, ASP, PERL, etc?

I personally usually develop a layer above the database where business logic
goes, and put user-level authentication there, and give the business logic
layer full access to the database. It's simple to encrypt individual fields
at the business logic layer.

Sean O'Dell

 

[Gordon Burditt]

I was wondering if it is possible to create a secure database system

using RDBMS(MySQL, Oracle, SQL*Server, PostgreSQL etc) and web
scripting/programming language(Perl, PHP, Ruby, Java, ASP, etc) combination?

It depends on how you define 'secure' and what your security policy
is. For example, MySQL allows database connections to be encrypted
with SSL. I suspect that some of the others do also. Also, most
web servers can be set up in a configuration that supports https.
And pretty much every web browser supports https.

I have the following in mind:

I wanted to store all my( and my brothers and sisters) important
document
information such as birth certificate, SSN, passport number, travel
documents, insurance(car, home, etc) document, and other important
documents
imagined in the database.

Why is this not several SEPARATE databases?

The data will be entered either manually and/or scanned(with OCR). I
need to
be able to search on all the fields in the database.

Do you actually have a program that does decent OCR of handwriting,
starting from images? It would be useful to be able to take a bunch
of scanned cancelled checks and search for, say, "Auto insurance"
(a comment I'd probably write on the memo line of the check).

It is very difficult to get anything useful out of searching images
stored as binary blobs in a database table.

We have 10 computers(5bros, 4sisters, and myself) plus 1 server with I
maintained. The data should be synchronize/replicate between those
computers.

MySQL allows replication between different servers, again with SSL
for the replication connection. Generally, though, changes need to
be entered at the master.

Well, so far it is easy, isn't it?

Here's my question:

a) How can I make sure that it secure so only authorized person can
modify/add/delete the information? Beside transaction logs, are there
any
other method to trace any transaction(kind of paper trail)?

Who's an authorized person?
At what granularity do you need the access controls? For example,
MySQL has access controls on individual table columns, so a specific
person (database login) may be allowed read-write, read, or no
access to the 'SSN' column. However, I do not believe it has any
kind of access restrictions that would allow one to read and alter
the SSN in THEIR OWN RECORD ONLY. If you put each person's data
in different tables, or different databases, that would make access
control better, but it makes it harder to search for things like
"whose wills are kept by <name of attorney>"?

It is possible for PHP to access the data using its own database
login (for which it will likely have near-full privileges), and let
the PHP code enforce restrictions like "a user may see only his own
SSN". PHP would have its own set of web users (with their own
passwords, privileges, etc. enforced by PHP but with the info kept
in the database). This does mean, however, that anyone with PHP's
database login gets to see all the data. It also means that the
PHP code that enforces the security rules could leak information
if the security policy is not implemented correctly. And implementing
all that PHP code CORRECTLY takes work.

A suggestion for your PHP web pages: log the time, date, IP address
the request came from, user name (or other form identifying the
requester), whether the password was correct (log the bad attempts
too!), what they accessed, and what they changed, and what the old
values of the changed fields were.

MySQL has query logs (however, if there is one database user, PHP,
for all the web page users, it will not log which query was made
on behalf of who). Your PHP application can log what screens who
viewed and what changes were entered. This takes WORK. PHP will
not do all that logging itself automatically.

Assuming there are 3 step process to one enter the info e.g:
- One who enter the info (me)

Now, right here, you've got a security problem. You see all the data.
And you shouldn't.

- One who verify the info(the owner of info)
- One who verify and then commit the change!

For a multi-step process like this you need some place in the database
to store pending changes. What is your access policy for THOSE?
Can I, who entered the data, see the data I entered while it is waiting
for the owner of the data to approve it?

How can I implement such a process in RDBMS and/or PHP or any other web
language?

b) How can I make sure that no one can tap the info while we are
entering
the data in the computer? (our family are scattered within US and
Canada)

Use SSL web pages (https). SSL between the web server and the
database matters less if the web server and the database are on the
same machine or on a local LAN, and direct access to the database
is not allowed except on the same machine or within the local LAN.
But SSL between the web server and various users' browsers all over
the continent is a must. Even if you use a VPN, browsers tend to
take better care of SSL data (like not leaving it around in a cache).

c) Is it possible to securely synchronize/replicate between our
computers
using VPN? Does RDBMS has this functionality by default?

VPN is typically not a feature of a RDBMS (neither is "wireless
802.11g", or DSL, or IP over Avian Carriers - a RDBMS typically
runs over IP, and a VPN uses some means to transport IP securely).
A RDBMS that can replicate over the net should be able to transparently
replicate over a VPN if you set up the VPN to encrypt all traffic.
There is nothing inherently wrong with using multiple security
layers (e.g. https over IPSEC over SSH tunnel over WEP-enabled
802.11g, although this is a bit extreme) unless the overhead just
gets to be unacceptable.

MySQL supports replication over a SSL database connection, by itself
without needing to install a VPN. (Having both is not an unreasonable
choice, though).

Is there a need for users of this database to access data on the
road, away from their VPNs? For example, several family members
are on a trip, there's an accident, and one of them urgently needs
medical history information for one of the others.

d) Other secure method that I have not yet mentioned.

Another thing to think of is encrypting data *IN* the database, and
if you do that, how you manage keys. Presumably some of this info
is being kept to ensure that it is available if the owner of the
data dies, which may make this option less desirable.

Anyone has good ideas on how to implement such a systems?

First, you need to decide on your security policy (especially the part about
legitimate users snooping on other legitimate users data). You also need to
decide what functions are needed. A RDBMS can generally "search on anything"
but a web page, unless it lets you enter an arbitrary SQL query, usually doesn't.

Gordon L. Burditt

 

[Michael Austin]

I was wondering if it is possible to create a secure database system
using RDBMS(MySQL, Oracle, SQL*Server, PostgreSQL etc) and web
scripting/programming language(Perl, PHP, Ruby, Java, ASP, etc) combination?

I have the following in mind:

I wanted to store all my( and my brothers and sisters) important
document
information such as birth certificate, SSN, passport number, travel
documents, insurance(car, home, etc) document, and other important
documents
imagined in the database.

they are actually going to allow you to do this?? I certainly
wouldn't... because if you didn't do it right, all of you would be
subject to identity theft.

<snippage>

Michael Austin.

 

[Sarah Tanembaum]

Thanks Carl.

The goal is to be able to control down to a field level most efficiently. If
the only trade of for good security is performance, I can live by that BUT
not functionality. It should be transparent and ONLY the authorized user can
view the field in cleartext.

Ideally, if somehow I can rest the responsibility to the owner of the
information(user configurable) whether its the whole record or part of the
record(some fields are read-only public, some fields read-write for
restricted group, and some are for the owner eyes only), then I can think of
other security prevention without sacrifying usability.

Does any of the opensource/commercial (object or relational) DBMS support
field level control? Personally, I prefer the database be the custodian of
this sensitive data, rather than the programming/scriptiong language
controlling the business logic. Is this make any sense?

Thanks

Sarah

 

[Sarah Tanembaum]

Hi Sean, I think we're getting there. As my question to Carl, is there any
available database system that include those (configurable) business logic
embedded in their database?

Sarah

 

[Carl Youngblood]

Sarah, at this point I think you should read the book(s) I mentioned.
As I said earlier, every RDBMS I know of will allow you to do the
things that Wayner talks about in TRANSLUCENT DATABASES. It's all up
to you as the programmer to decide how you want to encrypt or
obfuscate the different fields.