I am very new to programming and have a theory-based question relating to user management in databases. I can't quite think how this would be asked so if it's a repeat question please feel free to link.
tldr: The core problem is that a new user needs a connection to the database in order to perform CREATE USER... but to establish that connection, they need to have been CREATE-d already at some point.
Full Question:
If I have a database that I want to allow multiple users to access, I understand that the administrator can create "server-side" users (via CREATE USER), with credentials selected by the user. The database connection can be opened by each user through a login page by inputting their credentials which are subsequently passed in as arguments to the new connection.
However, what if I want to eliminate the need for an administrator to set up new user accounts? That is I want the end user to navigate to a register page, where they choose credentials, hit register and can get access to the database.
My current understanding is that the only way to do this is to create "pseudo-user" accounts. i.e. the registration is simply inserting the new user credentials into a credentials table. Subsequently all users "log in" through the same connection, but must first clear a login page which checks that their input credentials match the ones stored in the credentials table. (This would still need the administrator to create a "real" user with the appropriate privileges, otherwise everyone would be connecting through the root user).
edit: I meant to say this is via php.
Does this sound right?
Thank you community.
You mixing users of the app, with database users. They are rarely, if ever, the same thing.
Your app needs a single user account for mysql, but your app needs to track its own users in the database -- typically in a table called something like app_users. A table that might look like:
id username email passwd_hash salt
-- -------- ----- ----------- ----
01 fred f@a.c sdfasdf3r23 asdwer32
The user of the web app will never have permission directly to the database, but all the application queries will be run through the app's account. Those queries should be either prepared statements, or prepared statements executing only stored procedures. The app account shouldn't be allowed to alter the schema. The app credentials the app will need to connect to the database should be encrypted and stored outside of the web root, but in a folder that the OS user the web server runs as has access to read.
You may have more than one app account for mysql, for various levels of permission, but those are still not the same as user accounts stored in the database.