Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The thought occurred to me recently, but I have a suspicion the idea might be insane. A couple 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 yet clear to me: the User table is properly indexed for O(Log(n)) single-user lookups.

share|improve this question
add comment

3 Answers

up vote 6 down vote accepted

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.

share|improve this answer
    
+1 for your concise assessment. I tend to be more verbose !!! –  RolandoMySQLDBA Jun 16 '11 at 15:59
    
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! –  Derek Downey Jun 16 '11 at 16:14
add comment

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.

share|improve this answer
    
+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. –  Derek Downey Jun 16 '11 at 15:55
add comment

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.)

share|improve this answer
    
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 '11 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 '11 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 '11 at 23:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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