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.