7

The thought occurred to me recently, but I have a suspicion the idea might be insane. A couple of benefits I'm perceiving are:

  • facilitate automated logging via triggers
  • control mysql connections on a per-(web-application-)user level

Obviously this would yield me a ridiculously long table of users, but any penalties of that aren't clear to me yet: the User table is properly indexed for O(Log(n)) single-user lookups.

4 Answers 4

7

What type of logging would you be looking at gaining? What I do in my application is an audit table that tracks application user, application command, and result (among other things). This, along with another audit table that tracks individual table changes, provides all the logging we need per user.

You could also set up a connection pool in your application to control connections per application.

At first blush, I think trying to manage so many users is a burden that outweighs any possible benefits.

2
  • I think our answers almost contradict each other regarding user connection limitating. I opt for 'reinventing the wheel' to avoid the extra overhead of maintaining all those users. Perhaps in a few years I'll change my mind! Jun 16, 2011 at 16:14
  • @DerekDowney: How to set up a connection pool in your application?
    – ursitesion
    Oct 5, 2014 at 14:18
6

facilitate automated logging via triggers

You would definitely have a penalty to pay because you would introduce additional disk I/O using triggers to record every login. You could pull off some elaborate infrastructure nuances to alleviate most of that disk I/O as follows :

  • Logging users via triggers to BLACKHOLE tables
  • Set up replication slave to only accept SQL from the master's BLACKHOLE tables
  • Convert the BLACKHOLE table on the slave to MyISAM
  • Reading login statstics from the slave only
  • You have to live with additional disk I/O via binary logging

However, the introduction of triggers slows down app performance just to authenticate users, particularly if the number of user login is high. Rhetorical questions for your research:

  • Is the infrastructure suggestion and trigger coding able to handle a peak of user logins ?
  • Is the implementation of these ideas worth it if peaks are never seen ?
  • Is the implementation of these ideas worth it if you anticipate peaks in the future ?

control mysql connections on a per-(web-application-)user level

There is already a built-in feature of controlling mysql authentication in terms of the number of queries that can be done per hour. Please note them in the description of mysql.user in MySQL 5.5.12:

mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
| plugin                 | char(64)                          | YES  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.01 sec)

You have max_questions, max_updates, max_connections, and max_user_connections.

In MySQL 5.0, you can limit use of the following server resources for individual accounts:

  • max_questions : Number of queries that an account can issue per hour
  • max_updates - Number of updates that an account can issue per hour
  • max_connections : Number of times an account can connect to the server per hour
  • max_user_connections : Number of simultaneous connections to the server by an account

These can be managed for your app users by creating a corresponding mysql user for each app user. If you are planning to manage user limits, please remember not to reinvent the wheel since MySQL can control these aspects in its authentication protocol.

1
  • +1 for the disk io/logging issues! As for not re-inventing the wheel, I think the OP is aware of the ability to limit based on mysql.user, since that was one of the benefits of creating a mysql user per application user. Jun 16, 2011 at 15:55
1

I have one question:

Do your users need a password to get into your application?

If so, then you absolutely should not create a database user for each application user.

Q: How often do people forget their passwords?
A: Often.

When they do forget their password, how do they reset them?
Well, they probably go into your application, somewhere, and press the "reset password" button.

But that operation can't go anywhere near the database because they can't log into the database, because they've forgotten their password into the database!

Of course, you could build this functionality to use some other way into the database, but that would have to use elevated permissions because it now has to be able to reset another user's database password.
Not ideal.

Alternatively ...

If the application uses a dedicated, application account for everything and application Users are managed just as application data, not as database users, then life suddenly gets a whole lot easier. You only ever need the application account to connect to the database and no User ever gets to know what that is. They log in using their own "password", but that's only checked against the salted-and-hashed password stored your [application] users table.

Also, your workload for database Permissions Management just [largely] disappeared.
You don't need to maintain thousands of database users, assigning and revoking the Groups and/or Roles that you use to give them database-level permissions.
Instead, your application has to work out what each user can and cannot do and restrict them accordingly.

0

Another concern might be the overall security of such a model. When users are handled via the webapp layer, they only have access to the database by way of whatever methods have been written to allow them to read, update, write, etc the specific tables those methods define. Creating an actual mysql user for that user means that mysql user would have to have (at least) read access to the database in general, which makes me a little nervous, especially given how big that list could grow. It's a little easier via the web app to handle alerting/blocking on failed logins (to mitigate brute-forcing, etc), and easier to delegate which users can access what data in what capacity. You could mitigate brute force attempts in other ways, but they tend to be much higher maintenance (AFP, etc) and less practical in the case of honest mistakes. Keylogger malware and bruteforcing at two thoughts at the top of my head that make this idea less comfortable for me (in addition to some of the other suggestions here.)

3
  • I think what you're describing may be a significant departure from I had in mind. It certainly would be insane to give them direct access to their MySQL account, or to grant the user's MySQL account any privileges in addition to what a general application MySQL account might have. To an overwhelming extent, the user would still be regulated via the application.
    – rsimoes
    Jun 19, 2011 at 23:30
  • I didn't mean to imply that the user would have more privs than the app - just that an app user having access to the db directly (not that they would ever access it that way directly) makes my skin crawl. Simply the fact that their user exists as one that has read (plus whatever else) is the issue I have, not that they would be directly interfacing with the db intentionally.
    – snipe
    Jun 19, 2011 at 23:41
  • Again, I'm not worried about what legitimate users will do within the confines of your app. My concern is what happens when their user/pass is compromised, sent to a botnet, etc.
    – snipe
    Jun 19, 2011 at 23:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.