Related questions

How to grant privileges for specific table for user in mysql?

2018-01-11 19:57 Kasprian imported from Stackoverflow

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.

See also questions close to this topic

  • php mysql Query result with %Like%

    I am stuck with the query result and need help plz.

    My sample column How are you what is your name where are you from whats the age of the youth

    Now this is my column in Table and I query the below

      $search='you';
  $s=mysqli_query($con,"Select * from tblxyz where column1 is LIKE '%{$key}%'");

    I am getting the result of all the 4 rows as there is "you" in all, But I would like to get the result of the exact word which SQL searched, so the result from the 4th row would be "youth". How to get those words which SQL found based on my query.

    Thanks in Advance

  • Mysql join Delete query issues

    I have a delete query

         delete from Admin_course_groups join admin_course_details
 on Admin_course_groups.fk_c_id=admin_course_details.fk_c_id 
    where fk_g_id=1 and type='G'

    When i run this query i got an error

    MySQL said: Documentation

    1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

    near 'ac inner join admin_course_details ad on ac.fk_c_id=ad.fk_c_id' at line 1

    My tables Admin_course_groups

    a_id | fk_c_id|fk_g_id|type
1           1   1       G
2          3     1      G

    admin_course_details 

    d_id | status | fk_c_id |fk_user_id
 1     1         1         2
 2      1        3         2

    Any help would be Appreciated.

  • How to initialize H2 or MySQL Database

    I have configured H2 and MySQL for datasources, but I am unclear on how to automate the initialization of my database (especially for H2 in testing). Spring Boot has a "data.sql" file, is there something similar for Micronaut?

  • How to find dependent privileges?

    When I try to revoke privileges, I get an error about dependent privileges. It says I can use CASCADE, but I'd like to see what the dependent privileges are first. Is there a way to see all dependent privileges in this scenario?

    psql> revoke all privileges on schema public from "foo";                                                      
ERROR:  dependent privileges exist
HINT:  Use CASCADE to revoke them too.
  • CreateFile in read mode needs elevated privileges

    I am using the same code which I believe has been used before but now in windows 10 it is asking for elevated privileges The code is:

    HANDLE fh; fh=CreateFile("e:\temp\event.dat", GENERIC_READ, FILE_SHARE_READ, NULL, OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, NULL);

    e: is a physical drive. Any idea why this is complaining?

  • Raspberry pi protection against reverse engineering the codes

    Problem I want to secure my Raspberry pi in a special way. I would like to start up raspberry pi without entering a password as pi user. However, I want pi user to have zero privilege. Cant read a file, cant copy a file, simply nothing. And without access to root->'sudo su'. On the other hand when raspberry pi starts itself with pi user, I want backend process to be running as root. So to put it simply, I want it like in a zoo - two worlds but neither of them can enter the other. Clients can be present, see what process are running, see files in directories, but cant read it, copy, remove and etc. In the same time I want the backend to be untouched and running and writing files.

    Reason:

    I have raspberri pi product - customer get it home, when plug in power supply,RPi starts up and runs backend programs with root privilege and communicates with my desktop software.

    But I dont want curious customer that plugs in HDMI and see my code. I also dont want him to take the SD card and extract the code.

    I heard its possible to reverse engineer the code even if compiled. So I simply want the programs(python script) to be there but cannot be accessed in any way.

    Is it possible to make such protection?

    Thanks in advance

  • creating DB and grant syntax error in mysql

    enter image description hereI'm beginner python code, and not good at english.

    my laptop window 10 pro, python version 3.6 mysql commmunity server version 8.0.12

    problem

    headfirst python

    this is the code

    mysql -u root -p
mysql>create database vsearchlogDB;
mysql>grant all on vsearchlogDB.* to 'vsearch' identified by 'vsearchpasswd';

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'vsearchpasswd'' at line 1

  • Why is dbo the only grantor in Microsoft SQL Server?

    I can't understand why is dbo the only grantor in SQL Server? Or am I wrong?

    I mean it as follow: No matter as which user I am currently loged in in SQL Server Management Studio, when I go to properties of a database under Securables and grant some permissions to a user and save it and come back to Securables: dbo is shown as Grantor for those permissions, which I granted before and not the current loged in user.

    Why is it so?

    Under which scenario could I see another user than dbo as grantor?

    Thanks

  • Grant Select on Table

    I am trying to set up User_2 in Oracle, and have that user be granted SELECT on a table by User_1. No matter what I do, create a role and grant select to the role, or grant the User_2 the SELECT directly, all I get is 'Table or View does not exist'. However, if I qualify the table name with the DB Owner, I get my select.

    Example, User_1.Items is granted SELECT to User_2. If I then query SELECT * FROM ITEMS, I get the error.

    However, if I try SELECT * FROM USER_1.ITEMS, I get it fine. Is that the way it has to be? I thought the User_1 prefix was implied by the grant.

    Thanks!