The MySQL security model aloows for table-level and even column-level restriction. That is, it is possible to “hide” some tables from a user, allowing her to access relevant views.
This usually means a complicated privilege system: if you have many tables and many users, it may become difficult to maintain the privileges.

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.

#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

#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

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

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