DB Privilege Management

[Daniel Carter]

I have a database in which I expect to eventually have millions of users. I have created views that allow the user that is currently logged in to see only his/her data from a master table I've created. I would like each user to only have rights to select/update this view but I would rather not have millions of grants (ie. millions of rows in the mysql.tables_priv table) as I believe this effects performance, particularly when starting up the database or refreshing the rights - let me know if I am wrong here. So basically I wanted to just give everyone select & update rights on the view, but I tried for hours and couldn't figure out how to do it. I read that by leaving the username blank in a grant statement the privilege(s) would apply to everyone, but this doesn't seem to work with table specific rights.

Here is an example of what I am doing and what I tried:

My table:
create table test_db.users (fname varchar(20), mi varchar(1), lname varchar(20), username varchar(25));

My view:
create view users_view as select fname, mi, lname from test_db.users where username = user();

Trying to grant access to view to all users (DOESN'T WORK):
grant select, update on test_db.users_view to ''@localhost;

Trying to grant access to entire db to all users (WORKS BUT DEFINITELY NOT WHAT I WANT):
grant select, update on test_db.* to ''@localhost;


To work around this, I thought I'd create another database and put my view in there then grant all users rights to that db:

create database public_test;

create view public_test.users_view as select fname, mi, lname from test_db.users where username = user();

grant select, update on public_test.* to ''@localhost;


My workaround does work, but I am not sure if this will affect performance or if there is an easier way. Please let me know if you can answer either of those questions. Also, is the view I have created a good way of keeping users from seeing other users' data?

As a side note, I saw a post on here in which a user said it is very unsafe to let users have direct access to the database. I just want to mention that I do not plan on doing this, I am just taking precautions in case a hacker does find his/her way into it. Let me know if there's some other preventative measure I should be taking here instead.


Thanks,
Dan