Each physical user will have about 20 tables in the database.

When a new user registers, another 20 tables will be created with access only for their new MySQL account.

Data does not have to be shared between users.

Is this logic more secure than simply having just 20 tables for all the users? And what about performance will be dead or not ?


I just want to be sure that a user who likes to "play" with injections and finds a way around will not be able to access other user data.

  • This doesn't make much sense. Information sharing between databases wouldn't work. How would a post from user a be shown to user b? – Polynomial Aug 8 '12 at 11:56
  • 1
    No, there is no sense at all in this design. Security increases will be minimal, while cost associated with code hassles and storage space would be huge. – user10211 Aug 8 '12 at 12:28
  • 3
    Instead of making table, make a view and keep a one table. On view assign user permission, that he can read only his view, so you would make 20 views for each user. – Andrew Smith Aug 8 '12 at 12:40
  • @Polynomial As I say the data of the users does not have to be shared between users the data is only for them self – Stelios Joseph Karras Aug 8 '12 at 12:46
  • 1
    @AndrewSmith Smith this seems to be a good idea. – Stelios Joseph Karras Aug 8 '12 at 12:50

This is a bad design (or no design at all for that matter).

You should model your tables like you create classes in object oriented programming : each class / table represent a concept, so you should have a User table and some permissions on other tables for each user.

Merise is a great method to design database (and much more but it's mainly used to design databases nowadays). I'm afraid it's a french cultural exception as no other country seems to use it, but if you can find resources in your language it would be of great help with your designs.


Since this is security.stackexchange.com, I'm going to focus on the security question. (I agree with other posters/commenters: this is generally not a good design for long term maintainability. It's probably not great for performance but that's impossible to know without knowing exactly how users -- including you, the admin -- are querying it.)

From a security POV, separating user data into different tables in MySQL will only have benefit if you also set up individual user accounts for each user in MySQL, and then explicitly set their permissions so that they can only access those tables. At that point, the permissions become a huge mess to keep track of, validate, and correct, but it would mitigate the risk of a SQL injection attack.

It would be conceptually easier to give each user their own schema (or "database" in MySQL parlance) and give them privileges to their entire schema. This makes the privileges much easier to audit and still mitigates the risk of SQL injection.

This latter concept isn't totally insane -- lots of servers are configured this way in order to run several different database-backed application on the same server without letting them access each other's data. Whether it makes sense in your case depends, to a large degree, on how many users you have.

I'm not endorsing your design; just pointing out the pros/cons.

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.