How to grant privileges for specific table for user in mysql?
I am creating databdase for project and i would like to grant different privileges on different tables.
stayfit=database name
my query
CREATE USER 'kasprian'@'localhost';
SET PASSWORD FOR 'kasprian'@'localhost'= PASSWORD('1234');
GRANT SELECT,INSERT ON stayfit.Calories_and_nutrients_balance TO 'kasprian'@'localhost';
GRANT SELECT,INSERT,UPDATE ON stayfit.User_size TO 'kasprian'@'localhost';
It doesn't do anything. Only 0 rows(s affected ) Can i even do this? Because only granting privileges to all tables is working for me. For example:
GRANT SELECT,INSERT,UPDATE ON stayfit.* TO 'kasprian'@'localhost';
1 answer
-
answered 2018-01-11 20:53
wchiquito
Try:
$ mysql -u root -p
mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 5.7.20 | +-----------+ 1 row in set (0.00 sec) mysql> CREATE DATABASE IF NOT EXISTS `stayfit`; Query OK, 1 row affected (0.01 sec) mysql> USE `stayfit`; Database changed mysql> CREATE TABLE IF NOT EXISTS `Calories_and_nutrients_balance`( -> `id` SERIAL -> ); Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE IF NOT EXISTS `User_size`( -> `id` SERIAL -> ); Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER 'kasprian'@'localhost'; Query OK, 0 rows affected (0.01 sec) mysql> SET PASSWORD FOR 'kasprian'@'localhost'= PASSWORD('1234'); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SHOW GRANTS FOR 'kasprian'@'localhost'; +----------------------------------------------+ | Grants for kasprian@localhost | +----------------------------------------------+ | GRANT USAGE ON *.* TO 'kasprian'@'localhost' | +----------------------------------------------+ 1 row in set (0.00 sec) mysql> GRANT SELECT,INSERT ON `stayfit`.`Calories_and_nutrients_balance` TO 'kasprian'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR 'kasprian'@'localhost'; +----------------------------------------------------------------------------------------------+ | Grants for kasprian@localhost | +----------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'kasprian'@'localhost' | | GRANT SELECT, INSERT ON `stayfit`.`Calories_and_nutrients_balance` TO 'kasprian'@'localhost' | +----------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> GRANT SELECT,INSERT,UPDATE ON `stayfit`.`User_size` TO 'kasprian'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR 'kasprian'@'localhost'; +----------------------------------------------------------------------------------------------+ | Grants for kasprian@localhost | +----------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'kasprian'@'localhost' | | GRANT SELECT, INSERT, UPDATE ON `stayfit`.`User_size` TO 'kasprian'@'localhost' | | GRANT SELECT, INSERT ON `stayfit`.`Calories_and_nutrients_balance` TO 'kasprian'@'localhost' | +----------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> exit Bye
$ mysql -u kasprian -p
mysql> SELECT `id` FROM `stayfit`.`Calories_and_nutrients_balance`; Empty set (0.01 sec) mysql> DELETE FROM `stayfit`.`Calories_and_nutrients_balance`; ERROR 1142 (42000): DELETE command denied to user 'kasprian'@'localhost' for table 'Calories_and_nutrients_balance' mysql> SELECT `id` FROM `stayfit`.`User_size`; Empty set (0.00 sec) mysql> DELETE FROM `stayfit`.`User_size`; ERROR 1142 (42000): DELETE command denied to user 'kasprian'@'localhost' for table 'User_size'
UPDATE
FLUSH PRIVILEGES
isn't necessary in this case, see 6.2.6 When Privilege Changes Take Effect.