Creating (and querying) multiple databases

[Firelord84]

I am designing a database to hold specialized information about a person. It will hold all their general information, and very large amounts of images about that person (each kind of image will be stored in a different table) Currently there are 11 different tables about each person (and more will be added). The customer wants each person to have their own database for security and privacy reasons. (I tried to pitch the idea of one set of tables in which we stuff all the people's information into and assign them all Unique IDs, but they didn't seem to like that based on privacy and security). Will I be able to query all of those DBs to generate reports about these people? If so, how hard will it be? I would really like to avoid storing each person in his own DB--does anyone have any thoughts about how to design this best? I would appreciate any input you have. Thanks in advance for you help.

--Clint

 

[rycamor]

MySQL supports table-level and even column-level user priviledges. When using a single database, there is absolutely no reason to worry about security if you set things up right.

On the other hand, if you use multiple databases, you are creating a big headache for the future, or for anytime you want to assemble statistics, or change database structure, etc... Imagine having to make sure everyone has the same version? The cardinal rule of programming is that any one type of information should be stored in only one place.

If the only consideration is security, just show them this tutorial:

http://www.devshed.com/Server_Side/MySQL/Access/,

and try to convince them that individual databases is a Bad Idea.

 

[Firelord84]

I completely agree with you. Is there any way to restrict access on the row level? The customer wants to allow users access to certain people's records, but not to others. As mentioned above, I was going to include a Unique ID on each row so all the person's information could be tied together. Is there some way to grant this kind of restricted access? Again, thanks in advance for your comments.

--Clint

 

[rycamor]

I doubt there has ever been such a thing as row-level security in a database system, as it doesn't really make a lot of sense. Rows are expected to be always changing, updating, etc...

I really don't know enough about the application architecture or your client's needs to be able to give you a good answer on that. What is the database for, and why would so many people have direct access into it anyway? If this is a database with an application as a front-end, such as a web-based system, you can just restrict access at the application level using programming methods, rather than by creating user-level logins. All my web-based applications have one user: the user used by my PHP programming to connect to MySQL. Everything else is decided at the application level, and can be done quite securely using unique IDs and encrypted password columns in the database, without giving any users direct access into the database.

In your situation, I sincerely doubt that database-level user priviledges is what you need; you should just think in terms of application-level security, and manage everything from there. Be careful, though. What programming language are you using?

 

[Firelord84]

I'm using Java to write my application, and the JDBC for database access. However, the customer also wants the ability to access it through some sort of DB front-end. He wants to give others the chance to run queries and generate reports based on the data being stored. There is, as I mentioned before, a huge concern with who can access which parts of this information.

Here's an example. Let's say we've collected a full set of information about Sam and Jane. Sam agrees to let his information be used to generate reports with, but Jane does not. I need some way of allowing certain users access to Sams records, but not to Jane's--while still allowing them to be stored all together in one set of tables. (or maybe they will have to be stored in table sets grouped by security level...)

If the database was going to be accessed only by my application, I could build in security measures (which I am doing), but becuase the customer also wants to generate statistical reports on the information and I think have to allow the database to be accessed with a front end.

There is the added caveot that not all of the tables will have to be accessable to the staticians (probably any tables used to store images). They will really only be interested in data they can compare.

I hope I've explained things a little more clearly. What the customer wants is very specific. The last version of this product didn't use a database, but it was decided that the ability to generate reports and having all the data in one organized structure was worth changing how we store our information...

I will also mention that this product will be used at several sites. These different sites are going to want to share information between them. I don't know if that should change how I approach this or not...

Again, thanks in advance for you response.

--Clint

 

[rycamor]

Hmm... I see that you have a somewhat more thorny situation here.

My quick answer is that you could perhaps run the queries through some sort of "pre-flight" checker that disallows access to information not in that user's profile. You should have a table that no one but the administrator has access to that keeps user "meta-data", which includes the user's preferences, priviledges, etc...

So essentially you will be filtering every query that comes to you and adding WHERE or ON conditions based on the user's priviledges, and on the checked preferences of each user as the rows are requested.

Or maybe even more simply, you have a couple of "hidden" columns in the user database, which are only available to the administrator and the software, and as you process the users' queries, you add these columns to the conditions of the query.

But overall, this situation requires a fair amount of thought to make it bulletproof. I will let you know if I think of anything else.

 

[Firelord84]

Hmmm... your ideas seem interesting. How can I add something to the query once they enter it? I'm fairly new to SQL as a whole... If a user is accessing the DB through the mySQL frontend, can I do these sort of things with their queries? If so, how? I would be very interested in hearing more on your design thoughts, and possible implementations.

Thanks again,

--Clint

 

[rycamor]

Maybe instead of letting users enter free-form queries, you should make them use a front-end that includes a "query builder". I am assuming here that these users will have read-only access in general, using only be SELECT queries.

So, just make a form, with checkboxes for the columns the user wants to search, and a text entry field for the WHERE clause. If they will need to do JOIN queries, obviously theis form will have to be a little more complex, but you get the idea. This way, your application is what actually builds the query, so you can work in whatever other clauses and restrictions you want, and theoretically it will be transparent to the user.

You should plan on doing a fair amount of testing and debugging on this, though.

 

[Firelord84]

That query builder is a great idea! That way no one can gain access to the database through any other ways except through our applications. Well, thanks so much for you help. I'm starting to get a much better handle on database design.

Thanks again,

--Clint

 

[rycamor]

Where's my star? ;-)