1

I'm trying to make my once homey website able to take more than two users. Is it scalable to make a new MySQL user for each person that registers with my website, running

CREATE USER $userName [IDENTIFIED BY [PASSWORD] '$userPass']

each time someone registers? I would then set up mysql_connect() calls in their name.

Alternatively, are there benefits/caveats to doing something like

 CREATE USER genericSiteUser [IDENTIFIED BY [PASSWORD] 'sitePass']

and just putting $userName in a column to keep track of who left what comment, etc? Or is there a third way of doing it better than both of these?

drug_user841417
  • 797
  • 1
  • 7
  • 12

2 Answers2

1

Creating database users is not scalable and also not secure. You should instead create one database user with the least privileges you can (ideally only insert/select/update/delete), and then use a table to keep track of users.

You can then create a foreign key to the user table in your comments table.

Platinum Azure
  • 45,269
  • 12
  • 110
  • 134
  • I thought handling the users in their own table was more common. Can you explain what you mean by a 'foreign key'? Is that what I called 'sitePass'? – drug_user841417 Sep 03 '12 at 02:27
  • 1
    You should never use `CREATE USER` ever (for your WEBSITE'S users). You should just have a table, let's call it `users`, and then use `insert into users values ('newUserName', 'hashedPassword')`. Then your login code should check for a username and password matching what the user typed in. I'm oversimplifying quite a bit-- you need to look at a good tutorial to get the right level of detail without it being overwhelming. – Platinum Azure Sep 03 '12 at 02:30
  • A foreign key is simply a reference to another table. You just put the ID (or whatever other primary key) into the referring table (`comments`) and you can then join on that column when you need to connect a username to a comment. Again, read a tutorial on the concepts so you can get a better idea than I'm describing here. – Platinum Azure Sep 03 '12 at 02:32
  • @Platinum_Azure I appreciate the direction you're giving me. Do you know a tutorial that can examplify [sic] the concept you're talking about? Unfortunately, !so and !g searches for "create a user table mysql" only refer to database users, not a table of actual people users :-( Thanks again. – drug_user841417 Sep 03 '12 at 02:37
  • Take a look at this question: http://stackoverflow.com/questions/4011097/login-registration-system-with-php-and-mysql?rq=1 Use the items in the question as Google search terms and try to piece together your knowledge. If you have specific questions as that comes about, feel free to ask new questions here on Stack Overflow. Hope this helps! (I haven't found a good tutorial actually-- it's been forever since I've worked with PHP and I've been doing a lot of work with web frameworks these days.) – Platinum Azure Sep 03 '12 at 02:52
0

No -- the MySQL CREATE USER command is used to give users access to the MySQL database itself. It should never be invoked by an ordinary application; indeed, good security practice dictates that the database users used by your application should not have the SUPER privileges required to manage users.

As the users of your application are entirely unrelated to the user credentials used to access MySQL, your application should maintain data on its users in a database table, just like any other data used by your application.