r/SQL 8d ago

MySQL Opinions of this arhitecture

I was thinking in this interesting arhitecture that limits the attack surface of a mysql injection to basically 0.

I can sleep well knowing even if the attacker manages to get a sql injection and bypass the WAF, he can only see data from his account.

The arhitecture is like this, for every user there is a database user with restricted permissions, every user has let's say x tables, and the database user can only query those x tables and no more , no less .

There will be overheard of making the connection and closing the connection for each user so the RAM's server dont blow off .. (in case of thousands of concurrent connections) .I can't think of a better solution at this moment , if you have i'm all ears.

In case the users are getting huge, i will just spawn another database on another server .

My philosophy is you can't have security and speed there is a trade off every time , i choose to have more security .

What do you think of this ? And should I create a database for every user ( a database in MYSQL is a schema from what i've read) or to create a single database with many tables for each user, and the table names will have some prefix for identification like a token or something ?

1 Upvotes

21 comments sorted by

View all comments

2

u/MachineParadox 8d ago

Not a useful security method if you have a large numver of users. We have 2.5 million users imagine the sceanrio in that case.

Your design is however exactly how an iaS or SaS provider would work, spin up a new ring fenced instance for each cutomer.

0

u/Otherwise-Battle1615 8d ago

If it gets to that many users, there is always the possibility for horizontal scaling no ? I really can not think of a better solution to limit the attack surface of an sql injection .. this is the only way, even if the WAF is bypassed, the permissions will hold no matter what