Is this logic security / performance wise or not?

Question

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 ?

EDIT

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.

Answer 1

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.

Answer 2

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.