CODEWITHSUNDEEP
javamysqlsecurityauthentication
Clocker
Clocker

Reputation: 1336

MySQL.users table as application authentication

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'

Upvotes: 2

Views: 335

Answers (1)

Taylor
Taylor

Reputation: 4087

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.

Upvotes: 4

Related Questions