0

I have two questions about how to partition data across servers.

  1. Can I create and connect two or more databases, with the records correctly associated, using only a mapping table stored in a third (or fourth), separate database to link them? For example, suppose I wanted to store a family tree with info such as date of birth, mother's maiden name, citizenship/immigration status, ethnic/religious affiliation, and so on for each member, but I wanted to partition the info across databases (and machines) to reduce the amount of personally identifiable information (PII) exposure that might occur through a single breach. I want to link a mapping table (stored in its own separate database) with another small database that includes tables of DOB and ethnic/religious affiliation and a few other details, and with a third database that contains the rest of the sensitive information (e.g., mother's maiden name, citizenship/immigration status, and place of birth). Bottom line: if I insert or pull a record, I want to insert/acquire ALL information about a single entity, but as noted above, I don't want to store all of the info in one database. Is it possible to store each database independently, with nothing in common, using only a mapping table from a separate database to link them? If not, what is the minimum that I must include in each database to achieve what I describe?

  2. How would I determine the increase in time/space complexity due to the use of remote databases/multiple machines? The database system I'm contemplating is MySQL, but I'm assuming the answer would tend to be the same across the board.

EDIT: Because the point is to avoid storing all data in any single database/machine, database replication is not what I'm looking for. I want to actually partition the data across servers. Thanks so much for your help!

P.S. Please also note that this measure is not replacing encryption or any combination of security measures -- all of which are also being applied. This is in addition to those protective measures. We have multiple recent examples of millions of people losing entire sets of PII through the breach of a single database.

  • 1
    Of course if any single database is allowed to see data in any other database then compromising one database still compromises the others. – Erik Apr 8 '16 at 18:15
  • 1
    If I have access to all the information in the other machine I don't need to hack the other machine. A customer doesn't care if a hacker got the information by hacking machine A or machine B. The customer only cares if the information is compromised. For example if I hack your web server and sniff all the data it doesn't matter that I hacked the web server and not your database. Similarly if I hack database A and database A is able to run queries against the data in database B then I've hacked both database A and B for all intents and purposes. – Erik Apr 8 '16 at 18:32
  • 1
    So I query web service A to find out what other web services to query. Then I query each one individually for the parts described in web service A. Then the client unifies the data from all the various siloed web services... This can certainly be done, but it seems overly complex and laggy for most applications. Conceptually it is very similar to a social media aggregator service. – Erik Apr 8 '16 at 18:48
  • 1
    It seems like your mind is made up, and I hope you don't experience a big breach. If your security needs are truly beyond standard safe guards I recommend you hire a trusted security expert to help you design your security protocols. Security is non-trivial, and doubly so when you want to deviate from the norm. – Erik Apr 8 '16 at 19:09
  • 1
    I changed your title to avoid "misuse" of the keyword PARTITION. (And shard is not appropriate, either.) – Rick James Apr 9 '16 at 0:57
1

What you describe sounds much like horizontal sharding of sorts. That, of course, can be done. To answer your second question, you determine the performance (time/space) impact of your design by building a prototype system and measuring what's important to you.

However, security by obscurity, which seems to be your ultimate goal, is rarely worth the effort.

There are better ways to protect sensitive data, which include encryption (e.g. storage level or transparent database encryption, which obviously should include encrypting backups); row- and column-level access control at the database level; thorough security policies; audit, including possibly wire-level analysis of database traffic; network hardening and intrusion detection; etc. All these methods work well only in combination; looking only at one aspect of your security landscape is counterproductive.

  • The point is to eliminate the possibility of a hacker acquiring all personal data with a single swipe. If only certain info is stored in a database, then a breach of that database obviously exposes only that data. So the breach of a database storing DOBs, for example, exposes only the DOB, versus DOB plus all other info. Does this solution address that? Sharding typically involves replication, which I need to avoid. I do not want all data in any single location at any time except at the client side after a query (the client pulls from all databases, but no other server collects all info). – Lori Apr 8 '16 at 18:00
  • 2
    Sharding is often combined with replication but it doesn't necessarily involve replication. Horizontal sharding is about splitting some of your table columns onto a separate node, which is what you are describing. Still, your approach might protect your data only in one penetration scenario. I don't think it's worth the effort. – mustaccio Apr 8 '16 at 18:26
  • P.S. As noted in the edit of my original post, this measure is not replacing encryption or any other industry standard for security. It's in addition to them. As is clear from all of the recent data breaches, the standard security measures alone haven't been working. – Lori Apr 8 '16 at 19:23
  • 1
    The "single swipe" is to get to the API that reads data from all the pieces. You have only moved the problem from the database to the application layer. – Rick James Apr 9 '16 at 0:59
  • Please note that my question above was not "Should I do this?" but rather was "How can I best do this?" I understand that readers likely want to know why I want to do this, but the research is too long to explain -- although I do want to point out that over 94% of attacks are directed specifically to servers. I'd just like to hear opinions on how to do what I've asked above, if anyone cares to share that. :) Thanks! – Lori Apr 9 '16 at 4:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.