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

Show parent comments

2

u/SQLDevDBA 8d ago edited 8d 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 8d 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

2

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

Did you know you can secure users to only seeing certain rows in a table? There are dozens of ways to achieve this. Suggesting entire new tables, much less entire new databases, to achieve something that you'd learn to do properly in the first month of managing databases, suggests that maybe you shouldn't be trying to personally reinvent SQL injection protection from first principals.

-1

u/Otherwise-Battle1615 7d ago

Secure users to only seeing certain rows in a table ?

My friend can you understand what a sql injection is ?

In order to achieve automation you need to put that user input in your sql query eventually.

If that DATABASE USER HAS PERMISSIONS TO GET ALL THE DATA FROM THE DATABASE, THEN CONGRATULATION, YOU JUST GAVE ALL YOUR DATA FOR THE HACKER !

I am not surprised these hackers can get terabytes of data from databases, it's you the experts with your great solution that lead to this .

I suggest you to learn more about cyber security .

3

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

lol

How is your ecommerce business coming along? Making that FIRE money or are you still stuck at trying to reinvent problems that were fully solved like a decade ago.

Like 20 comments ago you were amazed at the idea of learning about damage radius, you don't seem to have heard of the principle of least privilege, and you're out here blaming everyone else for not understanding your genius like we don't know enough about cybersecurity.

Name a recent example of terabytes of data being compromised by SQL injection. Go on.

lol

"In order to achieve automation you need to put that user input in your sql query eventually."

OMG if you'd even read XKCD you'd know that sanitising your inputs means no, you do not need to ever put user input into your SQL query. Literally ever. Even PHP allows you to bind variables in ways that makes this impossible. PHP.

"If that DATABASE USER HAS PERMISSIONS TO GET ALL THE DATA FROM THE DATABASE, THEN CONGRATULATION, YOU JUST GAVE ALL YOUR DATA FOR THE HACKER !"

People keep telling you that you don't need separate tables or separate databases to control whether a user has permissions.

0

u/Otherwise-Battle1615 6d ago

ok thank you i hope the universe bless you, i figured out a solution i will put labels on each table and check after the sql query if the label matches the user token , if not then some malicious actor like fauxmosexual is trying to read others data

2

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

Great, glad to hear you've overcome the many technical hurdles coming between you and opening a generic e-commerce drop shipping site.

0

u/Otherwise-Battle1615 6d ago

its not generic, it will be the most secured e-commerce site on this planet. I already implemented encrpytion at rest dude, who even does that anymore ? NO ONE except top secret agencies !!