2

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

The link above explains the dilemma that I am in very well.

I have an application where users register to it by providing username and password. There is additional information which can be stored on a separate table (one for all users) that relate back to the user name. In addition, I want to restrict the users to only one table where events specific to that user are stored using GRANT. To use grant, a MySQL user account is required. The question then becomes, is there anything wrong with a MySQL user to be also the application user?

I've read a few posts that keep advising to implement my own authentication as well as access system, why re-invent the wheel? I haven't encountered anything on the MySQL page advising against the link above.

EDIT

I created a user with the following statement

CREATE USER 'test'@'localhost' IDENTIFIED BY 'testing';

Followed by the grant privileges

GRANT CREATE USER ON *.* to 'test'@'localhost';

I then log-on as this user and do the following commands:

    mysql> show grants;
+-----------------------------------------------------------------------------------------+
| Grants for tester@localhost                                                             |
+-----------------------------------------------------------------------------------------+
| GRANT CREATE USER ON *.* TO 'tester'@'localhost' IDENTIFIED BY PASSWORD '*AC57754462B6D4C373263062D60EDC6E452E574D' |
+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

mysql> use mysql
ERROR 1044 (42000): Access denied for user 'tester'@'localhost' to database 'mysql'

I believe the only issue here would be the DROP USER command. Is there any way to prevent that?

mysql> DROP USER 'root'@'localhost'; ERROR 1396 (HY000): Operation DROP USER failed for 'root'@'localhost'

Community
  • 1
  • 1
Clocker
  • 1,316
  • 2
  • 19
  • 29

1 Answers1

4

In part, your application shouldn't be managing MySQL users.

This is because someone who can manipulate your application can manipulate it to gain access to the full db or render the entire db unusable.

The application should authenticate with the db as the application, and the application's account for the db (i.e. the MySQL user) should be granted only permissions needed for that application.

In general the ability to create users, destroy users or grant permissions (any or all of those) are inadvisable for an application to have, as your model is wide open to escalation-type attacks.

I'd reconsider your design, and use for e.g. a column to separate user data or a meta structure like key-value pairs.

Taylor
  • 3,942
  • 2
  • 20
  • 33
  • Is it not possible to create a MySQL.user that the application can use to only create, read and delete other MySQL.users and grant privileges as well? – Clocker Jun 03 '14 at 23:08
  • 1
    Sure, but it's dangerous for your application to have that level of control on the db, if your application is exploited, the attacker can create additional users with escalated privilieges and do whatever they want. – Taylor Jun 04 '14 at 19:20
  • Agreed, I decided to against this approach. The issue arose when the application had to grant the necessary privileges to the newly created users. GRANT automatically grants whatever permissions the granting user has onto the granted users. Therefore, all users would have access on the entire database with CRUD and CREATE/DROP USER. Not good. – Clocker Jun 04 '14 at 23:44
  • Fantastic answer! The first one I find that not only repeats the mantra "db user != app user", but actually justifies it with a convincing argument. – Paulo Apr 05 '19 at 04:39