How to use GRANT ALL PRIV­ILEGES in MariaDB

The GRANT ALL PRIVILEGES command in MariaDB gives a user full priv­ileges on a database. A user with these priv­ileges 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 full 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­trat­ive functions. By using this command stra­tegic­ally, you can control and manage access rights within your database en­vir­on­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 on with an ad­min­is­trat­ive account and connect to the correct database instance. Define the username and host prefix carefully to avoid granting rights to un­in­ten­ded 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­ileges on a database is:

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

This statement gives the specified user full per­mis­sions for 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­tic­ate the user.

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

FLUSH PRIVILEGES;

This ensures that the granted priv­ileges are active im­me­di­ately and stored correctly in the system.

How to create a user and assign priv­ileges

To create a new user in MariaDB and give them full priv­ileges, 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­ileges on a database

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

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

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

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

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

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

You can grant only certain priv­ileges 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­ileges 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 actions the user can perform.

Step 5: Apply changes

The priv­ileges should update auto­mat­ic­ally. To ensure they come into effect im­me­di­ately, run the following:

FLUSH PRIVILEGES;

Step 6: View granted priv­ileges

To see the priv­ileges 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­ileges for databases or tables and specific per­mis­sions, such as the ability to pass on priv­ileges (WITH GRANT OPTION).