How to use GRANT ALL PRIV­I­LEGES in MariaDB

The GRANT ALL PRIVILEGES command in MariaDB gives a user full priv­i­leges on a database. A user with these priv­i­leges can create, modify and delete tables without re­stric­tions.

What is MariaDB GRANT ALL PRIVILEGES?

In MariaDB, the GRANT ALL PRIVILEGES command gives a user complete access rights to one or more databases. It includes per­mis­sions to create, modify and delete tables, as well as access to ad­min­is­tra­tive functions. By using this command strate­gi­cal­ly, you can control and manage access rights within your database en­vi­ron­ment.

To run GRANT ALL PRIVILEGES in MariaDB, you need advanced system rights. This usually means having the SUPER privilege or per­mis­sion to grant rights (GRANT OPTION). You need to log in with an ad­min­is­tra­tive account and connect to the correct database instance. Define the username and host prefix carefully to avoid granting rights to un­in­tend­ed users. The database you want to grant access to must already exist, since this command applies only to existing databases.

What is the syntax of the MariaDB GRANT ALL PRIVILEGES command?

The basic syntax for granting all priv­i­leges on a database is:

GRANT ALL PRIVILEGES ON database.* TO 'username'@'host';

This statement gives the specified user full per­mis­sions on the chosen database. The ’username’@host part specifies which user can connect and from which host. You can also set a password in the same statement to au­then­ti­cate the user.

After executing the command, reload the priv­i­leges so the changes take effect:

FLUSH PRIVILEGES;

This ensures that the granted priv­i­leges are active im­me­di­ate­ly and stored correctly in the system.

How to create a user and assign priv­i­leges

To create a new user in MariaDB and give them full priv­i­leges, follow these steps:

Step 1: Create user

Create a new user with a secure password. Use the following command:

CREATE USER 'newuser'@localhost IDENTIFIED BY 'strongpassword';

The MariaDB CREATE USER command creates the account and assigns a password. Replace newuser with the desired username and strongpassword with a secure password. The localhost value restricts access to con­nec­tions from the local server. To allow access from another host, replace localhost with the IP address or hostname of that server.

Step 2: Grant all priv­i­leges on a database

Grant the user all available priv­i­leges on all tables in a specific database. The * wildcard applies the priv­i­leges to every table in that database.

GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@localhost;

Step 3: Allow the user to grant priv­i­leges (GRANT OPTION)

Add WITH GRANT OPTION if you want the user to be able to pass their priv­i­leges on to others:

GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@localhost WITH GRANT OPTION;

Step 4: Grant specific priv­i­leges to a database or table

You can grant only certain priv­i­leges to a database or table. For example, to give read-only access to all tables in a database:

GRANT SELECT ON database_name.* TO 'newuser'@localhost;

Here, ’newuser’ can read all data in the database_name database but cannot make any changes. The * after the database name applies the privilege to every table in that database.

To grant priv­i­leges on a specific table only:

GRANT SELECT ON database_name.table_name TO 'newuser'@localhost;

This allows ’newuser’ to read data from the table_name within the database_name database. This method gives you precise control over what the user can do.

Step 5: Apply changes

The priv­i­leges should update au­to­mat­i­cal­ly. To ensure they take effect im­me­di­ate­ly, run the following:

FLUSH PRIVILEGES;

Step 6: View granted priv­i­leges

To see the priv­i­leges assigned to a user, enter the following command:

SHOW GRANTS FOR 'newuser'@localhost;

This lists all the per­mis­sions for newuser in the MariaDB instance. The output includes both general priv­i­leges for databases or tables and specific per­mis­sions, such as the ability to pass on priv­i­leges (WITH GRANT OPTION).