MySQL conditional user permissions

Question

The question… Is it possible to add MySQL permissions to only allow to select fields based on permissions?

Example:

1. User user1 can only select/insert/delete from the users table where the column instance is equal to 1 (1 being passed via PHP).
2. User user2 can only select/insert/delete from the users table where the column instance is equal to 2 (1 being passed via PHP).

Here's the background info:

I'm creating an application with the same code base being used for multiple sites. Conditions within the app load different layouts. The multiple sites are using the same database because most information can be shared between sites. A user that registers on one site must also register on another site (this is how we want it because the sites are "by invitation only")

What I'm thinking of doing is to have users table: id, email, password, instance. The instance column would have the id of the site.

On the application layer every time I need to select/insert/delete from this table I append instance = [site_id] to the query... example: SELECT * FROM users WHERE email = '' AND instance = [site_id];

Answer 1

no, the permission is per table,db,server,etc but not for rows, however there is a solution, you can use view tables and set permission to user, for example

mysql> CREATE VIEW test.v AS SELECT * FROM t where email = '' AND instance = [site_id];

just create 2 view tables and grant access to those users

here is the Mysql documentation

Answer 2

It is not possible from what I know, MySQL doesn't allow conditional users.

Use one user for both sites and modify your all queries accordingly to your 'instance'. So every time you query something site-specific you add WHERE instance = $site_id.