The question… Is it possible to add MySQL permissions to only allow to select fields based on permissions?
Example:
user1 can only select/insert/delete from the users table where the column instance is equal to 1 (1 being passed via PHP).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];
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.
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
MySQL does not facilitate using permissions to lock down certain rows based on the MySQL user that is connected to the database.
If you have users that you want to limit in such a way, it is probably best to not give them direct database access, but have them connecting through another layer.
Using a view is not a good idea - every user would have to use different queries (referencing their own personal view) to accomplish the same things. If you were just limiting the columns that a user could see (instead of the rows), a view would be a good solution.
It is possible to use stored procedures to accomplish something like what you're looking for:
# This table already exists in your schema
CREATE TABLE `user` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`email` VARCHAR(50) NOT NULL,
`instance` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB;
INSERT INTO `user`(`id`,`email`,`instance`)
VALUES ( NULL,'wat@lol.com','1');
INSERT INTO `user`(`id`,`email`,`instance`)
VALUES ( NULL,'huh@bwu.com','2');
INSERT INTO `user`(`id`,`email`,`instance`)
VALUES ( NULL,'no@yes.lol','1');
# This would be a new table you would have to create
CREATE TABLE `user_instance` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`user` VARCHAR(20) NOT NULL,
`instance` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_instance` (`user`,`instance`)
) ENGINE=INNODB;
INSERT INTO `user_instance`(`user`,`instance`) VALUES ('user1','1');
INSERT INTO `user_instance`(`user`,`instance`) VALUES ('user2','2');
# This is a stored procedure that might accomplish what you want
DELIMITER $$
CREATE PROCEDURE `p_get_users`()
BEGIN
SELECT `user`.`id`, `user`.`email`
FROM `user`
WHERE `user`.`instance` IN(
SELECT `instance`
FROM `user_instance`
WHERE `user` = USER());
END$$
DELIMITER ;