title | description | author | ms.author | ms.service | ms.topic | ms.date |
---|---|---|---|---|---|---|
Create users - Azure Database for MariaDB |
This article describes how you can create new user accounts to interact with an Azure Database for MariaDB server. |
ajlam |
andrela |
mariadb |
conceptual |
4/2/2020 |
This article describes how you can create users in Azure Database for MariaDB.
Note
Bias-free communication
Microsoft supports a diverse and inclusionary environment. This article contains references to the word slave. The Microsoft style guide for bias-free communication recognizes this as an exclusionary word. The word is used in this article for consistency because it's currently the word that appears in the software. When the software is updated to remove the word, this article will be updated to be in alignment.
When you first created your Azure Database for MariaDB, you provided a server admin login user name and password. For more information, you can follow the Quickstart. You can locate your server admin login user name from the Azure portal.
The server admin user gets certain privileges for your server as listed: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, 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
Once the Azure Database for MariaDB server is created, you can use the first server admin user account to create additional users and grant admin access to them. Also, the server admin account can be used to create less privileged users that have access to individual database schemas.
Note
The SUPER privilege and DBA role are not supported. Review the privileges in the limitations article to understand what's not supported in the service.
-
Get the connection information and admin user name. To connect to your database server, you need the full server name and admin sign-in credentials. You can easily find the server name and sign-in information from the server Overview page or the Properties page in the Azure portal.
-
Use the admin account and password to connect to your database server. Use your preferred client tool, such as MySQL Workbench, mysql.exe, HeidiSQL, or others. If you are unsure of how to connect, see Use MySQL Workbench to connect and query data
-
Edit and run the following SQL code. Replace your new user name for the placeholder value
new_master_user
. This syntax grants the listed privileges on all the database schemas (.) to the user name (new_master_user in this example).CREATE USER 'new_master_user'@'%' IDENTIFIED BY 'StrongPassword!'; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, 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 OPTION; FLUSH PRIVILEGES;
-
Verify the grants
USE sys; SHOW GRANTS FOR 'new_master_user'@'%';
-
Get the connection information and admin user name. To connect to your database server, you need the full server name and admin sign-in credentials. You can easily find the server name and sign-in information from the server Overview page or the Properties page in the Azure portal.
-
Use the admin account and password to connect to your database server. Use your preferred client tool, such as MySQL Workbench, mysql.exe, HeidiSQL, or others. If you are unsure of how to connect, see Use MySQL Workbench to connect and query data
-
Edit and run the following SQL code. Replace the placeholder value
db_user
with your intended new user name, and placeholder valuetestdb
with your own database name.This sql code syntax creates a new database named testdb for example purposes. Then it creates a new user in the Azure Database for MariaDB service, and grants all privileges to the new database schema (testdb.*) for that user.
CREATE DATABASE testdb; CREATE USER 'db_user'@'%' IDENTIFIED BY 'StrongPassword!'; GRANT ALL PRIVILEGES ON testdb . * TO 'db_user'@'%'; FLUSH PRIVILEGES;
-
Verify the grants within the database.
USE testdb; SHOW GRANTS FOR 'db_user'@'%';
-
Log in to the server, specifying the designated database, using the new user name and password. This example shows the mysql command line. With this command, you are prompted for the password for the user name. Replace your own server name, database name, and user name.
mysql --host mydemoserver.mariadb.database.azure.com --database testdb --user db_user@mydemoserver -p
For more information regarding user account management, see MariaDB documentation for User account management, GRANT Syntax, and Privileges.
Open the firewall for the IP addresses of the new users' machines to enable them to connect: Create and manage Azure Database for MariaDB firewall rules by using the Azure portal