How to create MySQL admin user (superuser) account

How do I create an admin user in MySQL? How can I set up and create a superuser account in a MySQL server running on Linux or Unix-like systems? Can you provide a quick guide about making a superuser with root-like access to the MySQL or MariaDB databases?

Both MySQL and MariaDB (drop-in replacement for Oracle MySQL) are free and open-source database systems to build dynamic applications running on Linux, *BSD, and Unix-like systems. Typically we use PHP, Perl, Python, Java, and other server-side programming languages with MySQL server. This page explains how to create a MySQL admin user (superuser) account using your Linux or Unix shell. The commands also work on the MariaDB server.
Tutorial details
Difficulty Intermediate (rss)
Root privileges Yes
Requirements MySQL 8.x or MariaDB 10.4+
Time 5m

Creating MySQL admin user in MySQL server

The steps to create a new user in MySQL and make it a superuser/admin are as follows:

Step 1 – Login to MySQL server

The syntax is:
$ mysql -u root -p
$ mysql -h host_name_ip -u root -p

Step 2 – Create admin user account

Run the following command at mysql> prompt:

Warning: For security reasons, you should not use % as this allows access to everyone. I strongly recommend restricting access to localhost or sysadmin/developers CIDR (Classless inter-domain routing) hidden with VLAN and firewalled port.

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'the_secure_password';
We can create user for network access too for all users. The percent sign, (%) means all ip address:
CREATE USER 'admin'@'%' IDENTIFIED BY 'the_secure_password';
Limit access to 10.147.164.0/24 CIDR (10.147.164.0/255.255.255.0 subnet):
CREATE USER 'admin'@'10.147.164.0/255.255.255.0' IDENTIFIED BY 'the_secure_password';
In this example, create a new user called ‘sayali’ and restrict her access to the 192.168.1.0/24 subnet. Further, TLS/SSL is used to protect transmission with an encryption protocol.
CREATE USER 'sayali'@'192.168.1.0/255.255.255.0' IDENTIFIED WITH 'mysql_native_password' BY 'password_here' REQUIRE SSL;
In MySQL version 8.x, caching_sha2_password is the default strong authentication plugin rather than mysql_native_password, which was the default in MySQL 5.7. In other words, we can switch back to mysql_native_password plugin for backward compatibility purposes that implement native authentication. We do not recommend to use the mysql_native_password authentication plugin for new installations that require high password security. Say, if an attacker can both listen to the connection protocol and get a copy of the mysql.user table, then the person would be able to use this information to connect to the MariaDB/MySQL server. Hence the following is recommend syntax for MySQL 8.x server:
CREATE USER 'vivek'@'192.168.1.0/255.255.255.0' IDENTIFIED BY 'password_here' REQUIRE SSL;
MariaDB v10.04+ example for ed25519 auth plugin:
CREATE USER 'username'@'host' IDENTIFIED VIA ed25519 USING PASSWORD('password-here');
Use the following command to list users with their plugins:
SELECT user,plugin from mysql.user;
SELECT user,plugin,host,ssl_type from mysql.user;
SELECT user,plugin,host from mysql.user;

Click to enlarge

Step 3 – Grant PRIVILEGES to admin user

The SQL syntax is as follows to grant all privileges on database:
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'vivek'@'10.147.164.0/255.255.255.0';

The *.* means all databases on MySQL or MariaDB server. For security purposes, you should not set up and grant all permissions for a mysql admin user account with access via the website or any other means. The solution is to grant full permission to the specific database as follows:
# grant full access to proddb for admin user only #
GRANT ALL PRIVILEGES ON proddb.* TO 'admin'@'%';
# Give vivek admin access to blogdb only #
GRANT ALL PRIVILEGES ON blogdb.* TO 'vivek'@'10.147.164.0/255.255.255.0';

The blogdb.* means all tables of blogdb MySQL/MariaDB database.

Understanding MySQL grants for admin (superuser) account

The syntax is:

GRANT permission_type ON db_name.table_name TO 'user'@'host';
GRANT permission_type ON db_name.* TO 'user'@'host';

