Re: Recommended User Administration Structure & Privileges?

[Rick James]

The one you bring up is only one of many security issues. And your 'solution' is 'wrong'.

> I plan on using mysql in connection with some php based websites for online account management, blogs, and simple data tracking.

And you _will_ write PHP code to insulate the database from the dumb mistakes the users will make. At at that point, the safety is in _your_ code, and you don't need more than one or two (readonly + read/write) logins to the one database involved.

> I want to create a unique user for each associated database so if one is compromised, it doesn't affect the other databases.

Thousands of databases will lead to performance issues.

For protection, have a second database that contains the login information for the users. Yes, _your_ code, not MySQL's, must maintain info on access to the data. This would involve a 3rd MySQL login -- for the user login page.

Also, think through how you are going to keep track who a user is? A cookie? If (instead) it is in the url, then someone could easily copy/hack a url and get access to someone else's data.

None of the above MySQL logins have access to more than the database they access; none have SUPER. Hence, you (as the admin) still have a "root" login.

But the perhaps the most likely vulnerability is "SQL injection". You _must_ escape anything the user enters before inserting it into SQL. (Or use an API that does the escaping for you.)