1

Im trying to grant a user all privileges, as well as grant them with the privilege to grant other users SELECT only access.

This is my statement show here :

GRANT ALL ON [database] TO 'user1' WITH GRANT OPTION

However this allows user1 to grant others the same privileges as their own. I want to only allow user1 to grant others SELECT privileges only. Is what Im asking even possible?

Thanks,

SAVe
  • 814
  • 6
  • 22
Tom
  • 81
  • 1
  • 6

1 Answers1

2

First, check this topic.

Second, if you grant access as "ALL PRIVILEGES", then the user automatically will have option to grant READONLY access to other users.

Here is a list of the MySQL privileges which are most commonly used:

        ALL PRIVILEGES – grants all privileges to the MySQL user
        CREATE – allows the user to create databases and tables
        DROP - allows the user to drop databases and tables
        DELETE - allows the user to delete rows from specific MySQL table
        INSERT - allows the user to insert rows into specific MySQL table
        SELECT – allows the user to read the database
        UPDATE - allows the user to update table rows

Here is a sample syntax where only two privileges are granted for the user:

GRANT SELECT, INSERT, DELETE ON database.* TO 'user'@'localhost';
PirrenCode
  • 444
  • 4
  • 14
  • I realise that they can grant readonly access to other users, but I don't want them to be able to grant writing access to other users as well. Im imagining something like this.. GRANT ALL ON [database] TO 'user1' WITH GRANT OPTION(SELECT) I realise that's not correct but for the sake of getting my point across. – Tom May 16 '19 at 09:46