How to restrict user access in Managed MySQL

Users you create in the Control Panel or through the API have the same privileges as the admin user, upadmin, including access to every database. This guide shows how to give users only the access they need, for example an application that should use only its own database, or a reporting tool or data pipeline that should read only selected data.

For how users and privileges work on Managed MySQL, see Managed MySQL users and permissions.

Prerequisites

• A Managed MySQL service.
• A MySQL client that can connect to the service as upadmin. See Connecting to Managed MySQL.

Run the statements in this guide as upadmin. Replace the database, table, user names and passwords with your own.

Give a user access to one database

CREATE DATABASE shop;
CREATE USER 'shop_app'@'%' IDENTIFIED BY 'replace-with-a-strong-password';
GRANT ALL PRIVILEGES ON shop.* TO 'shop_app'@'%';

You can also create the database in the Databases tab of the Control Panel instead of with CREATE DATABASE.

shop_app has full access to shop and no access to your other databases.

Give a user read-only access to a view

A view lets you share selected columns or rows with another service without giving it access to the underlying table.

CREATE TABLE shop.orders (
  id INT PRIMARY KEY,
  customer_email VARCHAR(255),
  total DECIMAL(10,2),
  created_at DATETIME
);
CREATE VIEW shop.orders_summary AS
  SELECT id, total, created_at FROM shop.orders;
CREATE USER 'reporting'@'%' IDENTIFIED BY 'replace-with-a-strong-password';
GRANT SELECT ON shop.orders_summary TO 'reporting'@'%';

reporting can query shop.orders_summary, but not shop.orders or any other table.

Create views as upadmin and keep the default SQL SECURITY DEFINER. A view stops working if the user that created it loses its privileges or is deleted. To fix a view, recreate it as upadmin:

CREATE OR REPLACE VIEW shop.orders_summary AS
  SELECT id, total, created_at FROM shop.orders;

Existing grants on the view are kept.

Restrict a user created in the Control Panel

A user created in the Control Panel or API starts with the same privileges as upadmin. If the user created any views, recreate them as upadmin first. Then revoke all of the user's privileges and grant only what it needs:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'etl_service'@'%';
GRANT SELECT ON shop.orders_summary TO 'etl_service'@'%';

Check the result with:

SHOW GRANTS FOR 'etl_service'@'%';

+--------------------------------------------------------------+
| Grants for etl_service@%                                     |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO "etl_service"@"%"                      |
| GRANT SELECT ON "shop"."orders_summary" TO "etl_service"@"%" |
+--------------------------------------------------------------+

GRANT USAGE means the user can connect but has no other global privileges.

Connections that are already open keep the user's previous privileges until they reconnect. To close them, select them in the Connections tab of the Control Panel and select Terminate connection, or restart the application that uses them.

Connect as a restricted user

The connection details in the Control Panel are for upadmin and use defaultdb as the database. To connect as another user, replace the username, password and database name in the connection string:

mysql://reporting:<password>@<hostname>:<port>/shop?ssl-mode=REQUIRED

Keep defaultdb only if the user has access to it. Otherwise, the connection fails with ERROR 1044 (42000): Access denied for user 'reporting'@'%' to database 'defaultdb'.

With the mysql command-line client, give the database name as the last argument:

mysql --host=<hostname> --port=<port> --user=reporting \
  --password --ssl-mode=REQUIRED shop

Troubleshooting

• ERROR 1044 (42000): Access denied for user '<user>'@'%' to database 'defaultdb' when connecting

  The user has no access to defaultdb. Connect to a database the user has access to.

• ERROR 1045 (28000): Access denied for user '<user>'@'%' (using password: YES) when querying a view

  The user that created the view has been deleted. Recreate the view as upadmin.

• ERROR 1045 (28000): Access denied for user 'upadmin'@'%' (using password: YES) after GRANT ALL PRIVILEGES ON *.*

  Grant specific privileges, or all privileges on a single database.

• ERROR 1356 (HY000): View '<view>' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

  The user that created the view has lost its privileges, or the view uses SQL SECURITY INVOKER. Recreate the view as upadmin with the default SQL SECURITY DEFINER.

For the full privilege syntax, see the MySQL reference manual for GRANT and REVOKE.

MySQL is a registered trademark of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.