11

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?

James Drake
  • 143
  • 6
  • You can create a mysql user for every user on your website. But you'll have to connect to mysql, using different credentials all the time. These credentials must be stored somewhere. Probably, in a file. So, you'll have 2 copies of data: in mysql.users table nd your file. Too much fuss IMHO. – user4035 Sep 21 '13 at 17:01
  • But when the user logs into the website they type in the user and pass, which just get used directly by PHP to login to MySQL. So no user/pass needs to get stored anywhere. – James Drake Sep 21 '13 at 17:07
  • Ah, yes, I didn't think about. Just one problem: shared hostings often don't allow to create new users. But in general, this approach can work. – user4035 Sep 21 '13 at 17:11
  • Talking about a dedicated machine running one website. No shared hosting, no shared MySQL databases, no shared nothing. One machine for one application. – James Drake Sep 21 '13 at 17:13
  • A website usually applies to MySQL database, when accesses by an anonymous user: to get menus, content etc. If you implement users as mysql users, you'll have to close the current connection and reconnect, using the user credentials. It is not necessary, when storing users in your own table and creates delays. Plus, if you would like to store extra information about the users, like first name, last name, office etc, you'll have to create a separate table, connected to mysql.users table and then join them, as you can't modify core mysql tables. This will also create difficulties. – user4035 Sep 21 '13 at 17:25
  • You are mixing `Database User` with `Application User`. – Shiplu Mokaddim Sep 21 '13 at 17:29
  • For this example there should be no public info. So if an anonymous user views the site then they see nothing except a login box (which does not need a database request to display). – James Drake Sep 21 '13 at 17:32
  • @shiplu.mokadd.im - but the question is WHY? Why have one MySQL user for the PHP script to make requests (Database User) and then create many users for your website using PHP scripts (Application Users)? – James Drake Sep 21 '13 at 17:36
  • I have just answered it. – Shiplu Mokaddim Sep 21 '13 at 17:43

2 Answers2

3

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.

SamT
  • 10,374
  • 2
  • 31
  • 39
  • I get these points for shared storage. But if one whole MySQL installation is used only for this application, what's the difference between "database/table pollution" (where loads of random new users create loads of databases/tables) and "row pollution" (where loads of random new users create loads of rows)? I really get these points when one MySQL installation is serving different databases for different apps. But to create this single dedicated application it just seems silly to write a whole PHP app to handle users and their data and then stick that all into one table or database. – James Drake Sep 21 '13 at 17:52
  • 1
    Relational databases are optimized specifically to deal with large numbers of rows, not tables and databases. There will be unforeseen performance hits and probably bugs that will arise due to using it in this way. What you're trying to do with MySQL is akin to using a hammer to drive a screw through wood. – SamT Sep 21 '13 at 18:51
1

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?

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Application user and Database user are not same. Applications users are not database user. Application itself is a database user. Thats the main thing – Shiplu Mokaddim Sep 21 '13 at 17:31
  • I would like to give each user their own table or database. – James Drake Sep 21 '13 at 17:34
  • @shiplu.mokadd.im Well, that's the point of his question: why not use database users to implement application users? – Barmar Sep 21 '13 at 17:34
  • There needs to be no user<>user interaction. In fact, as part of the security, I would prefer no interaction at all between users. Interaction should only be between user and admin. – James Drake Sep 21 '13 at 17:42
  • I get the searching thing. Is it really a big deal searching through tables rather than rows? I have to think if this would impact my application. – James Drake Sep 21 '13 at 17:43
  • As I said, it still makes writing general applications difficult. The administrator needs to use the entire database. – Barmar Sep 21 '13 at 17:43
  • Yes, it's a big deal. You can't use indexes to find a table. – Barmar Sep 21 '13 at 17:44
  • Let me just understand that last bit. If I wanted to search for all info by 'user' then I can just bring up the database named 'user' and I have all his info. But if I want to search for all users that have an address in 'city' then this is not possible? Or just really crappy? – James Drake Sep 21 '13 at 17:58