Secure Database Systems

Discussion in 'Perl Misc' started by Sarah Tanembaum, Jul 9, 2004.

  1. 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
     
    Sarah Tanembaum, Jul 9, 2004
    #1
    1. Advertising

  2. Sarah Tanembaum

    Ron Savage Guest

    On Fri, 9 Jul 2004 16:41:37 +1000, Sarah Tanembaum wrote:

    Hi Sarah

    Frankly, I'd skip the db route at this time and go tohttp://www.treepad.com/

    The business version has encryption built in. There is a freeware version(TreePad Lite) to tryout.
    --
    Cheers
    Ron Savage, on 9/07/2004
    http://savage.net.au/index.html
     
    Ron Savage, Jul 9, 2004
    #2
    1. Advertising

  3. Sarah Tanembaum

    Keith Keller Guest

    -----BEGIN PGP SIGNED MESSAGE-----
    Hash: SHA1

    On 2004-07-09, Sarah Tanembaum <> wrote:
    > I was wondering if it is possible to create a secure database system


    Sure! Just unplug it.

    [rest of inappropriate crosspost snipped]

    - --keith

    - --
    -francisco.ca.us
    (try just my userid to email me)
    AOLSFAQ=http://wombat.san-francisco.ca.us/cgi-bin/fom

    -----BEGIN PGP SIGNATURE-----
    Version: GnuPG v1.2.3 (GNU/Linux)

    iD8DBQFA7sJnhVcNCxZ5ID8RArUPAJ9HH4g9olmXo2iBUyOUFm+zrvHl5gCfZUPy
    R4OCbgUywvlBWh8sSdTsvOY=
    =pfAK
    -----END PGP SIGNATURE-----
     
    Keith Keller, Jul 9, 2004
    #3
  4. Sarah Tanembaum wrote:
    > 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, Jul 9, 2004
    #4
  5. Sarah Tanembaum wrote:

    > 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.
     
    Randy Lawrence, Jul 9, 2004
    #5
  6. >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
     
    Gordon Burditt, Jul 9, 2004
    #6
  7. Sarah Tanembaum wrote:
    > 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.
     
    Michael Austin, Jul 9, 2004
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. A.M
    Replies:
    5
    Views:
    5,448
    Teemu Keiski
    Jun 8, 2004
  2. Daniel Malcolm
    Replies:
    0
    Views:
    557
    Daniel Malcolm
    Jan 24, 2005
  3. zdrakec
    Replies:
    1
    Views:
    438
    zdrakec
    Jul 25, 2005
  4. Mike
    Replies:
    2
    Views:
    633
  5. Sarah Tanembaum

    Secure Database Systems

    Sarah Tanembaum, Jul 9, 2004, in forum: Ruby
    Replies:
    12
    Views:
    226
    Carl Youngblood
    Jul 10, 2004
Loading...

Share This Page