MariaDB - How to create user and grant privileges?

As MariaDB is the drop-in replacement for MySQL, we benefit from the fact that MariaDB supports the same flavor of SQL syntax of MySQL, along with a few minor improvements. So if you are familiar with MySQL, it’s easy to start with MariaDB without changing your queries or your data files.

In this post, we are going to learn how to create a new user and grant privileges to that new user in MariaDB.

1. Create a new user

In order to create a new user, you have to make sure that you have the global CREATE USER privilege or the INSERT privilege for the database.

To list all the current privileges granted to your user, run one of these following statements:

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

If it’s MariaDB 10.0.5 and newer, you can also show the privileges granted for a user role:

SHOW GRANTS FOR role;

Now, run this statement to create a new user

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'your_password';

The IDENTIFY BY clause is optional. If you don’t specify a password, the new user can be able to connect without a password.

2. Grant privileges

The GRANT statement syntax:

GRANT privileges ON object TO user;

The GRANT statement also allows you to create a new user and grant privileges to that user, in case it doesn’t exist yet. Note that if the NO_AUTO_CREATE_USER SQL_MODE is set, you can not create a new user by GRANT statement and it will return an error.

List of privileges to grant:

CREATE - the ability to create new tables or databases
DROP - the ability to them to delete tables or databases
DELETE - the ability to delete rows from tables
INSERT - the ability to insert rows into tables
SELECT - the ability to use the SELECT command to read through databases
UPDATE - the ability to update table rows
GRANT OPTION - allows them to grant or remove other users’ privileges
ALL PRIVILEGES - all of the above.

If you are granting access on function or procedure, it will have EXECUTE privileges providing the ability to execute the function or procedure.

List of object levels:

Global privilege: global access across the system.
Database privilege: include privileges to create tables and functions, as well as privileges for all tables, functions, and procedures in the database.
Table privilege: include the ability to select and change data in the table
Column privilege: select and change on an exact column in a table.
Function privilege: access to a function.
Procedure privilege: access to a procedure.

To revoke privileges:

You might need to withdraw the permissions granted:

REVOKE privileges ON object FROM user;

Need a good GUI Tool for MySQL or 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. 