How to Create Databases and Users in Azure Database for MySQL (Flexible Server)

Managing users and databases in Azure Database for MySQL (Flexible Server) is similar to standard MySQL, but with one important difference: Azure is a managed database service, so some “superuser-style” permissions (especially global grants) are intentionally restricted for security and platform stability.

This guide shows you the correct, Azure-friendly approach to:

Connect securely to Azure MySQL
Create a new database
Create DBA-style and application users
Assign the right permissions (without hitting “Access denied” errors)
Validate access from a Linux VM

Why “GRANT ALL ON .” Can Fail in Azure MySQL

On self-managed MySQL, DBAs often run commands like:

GRANT ALL PRIVILEGES ON *.* TO 'some_user'@'%' WITH GRANT OPTION;

In Azure Database for MySQL, this frequently fails with an Access denied error even when you are logged in as the admin user. That’s because Azure restricts certain global permissions and “superuser” behaviors.

✅ Best practice in Azure: grant privileges at the database level (e.g., mydb.*) instead of server-wide (*.*).

Prerequisites

Before you begin, confirm you have:

An Azure Database for MySQL Flexible Server running
The MySQL client installed on your Linux VM
Network connectivity to the server (Private Endpoint/VNet Integration or approved firewall rules)
The administrator username and password created during server provisioning

Step 1: Connect to the Azure MySQL Server

From your Linux server, connect using the admin login:

mysql -h <mysql-server-fqdn> -u <admin-username> -p

Example format (do not copy this literally):

<mysql-server-fqdn>: your server DNS name in Azure
<admin-username>: the admin account configured for the server

If login succeeds, you’ll land in the MySQL prompt.

Step 2: Confirm the Logged-in Identity and Privileges

Run these commands to confirm who you are and what you can do:

SELECT USER(), CURRENT_USER();
SHOW GRANTS FOR CURRENT_USER();

This helps verify:

The account you authenticated with
The effective MySQL user context Azure mapped you to
What privileges are currently assigned

Step 3: Check Existing Databases

List databases:

SHOW DATABASES;

On a fresh server, you may only see system schemas like:

information_schema
mysql
performance_schema
sys

To use the server for applications, you need to create your own database.

Step 4: Create a New Database

Create a new database with recommended UTF-8 support:

CREATE DATABASE myappdb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

Verify it exists:

SHOW DATABASES;

Step 5: Create a DBA-Style User (Database-Scoped Admin)

Azure MySQL doesn’t support creating a full “superuser clone.”
But you can create a strong DBA-style account that has full control within a specific database.

Create the user:

CREATE USER 'db_admin_user'@'%' IDENTIFIED BY 'Use-A-Strong-Password-Here!';

Grant full privileges for the database:

GRANT ALL PRIVILEGES ON myappdb.* TO 'db_admin_user'@'%';
FLUSH PRIVILEGES;

Validate:

SHOW GRANTS FOR 'db_admin_user'@'%';

✅ This is the recommended approach for giving DB management access in Azure MySQL.

Step 6: Create an Application User (Least Privilege)

For application connectivity, it’s best to grant only what the app needs.

Create the user:

CREATE USER 'app_user'@'%' IDENTIFIED BY 'Use-A-Strong-Password-Here!';

Grant typical CRUD access:

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON myappdb.* TO 'app_user'@'%';
FLUSH PRIVILEGES;

Validate:

SHOW GRANTS FOR 'app_user'@'%';

Step 7: Test User Login from Linux

Exit MySQL:

exit;

Test the DBA-style user:

mysql -h <mysql-server-fqdn> -u db_admin_user -p -D myappdb

Then run a quick permission test:

CREATE TABLE perm_test(id INT PRIMARY KEY);
DROP TABLE perm_test;

If both commands work, the permissions are correct.

Optional: Use Roles for Cleaner Permission Management

Roles help manage privileges across multiple users and databases.

Create a role:

CREATE ROLE 'db_admin_role';

Grant database privileges to the role:

GRANT ALL PRIVILEGES ON myappdb.* TO 'db_admin_role';

Assign role to a user:

GRANT 'db_admin_role' TO 'db_admin_user'@'%';
SET DEFAULT ROLE 'db_admin_role' TO 'db_admin_user'@'%';
FLUSH PRIVILEGES;

Now, when you create a new database, you can grant the role access without reworking each user manually.

Security Best Practices (Recommended)

Avoid global grants like *.* unless absolutely necessary (and Azure may block them anyway).
Use least privilege for application accounts.
Prefer private networking (VNet integration / private endpoints) over public access.
Use strong passwords (or integrate with a secrets manager).
Confirm whether SSL is required: SHOW VARIABLES LIKE 'require_secure_transport';
Rotate credentials regularly and remove unused accounts.

Summary

In Azure Database for MySQL Flexible Server:

✅ Create databases normally
✅ Create users normally
✅ Grant permissions at database scope (mydb.*)
❌ Avoid or expect failure when attempting global superuser-style grants (*.*)

This pattern keeps you aligned with Azure’s managed security model and avoids common “Access denied” permission errors.

How to Create Databases and Users in Azure Database for MySQL (Flexible Server)