This guide will walk you through the steps to create a new superuser with root-like access to the databases in MariaDB.

In MariaDB, by default, you can use GRANT command to create a new user and grant privileges at the same time. You don’t need to run a CREATE USER command separately.

If the NO_AUTO_CREATE_USER SQL_MODE is set, a CREATE USER command is needed to create a user first, and the GRANT command will produce an error when the specified user does not exist.

To use the GRANT command, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting.

To create a superuser, you need to log in with a superuser account, then run this command:

GRANT ALL PRIVILEGES ON  *.* to 'user_name'@'host_name' WITH GRANT OPTION;

  • The ALL PRIVILEGES privilege grants all available privileges.

  • Using ALL PRIVILEGES does not grant the special GRANT OPTION privilege.

  • Use the WITH GRANT OPTION clause to give users the ability to grant privileges to other users at the given privilege level.

Certain privileges can only be set at certain levels:

  • Global privileges include privileges to administer the database and manage user accounts, as well as privileges for all tables, functions, and procedures.
  • Database privileges include privileges to create tables and functions, as well as privileges for all tables, functions, and procedures in the database.
  • Table privileges include the ability to select and change data in the table.
  • Column privileges allow you to control exactly which columns in a table that users can select and change.
  • Function privileges to specify the access to a function in the database.
  • Procedure privileges to specify the access to a procedure in the database.

Granting all privileges only affects the given privilege level. For example, granting all privileges on a table does not grant any privileges on the database or globally.

If the hostname is not provided, it is assumed to be ’%’. The ’username’@’%’ account uses the ’%’ wildcard for the host part, so it can be used to connect from any host.

To double check the privileges given to the new user, run SHOW GRANTS command:

SHOW GRANTS FOR user_name;

To revoke the superuser status:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_name;

Need a good GUI Tool for MariaDB? Try TablePlus. It’s is a modern, native GUI that allows you to simultaneously manage multiple databases such as MySQL, MariaDB, PostgreSQL, SQLite, Microsoft SQL Server… faster and easier.


Download TablePlus for Mac. It’s free anyway!

Not on Mac? Download TablePlus for Windows.

TablePlus GUI Tool MySQL