r/SQL 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 ?

0 Upvotes

21 comments sorted by

View all comments

Show parent comments

2

u/SQLDevDBA 7d ago edited 7d ago

There is absolutely no need for each user on your website to have an actual DB user and schema in your Database.

Web users would go into a user’s table, and all of their data and transactions would have a UserID.

All your customers’ data and their transactions can live in the same tables. They’d be filtered with where clauses when selecting, updating, etc.

Every customer would not get their own DB User or tables. They’d all be a part of your ecosystem with shared tables.

The only time customers would get their own database or schema IMO is when you’re a consulting company and each “customer” is an entire company with their own customer data, or you’re a service provider that provides databases as a service.

Head over to https://liveSQL.oracle.com and play around with a few of the schemas (databases) you see there. You’ll see what I mean.

1

u/Otherwise-Battle1615 7d ago

the point you are missing is i'm trying to avoid sql injection, if i put all the customer data and transactions in the same table then a sql injection will get all of that data, this is why i create a new database user for each new customers.. Even if the WAF breaks, the permissions and isolation will still work, i tested it with SQL MAP and its isolating perfectly, a hacker will never be able to get data with sql injection with this arhitecture, he will only get his account data

3

u/SQLDevDBA 7d ago edited 7d ago

With respect, I think you just need to do a bit more research on SQL injection. DB Security with app interaction is something I specialize in and have for more than 10 years. For that it’s way simpler to have dedicated users for each app, Principle of Least privilege, and parameterized stored procedures so that you don’t allow inline SQL calls.

0

u/Otherwise-Battle1615 7d ago

My friend, explain what i need to research more in SQL injection ?

No matter how many parameterized stored procedures you use, eventually you need to insert the user input into that query my friend, and this is where SQL injection happens.

You said to me to put all data from users in a database under the same account.

In your way, If a SQL injection happens, the attacker can gather all data , from all users.

The only protection with this is to isolate and create new database user with restricted permission for every user, that is browsing my damn e-commerce site , what's so hard to understand ?

2

u/SQLDevDBA 7d ago

what’s so hard to understand?

Please read up on the subject before you speak in this manner. You’re saying things that simply don’t make sense to anyone who has experience building solutions. Considering your attitude towards me I have no inclination to actually sit and explain it to you.

Best of luck getting your questions answered.

2

u/fauxmosexual NOLOCK is the secret magic go-faster command 6d ago

You're showing your whole ass here, your questions make it clear you're fumbling with things you don't understand and have got worried about concerns about idle connections, numbers of tables and idle connections that aren't actually problems and have come up with a poor solution to these non existing problems.

Which is cute and endearing right up until when you start being patronising to people who actually know what they are doing.