Here is a list of standard privileges:

  1. Data USAGE privileges includes: SELECT, INSERT, UPDATE, DELETE, and FILE
  2. Structure privileges includes: CREATE, ALTER, INDEX, DROP, CREATE TEMPORARY TABLES, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, CREATE VIEW, EVENT, and TRIGGER
  3. Administration privileges includes: GRANT, SUPER, PROCESS, RELOAD, SHUTDOWN, SHOW DATABASES, LOCK TABLES, REFERENCES, REPLICATION CLIENT, REPLICATION SLAVE, and CREATE USER
  4. SSL privileges includes: REQUIRE NONE, REQUIRE SSL, REQUIRE X509
  5. ALL PRIVILEGES: Shortcut to grants all privileges to a mysql user account.

For instance:
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `admin`@`%`;
# more fine tuning with database permissions #
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER, UPDATE ON *.* TO `vivek`@`10.147.164.0/255.255.255.0` WITH GRANT OPTION;

We can list user GRANTS as follows:
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR 'admin'@'%';
SHOW GRANTS FOR 'vivek'@'10.147.164.0/255.255.255.0';

Here is what you will see from the last SQL:

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for vivek@10.147.164.0/255.255.255.0                                                                                                                                                                                                                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 `vivek`@`10.147.164.0/255.255.255.0` WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Step 4 – Reload all the privileges

Now we set up a new user MySQL admin account with the required password. It is time to reload changes:
FLUSH PRIVILEGES;

Step 5 – Testing

From the client machine run:
$ mysql -u admin -h 10.147.164.6 -p db_name
$ mysql -u vivek -h 192.168.1.100 -p
# if you set TLS/SSL requirements, give CA file in PEM format #
$ mysql --ssl-ca=/path/to/our.tls.pem --ssl-mode=VERIFY_CA -u vivek -h 192.168.1.100 -p

Where,

  • --ssl-ca=/path/to/our.tls.pem : CA file bundle in PEM format.
  • --ssl-mode=VERIFY_CA : TLS/SSL connection mode. PEM certificates are only validated for VERIFY_CA and VERIFY_FULL SSL mode values.
  • -u vivek : Admin user name created in step #1.
  • -h 192.168.1.100 : MySQL server IP address.
  • -p : Prompt for MySQL server password.
  • db_name : State database name

Creating secure login file

The mysql_config_editor command allows us to store authentication credentials in a secure encrypted format in a confused login path file named $HOME/.mylogin.cnf. The file is not readable for human eyes. Hence it is called a confusing format. The syntax is:
$ mysql_config_editor set \
--login-path=remote \
--host=10.147.164.6 \
--port=3306 \
--user=admin \
--password

State TLS/SSL pem file in your ~/.my.cnf file too if encryption enabled for admin account:
$ vim ~/.my.cnf
Append/modify as follows:

[client]
ssl-mode=VERIFY_CA 
ssl-ca=/path/to/our.mysql-tls.pem

Verify it:
$ ls -l $HOME/.mylogin.cnf
$ file $HOME/.mylogin.cnf
$ cat $HOME/.mylogin.cnf

Print it:
$ mysql_config_editor print --all
Now all you have to do is type the following command to login as admin user:
$ mysql --login-path=remote

Please note that MariaDB doesn’t support mysql_config_editor feature. MariaDB users need to store info in the ~/.my.cnf itself, which is not secure enough as your admin password stored in a plain text format:

[client]
user=admin
password='your_password_here'
host=10.147.164.6
port=3306
ssl-mode=VERIFY_CA 
ssl-ca=/home/admin/tls-ca-bundle.pem

Wrapping up

This quick tutorial explained how to securely create admin (superuser) on MySQL or MariaDB server using the CLI and grant additional permissions as per your requirements. We further explained how to store the password securely in ~/.mylogin.cnf and TLS paths in ~/.my.cnf file for ease of login. If you wish to drop admin user, try:
DROP USER `user`@`host`;
To remove PRIVILEGES run:
REVOKE ALL PRIVILEGES ON *.* FROM `user`@`%`;


🐧 Please support my work on Patreon or with a donation.
🐧 Get the latest tutorials on Linux, Open Source & DevOps via:
CategoryList of Unix and Linux commands
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
0 comments… add one

Leave a Reply

Your email address will not be published. Required fields are marked *

Use HTML <pre>...</pre> for code samples. Problem posting comment? Email me @ webmaster@cyberciti.biz