Why not use the built-in MySQL users and permissions for a website?

Question

I have searched quite a bit on the subjects of encryption, storing passwords, designing secure PHP scripts etc.

There seems to be a couple of common themes:

• "Don't try and write your own encryptions scripts, use an existing library (eg. PHPass)."
• "Don't create MySQL databases for each user, make one big database and write a good PHP script to manage your users, passwords and their data."

Now, is it me, or do these two not seem a bit conflicting?

Let's take this example. I want to create a website where users can create an account. They can add sensitive info (eg. their home address) which they can then view on the website or edit. This sensitive info should not be public and not be available to any other user of the website. It should be "for the user's eyes only".

There is one single admin who should be able to read the sensitive info (for example to send a letter out to every customer) but of course not be able to read passwords. If deemed necessary this could be done locally, ie. without allowing admin access over the internet.

Every precaution could be taken to use the latest versions, best practices etc. The website could run from a dedicated machine which can be physically safe and not shared with any other task.

So, why not create MySQL users for each user of the site? Why roll your own PHP script to create users and then store this info in a table in a database when MySQL already offers this functionality? What are the actual reasons? Are we thinking that using PHPass (or an alternative) provides "safer" password storage than that built into MySQL?

Is storage inside a MySQL database considered "unsafe". If you had local access to my machine but no admin or root passwords or other user/pass combos for my MySQL database then you're still going to be able to get all the data anyway?

If creating a MySQL user for each user of the website is considered "acceptable", then why not create a new database or table for each user and set the permissions in MySQL so each user can access only their data and nothing else? Of course the admin with local access and root password can read all the info.

So it seems that by design the functionality of creating users and assigning permissions is already built into MySQL, why write a PHP script to do the same thing?

///

A follow up question.

If this is to work then there would need to be a MySQL user for the PHP script to use to create new users. This would have it's user/pass stored in plaintext, there's no way round this?

Now, ideally this MySQL user would not be able to read/write or do anything to any existing database, but would be able to create a new user, create a new database/table and assign permissions so this new user.

Is this possible?

Answer 1

MySQL users are for the users of the MySQL server itself. These users should be reserved for use only by the server administrator or applications that require a user to run (give a separate user to each application). The MySQL user management system was built specifically to accommodate controlled access to the database running on the server, NOT to be the basis of user authentication in a web application. Additionally, any database additions (and user creations) will require you to have a user running the application that has those permissions on the database. While itself is not a direct vulnerability, if one is found in your PHP system, it could make your life a whole lot worse.

You never want your application to be able to pollute your MySQL database namespace with additional databases or (tables for that matter). During the operation of your application, it should only be able to create, retrieve, update, and delete records using the Principle of least privilege, meaning you will be giving your database user access to do only the things it requires and nothing more.

As far as password hashing goes, use bcrypt via PHP's crypt() function. Store that in the database within a users table.

Answer 2

The problem is that MySQL access control is not fine-grained enough: it only operates at the database and table level. If you give a user UPDATE permission to a table, they can update any row in that table, including rows that contain information about other users of the application.

You could potentially give every user their own database, but that makes writing general purpose applications very difficult. If the administrator of the site wants to search users, they have to write a query that searches thousands of tables. Writing joins in such an environment will be totally unworkable.

Or consider applications that are used to communicate between users. If user A wants to send a message to user B, they'll need to write something into a table that user B can read. But if each user only has access to their own tables, there's no such table. What are you going to do, create a database for each pair of users? And then what about multi-way communications, like a forum system?