Allow users only certain information from database

问题:

I have a database of information for 100 or so users. I have 2 MySQL users that has permission to the database. root and also a specific database user "directory". User "directory" only has permission to run SELECT statements, nothing else.

Some of the information is sensitive to that user and I am using PHP as a proxy to detect which user they are before any SQL commands are ran, how can I append every SELECT statement to include WHERE id = '$their_id' where $their_id is a php variable with their id.

Please note the users will have ability to write their PHP mysql commands, I am defining these restrictions in a db.php that will include the connection information.

I want to prevent say the user joe being able to run a command like SELECT * WHERE id = 'bob' when joe isn't bob.

---

Edit:

I currently do not scan-through any of the MySQL commands, I simply just create the connection for them in db.php. Is there anyway I can make my own proxy MySQL commands and force them to use them, that way they have to use my command mysql_query and not the default mysql_query to override any processing I do.

回答1:

Rather than modify their query just always have them reference the data through a view. They have no rights at all to the real table the view is created over. You create that view with

Create View tablenametheyuse as select * from realtable with id = 'user'

and only give that user authority to his view.

They now code their select statements any way they want over the view.

回答2:

That doesn't seem very secure but I would just check every entry to see if the word 'id' is in it. Then check to see what it equals, if it isn't their user id then don't execute the command.