The thought occurred to me recently, but I have a suspicion the idea might be insane. A couple of benefits I'm perceiving are:
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.
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.
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 :
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:
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:
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.
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.
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.)
