How to Modify User Privileges in MySQL Databases
MySQL is an open source, object-relational database built with speed and reliability in mind. Its large and active developer community has created many third-party applications, tools, and libraries that expand MySQL’s functionality.
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 only 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.
Additionally by default, every database cluster is publicly accessible. To limit access, you can add trusted sources or manage user permissions by following this guide.
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:
mysqlsysmetrics_user_telegrafperformance_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
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 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 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.