0

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.

Thomas Chia
  • 79
  • 1
  • 7

2 Answers2

0

You dont need for each user, that visit your page, an MYSQL user-account. Go and set up some MYSQL User Accounts for groubs, like:

PUBLIC_USER for all user, that arent logged in with less rights.

PROTECTED_USER for logged user with more right.

PRIVATE_USER for users with maximal right, but less rights as you ROOT account.

You can make more groubs if needed, but dont make an MYSQL User Account for each visitor of you page.

There is really no need for.

It is very bad practise to let visitor run script that creates user in/for MYSQL.

And for the hole user/visitor Account Management create a table for it, dont you the MYSQL core tables.

e.g. user_account with username, password, ....

JustOnUnderMillions
  • 3,741
  • 9
  • 12
  • I assume under this structure the admin is still needed to declare which group a new user belongs to? – Thomas Chia Dec 11 '15 at 06:49
  • But that is PHP Logic an can be coded, like: user firsttime login becomes just protected_user. Maybe later he can become private_user (On Code Logic or By Admin) – JustOnUnderMillions Dec 11 '15 at 06:51
  • `declare which group a new user belongs to?` its all about the logic of your application. – JustOnUnderMillions Dec 11 '15 at 06:53
  • `create a "real" user with the appropriate privileges` Just thing global about what appropriate privileges should users have, create groubs from that, and bind users to the needed group. So: Before first Login all user should have PUBLIC_USER groub, after that you(Admin) or your code(logic) can change this groub. Thing that is simple and clear. – JustOnUnderMillions Dec 11 '15 at 06:57
  • And seperate full MYSQL USER credentials (for db connection) from WEBSIDE USER ACCOUNT credentials (for user login on page)! – JustOnUnderMillions Dec 11 '15 at 06:59
0

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.

J. A. Streich
  • 1,683
  • 10
  • 13
  • Ok, so the structure is something like: Minimally one app account that is a database user (like user:root); All users of the web app ultimately have their queries sent through this app account; Authentication is handled by checking web app login details with the app_users table; – Thomas Chia Dec 11 '15 at 06:44
  • Correct. Your login will basically be a query for user_id of the user whose username is X and password hashes to Y... You'd stick that into the session, and now you know who this user is. – J. A. Streich Dec 11 '15 at 06:50
  • And since you'd be storing password hashes you should read up on the "right" way to do it: https://secure.php.net/manual/en/book.password.php – J. A. Streich Dec 11 '15 at 06:52