Web applications face constant exploitation attempts. Those with a user base must keep their users’ private data, well… private.
While the MySQL security model allows restricting users access to databases, tables and even columns, it has no built in feature for restricting the rows access within the given table.
One cannot allow a user to only update rows 0 through 99, but restrict that user from updating rows 100 to 199. Such restrictions are usually managed in the application level, by adding a necessary “… AND filtering_column = some_value…”
Many web application have the notion of an ‘admin’ account, or several such accounts, which provide greater control over the application. The ‘admin’ account is one account to which many attacks are targeted. One such attack is an attempt to modify the admin’s password, such that the attacker can later log in with and access restricted data.
Assume the following table:
CREATE TABLE my_users ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, username VARCHAR(32) CHARSET ascii NOT NULL, password VARCHAR(32) CHARSET ascii NOT NULL COLLATE ascii_bin, UNIQUE KEY(username) );
Let us also assume we are somewhat careful, so that the passwords are not plaintext, but rather encoded with MD5.
INSERT INTO my_users (username, password) VALUES
('admin', MD5('qwerty')) ; -- Safe password as can be found!
INSERT INTO my_users (username, password) VALUES
('alice', MD5('123456')) ; -- Safer yet!
SELECT * FROM my_users;
+----+----------+----------------------------------+
| ID | username | password |
+----+----------+----------------------------------+
| 1 | admin | d8578edf8458ce06fbc5bb76a58c5ca4 |
| 2 | alice | e10adc3949ba59abbe56e057f20f883e |
+----+----------+----------------------------------+
2 rows in set (0.00 sec)
An attacker will try to set the password for the admin account using security holes in the web application. The web application may execute the following query:
UPDATE my_users SET password=MD5('att@cker!') WHERE username='admin';
The issued query is valid, and should generally be allowed. However, we may decide to block changes to the specific ‘admin’ row, in the following manner:
DELIMITER $$ DROP TRIGGER IF EXISTS my_users_bu $$ CREATE TRIGGER my_users_bu BEFORE UPDATE ON my_users FOR EACH ROW BEGIN IF (NEW.username='admin') THEN SELECT 0 INTO @admin_error FROM `Cannot modify admin data!`; END IF; END $$ DELIMITER ;
Let’s try running again the query:
UPDATE my_users SET password=MD5('att@cker!') WHERE username='admin';
ERROR 1146 (42S02): Table 'world.Cannot modify admin data!' doesn't exist
The query fails, since the BEFORE UPDATE trigger fails.
We can tweak the trigger to only allow specific users to modify the row:
DELIMITER $$ DROP TRIGGER IF EXISTS my_users_bu $$ CREATE TRIGGER my_users_bu BEFORE UPDATE ON my_users FOR EACH ROW BEGIN IF (NEW.username='admin' AND USER() != 'root@localhost') THEN SELECT 0 INTO @admin_error FROM `Cannot modify admin data!`; END IF; END $$ DELIMITER ;
This way it is possible for the root user to modify the password at will. We can further tweak the trigger to INSERT INTO some log table. The information we may wish to register is USER(), the CURRENT_TIMESTAMP(), old password and new password, and perhaps the CONNECTION_ID(). More data means more means to locate the security breach, and monitoring the log table allows for immediate response for such an attempt.
Hi!
Nice post.
Two things though …
#1
There is an error in your code. The USER() function is probably *not* what you want in this case – you need the CURRENT_USER() function.
The USER() function returns the credentials provided by the client, and this may not be identical to the credentials that were used to authenticate the user. Crazy perhaps, but that is how it is…Of course the risk of USER is not that big if you assume the attacker did not gain a direct connection to your server, but CURRENT_USER will actually also shield you better from attacks on the inside.
#2
The trigger solution is nice, but unfortunately, you (and me, and legions before is) have to bite it and accept the absence of a proper mechanism to raise a user-defined error. It may be paranoid, but what if somebody creates the table called `Cannot modify admin data!`?
Anyway, my point is that you could have solved it with a VIEW too, and depending on taste this could be a more clean solution. For example:
CREATE OR REPLACE
SQL SECURITY DEFINER
VIEW v_my_users AS
SELECT * FROM my_users
WHERE username != ‘admin’
OR CURRENT_USER() = ‘root@localhost’
WITH CHECK OPTION
this will not result in an error when the UPDATE is executed, so you must use the ROW_COUNT() function to check if the UPDATE succeeded or failed.
kind regards,
Roland
Hi Roland!
1. No, I’m afraid I was right. Indeed, my first attempt was with CURRENT_USER(). Guess what? It always gave out the trigger’s *definer*! I was surprised as well. Changing to USER() did in fact return the trigger’s invoker.
This is actually a real solution implemented on production due to a hacking problem which constantly hacked the admin account. And the logs did actually help in finding out the source of error.
2. The lack of error raising is a given fact in MySQL’s stored programs. Until this is resolved, we must settle with some ugly patch.
I’m pretty much sure some character should never be allowed in table names, even though they may actually be supported: take “.”, for example. It’s such an elementary delimiter (table_schema.table_name.column_name) that it would be a very bad practice, in my opinion, to include it in table names using back ticks.
So what I’m saying is paranoia must have its limits, though mathematically speaking it may be substantiated.
With regard to using a view such as the one you’ve recommended: my personal inclination is not to use updatable views (and I agree it’s a matter of taste). In this particular case I find the following issues:
– There must be an index-merge for this view to work well (because of the OR). I’ve yet to rely on the success of inde-merge. Insofar, I have found it to be sporadic, not much reliable.
– The particular implementation I’ve encountered has used Hibernate (the popular java persistence framework). I’m pretty much certain it would be very difficult, if not impossible (though I never tried), to let Hibernate talk with this view when doing some operations, but use the actual table when proper SELECT … JOIN queries are required.
– The trigger solution avoids changing the application code. I’m not generally in favor of that, I usually prefer to keep program logic in the program, but in this case the module which handled the users registration was 3rd party, and rewriting it was more troublesome.
Regards,
Shlomi
Hi!
#1
“my first attempt was with CURRENT_USER(). Guess what? It always gave out the trigger’s *definer*! ”
Ok – I stand corrected – you are right, the CURRENT_USER() function inside a trigger refers to the definer… (http://dev.mysql.com/doc/refman/5.1/en/account-activity-auditing.html)
I must say – I am a bit shocked that it is not really possible to access the authenticated user in this particular case. It seems to me the USER() is next to worthless, and you must take special precautions if you want to use it to find out who is really connected. At the very least, you should check if the data returned by USER() matches a real account. in the mysql.user table.
#2:
All good points. My main point is that enforcing a constraint like this without resorting to a hack is possible. Apparently it would not have been the ideal choice in your particular case.
Regards,
Roland
Hi Roland,
#2.
I agree – the VIEW is a solution which does not resolve to a hack. It would be interesting to see how well it performs in comparison with triggers.
Regards,
Shlomi
Surely the other issue with using a VIEW in this case would be that a potential attacker is less likely to be considerate and attempt to modify through the VIEW (even if this is shown in the application, rather than direct access to the table), since if they’ve got access to be able to issue such a query, they almost certainly have sufficient access to show tables. By using a trigger, the attempt will still be caught however the attacker tries to change the table in question.
Unless, of course, you can enforce a VIEW as being the only way to access a particular table – I’ve not come across the idea, but my general use of views is for reading data.