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

  • Having an issue with SLIM, not sure why my resources from sql server arent loading

    Hey guys trying to get SLIM working with angularjs, ive been following this tutorial but I have to put my app in a bunch of directories in a university server and I dont understand how to route that properly, I have a feeling that its to do with the start of my slim.

    Here is the part I think might be the issue

  • MySql Convert to UTC

    I am trying to convert CET date to UTC Format in MySql. Whats wrong with this?

    SELECT 
CONVERT_TZ('2018-01-26T06:15:00+01:00', @@session.time_zone, '+00:00');

    Expected: 2018-01-26T05:15:00Z

    Actual: 2018-01-26T06:15:00Z

    I guess it is not taking the +01:00 component

  • localhost/phpmyadmin - mysqli_real_connect(): (HY000/2054)

    I configured phpmyadmin on my machine and all went well. Until I attempted to log in via localhost/phpmyadmin, I got these errors:

    Cannot log in to the MySQL server

    mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]

    mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client

    Note: I used the same credentials in the config.inc.php file and those same credentials work perfectly when I connect to mysql from the terminal.

    What could be the issue?

    OS: MacOSX High Sierra 10.13.3/ PHP: 7.1.7/ MySQL: 8.0.11

  • AdjustTokenPrivileges: ERROR_NOT_ALL_ASSIGNED after success

    my code have a loop that use AdjustTokenPrivileges to enable SE_DEBUG_NAME privilege and remove it. Every time the I run it, the first set of enable/disable is successful, but after the first loop, the rest sets of enable/remove's give ERROR_NOT_ALL_ASSIGNED's for AdjustTokenPrivileges. Machine is Windows 10 pro 64bit. The compiled exe run in "run as administrator" mode. Please Help.

    The following code is in a loop.

    TOKEN_PRIVILEGES priv = { 0,0,0,0 };
HANDLE hToken = NULL;
LUID luid = { 0,0 };
BOOL Status = true;
if (!OpenProcessToken(GetCurrentProcess(), TOKEN_ADJUST_PRIVILEGES, &hToken)) {
    Status = false;
    goto EXIT;
}
if (!LookupPrivilegeValueW(0, lpszPrivilege, &luid)) {
    Status = false;
    goto EXIT;
}
priv.PrivilegeCount = 1;
priv.Privileges[0].Luid = luid;
priv.Privileges[0].Attributes = bEnablePrivilege ? SE_PRIVILEGE_ENABLED : SE_PRIVILEGE_REMOVED;
if (!AdjustTokenPrivileges(hToken, false, &priv, 0, 0, 0)) {
    Status = false;
    goto EXIT;
}
std::cout << "priv:" << GetLastError() << std::endl; //gives 0(no error) on first pair of enable/disable, then 0x514, which is ERROR_NOT_ALL_ASSIGNED, on the following loops.
EXIT:
if (hToken)
    CloseHandle(hToken);
return Status;
  • How to replicate schema with the same privileges of an existing account in oracle?

    In Oracle for IT clients tickets Operation/Requests, we usually get such request to replicate user account with the same privileges which other users have. Or you can say grant my model IDs after this or that user account. so here is simple pseudo code to achieve that.

  • Postgresql Revoke All Permissions All Users and Roles

    I have several tables in Postgres 9.2.23 that I need to update privileges on regularly (daily or multiple times per day). I have a master list maintained elsewhere of who can select that I use to grant privileges. However, I want to make sure that anyone who used to be on the list but has dropped off has all their privileges revoked.

    My thought was to do a clean sweep with something like:

    revoke all privileges on my_table from [all users/roles]

    before I grant select to the authorized users. However, I have not been able to figure out how to do a bulk revoke on all users/roles like that.

    I know I can generate a list of users/roles that currently have privileges and go from there, but I am hoping there is a shortcut to bypass this.

    Thank you.

  • T-SQL read-only database user for Toad

    I am looking for a T-SQL script to create a database user readonly for toad.

    example:

    USE [master]
GO

CREATE LOGIN [user_readonly]
    WITH PASSWORD = N'blabla';

GRANT CONNECT ANY DATABASE TO [user_readonly];
GRANT SELECT ALL USER SECURABLES TO [user_readonly];
GO

    Question is there a beter solution to create a read-only db-user for Toad?

  • How to extend grants in MariaDB

    I want to execute command

    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

    But I have error:

    ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: NO)

    When I check my grants I have USAGE, nothing more.

    MariaDB [(none)]> SHOW GRANTS;
+----------------------------------+
| Grants for root@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO 'root'@'%' |
+----------------------------------+

    Command 

    CREATE DATABASE test;

    Does not work too.

  • How can I restrict CREATE TABLE access for a user (who has ddladmin membership) to a specific schema (sql server)?

    i have one user who have ddladmin membership i want to restrict him on creating table on dbo schema can i do that ? or i have to go for user define role