Create users in Azure Database for MySQL

  • Commodity
  • three minutes to read

APPLIES TO: Azure Database for MySQL - Single Server Azure Database for MySQL - Flexible Server

This commodity describes how to create users for Azure Database for MySQL.

Note

This article contains references to the term slave, a term that Microsoft no longer uses. When the term is removed from the software, we'll remove it from this article.

When you first created your Azure Database for MySQL server, y'all provided a server admin user proper noun and password. For more data, see this Quickstart. You can make up one's mind your server admin user proper name in the Azure portal.

The server admin user has these privileges:

SELECT, INSERT, UPDATE, DELETE, CREATE, Driblet, RELOAD, Procedure, REFERENCES, INDEX, Modify, Testify DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, Alter ROUTINE, CREATE USER, EVENT, TRIGGER

After you create an Azure Database for MySQL server, you can use the commencement server admin account to create more than users and grant admin access to them. You tin also use the server admin business relationship to create less privileged users that accept access to individual database schemas.

Note

The SUPER privilege and DBA role aren't supported. Review the privileges in the limitations commodity to empathize what's not supported in the service.

Password plugins like validate_password and caching_sha2_password aren't supported by the service.

Create a database

  1. Get the connection data and admin user proper noun. To connect to your database server, you need the full server proper name and admin sign-in credentials. You can easily find the server name and sign-in data on the server Overview folio or on the Properties page in the Azure portal.

  2. Apply the admin business relationship and password to connect to your database server. Use your preferred customer tool, such every bit MySQL Workbench, mysql.exe, or HeidiSQL.

  1. Edit and run the following SQL code. Supervene upon the placeholder value db_user with your intended new user name. Replace the placeholder value testdb with your database name.

    This SQL lawmaking creates a new database named testdb. It so creates a new user in the MySQL service and grants all privileges for the new database schema (testdb.*) to that user.

                      CREATE DATABASE testdb;

Create a non-admin user

Now that the database is created , y'all can create with a not-admin user with the CREATE USER MySQL statement.

              CREATE USER 'db_user'@'%' IDENTIFIED BY 'StrongPassword!';  GRANT ALL PRIVILEGES ON testdb . * TO 'db_user'@'%';  Affluent PRIVILEGES;

Verify the user permissions

Run the SHOW GRANTS MySQL statement to view the privileges allowed for user db_user on testdb database.

              Utilise testdb;  SHOW GRANTS FOR 'db_user'@'%';

Connect to the database with new user

Sign in to the server, specifying the designated database and using the new user name and password. This instance shows the mysql control line. When yous use this command, you'll be prompted for the user's password. Use your own server name, database proper name, and user name. Encounter how to connect for Single server and Flexible server below.

Server type Usage
Unmarried Server mysql --host mydemoserver.mysql.database.azure.com --database testdb --user db_user@mydemoserver -p
Flexible Server mysql --host mydemoserver.mysql.database.azure.com --database testdb --user db_user -p

Limit privileges for user

To restrict the type of operations a user can run on the database, you need to explicitly add the operations in the GRANT statement. See an example beneath:

              CREATE USER 'new_master_user'@'%' IDENTIFIED BY 'StrongPassword!';  GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, Driblet, RELOAD, Procedure, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION Client, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, Alter ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'new_master_user'@'%' WITH GRANT Choice;  Flush PRIVILEGES;

About azure_superuser

All Azure Database for MySQL servers are created with a user called "azure_superuser". This is a system business relationship created by Microsoft to manage the server to carry monitoring, backups, and other regular maintenance. On-call engineers may too use this account to access the server during an incident with document authentication and must request access using just-in-time (JIT) processes.

Side by side steps

For more information well-nigh user account management, encounter the MySQL production documentation for User account management, GRANT syntax, and Privileges.