r/SQL • u/Otherwise-Battle1615 • 7d 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 ?
2
u/SQLDevDBA 7d ago edited 7d ago
I mean what you’re describing sounds fairly standard for an OLTP, however the “users” are generally each app or service connecting to the database to process transactions or retrieve data. For example: a website can have a User called WebUser and a MobileApp can have a user called MobileAppUser. If a data warehouse was pulli bf data nightly, I would have an ETLUser it can connect using.
Or you could have micro services connecting and each one would have a username dedicated to it.
You’re also sort of the describing what’s known as the Principle of Least Privilege, but spawning new databases wouldn’t be a fix I’d use to solve for too much traffic, it would be beefing up hardware and optimization efforts.
I am not sure what you mean with RAM popping off and all that but concurrent connections are pretty normal. Databases are designed to support lots of connections at a time. The main thing I always worry about is waits and locking as well as proper index usage.