How to Modify User Privileges in MySQL Databases

The privileges granted to a MySQL user determine what operations that user can perform. MySQL privileges are organized accordingly:

  • Administrative privileges allow users to manage the operations of the MySQL server itself, including the privileges of other users. Also known as global privileges.

  • Database privileges allow users to manage a specific database and all the objects within that database. These can be granted globally or just for specific databases.

  • Database object privileges allow users to manage specific objects within databases. These privileges can be granted for specific objects within a database, for an entire database, or globally.

Privilege Restrictions on DigitalOcean’s MySQL Managed Databases

By default, MySQL database clusters come with a user, doadmin, which has full access to every database you create. Instead of using doadmin to access the database, we recommend creating additional users who have only the privileges they need, following the principle of least privilege.

MySQL sets privileges based on account names, which consist of a user name and a host name in the format 'user_name'@'host_name'. You can specify the host by name ('user_name'@'localhost'), IP address ('user_name'@'198.51.100.1'), or using wildcard characters (like %, as in 'user_name'@'%', which matches all hosts). Learn more in MySQL’s documentation on specifying account names.

To ensure stability of the platform, DigitalOcean Managed MySQL Databases have some default restrictions on user privileges that cannot be changed. Users cannot insert or edit any of the following databases, but can select from them:

  • mysql
  • sys
  • metrics_user_telegraf
  • performance_schema

Users on DigitalOcean Managed MySQL Databases cannot insert, edit, or select from the information_schema database.

You can’t currently change a user’s privileges in the control panel, so to do so you need to use a command-line MySQL client like mysql. After you create a user in the cluster, connect to the cluster as doadmin or another admin user. From here, the commands you need to execute depend on the permissions you want the user to have.

Granting Privileges

To grant all privileges on a specific database to a user, you can use the following commands:

  
    
GRANT ALL ON example_database TO 'example_user'@'%';

  
Note
You cannot create additional admin users. However, you can give a user full access to all databases you’ve created by running the GRANT ALL command for each database.

To grant a user administrative privileges for a specific database, you must also give them the GRANT OPTION privilege. Here’s an example:

  
    
GRANT ALL ON example_database TO 'example_user'@'%' WITH GRANT OPTION;

  

To grant a user only read privileges on a database, you can use the following command:

  
    
GRANT SELECT ON example_database TO 'example_user'@'%';

  

To grant a user privileges on only a specific table in a database, you can use the following command:

  
    
GRANT SELECT ON example_database TO 'example_user'@'%';
mysql> GRANT INSERT ON example_database.example_table TO 'example_user'@'%';

  

Granting additional privileges to a user does not remove any existing privileges. To remove existing privileges, use the REVOKE command.

Revoking Privileges

Revoking privileges is quite similar to granting them. To revoke a user’s privileges, use the revoke command syntax. For example, to revoke all privileges on a specific database, use the following command:

  
    
REVOKE ALL ON example_database FROM 'example_user'@'%';

  

If a user already has privileges and you want to revoke them but still allow the user to read the database, you can use the following command:

  
    
REVOKE ALL ON example_database FROM 'example_user'@'%';
mysql> GRANT SELECT ON example_database TO 'example_user'@'%';

  

Viewing Privileges

To see the privileges for the current user, you can use the SHOW GRANTS; command, and will see output similar to the following, which shows the default grants for the doadmin user:

  
    
SHOW GRANTS;
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for doadmin@%                                                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT CREATE, DROP, PROCESS, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER, CREATE ROLE, DROP ROLE ON *.* TO "doadmin"@"%" WITH GRANT OPTION |
| GRANT ROLE_ADMIN ON *.* TO "doadmin"@"%" WITH GRANT OPTION                                                                                                        |
| GRANT ALL PRIVILEGES ON "defaultdb".* TO "doadmin"@"%" WITH GRANT OPTION                                                                                          |
| GRANT SELECT ON "mysql".* TO "doadmin"@"%" WITH GRANT OPTION                                                                                                      |
| GRANT SELECT ON "sys".* TO "doadmin"@"%" WITH GRANT OPTION                                                                                                        |
| GRANT SELECT ON "metrics_user_telegraf".* TO "doadmin"@"%" WITH GRANT OPTION                                                                                      |
| GRANT SELECT ON "performance_schema".* TO "doadmin"@"%" WITH GRANT OPTION   
+---------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.07 sec)

  

To view the grants for another user, you can use SHOW GRANTS while specifying the username:

  
    
SHOW GRANTS FOR 'example_user';
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for example_user@%                                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT CREATE, DROP, RELOAD, PROCESS, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO "example_user"@"%" WITH GRANT OPTION |
| GRANT ROLE_ADMIN ON *.* TO "example_user"@"%" WITH GRANT OPTION                                                                                        |
| GRANT ALL PRIVILEGES ON "defaultdb".* TO "example_user"@"%" WITH GRANT OPTION                                                                          |
| GRANT SELECT ON "mysql".* TO "example_user"@"%" WITH GRANT OPTION                                                                                      |
| GRANT SELECT ON "sys".* TO "example_user"@"%" WITH GRANT OPTION                                                                                        |
| GRANT SELECT ON "metrics_user_telegraf".* TO "example_user"@"%" WITH GRANT OPTION                                                                      |
| GRANT SELECT ON "performance_schema".* TO "example_user"@"%" WITH GRANT OPTION                                                                         |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

  

Resources

For more information on MySQL privileges and how they work, see Privileges Provided by MySQL.