How to use GRANT ALL PRIVILEGES in MariaDB

The GRANT ALL PRIVILEGES command in MariaDB gives a user full privileges on a database. A user with these privileges can create, modify and delete tables without restrictions.

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 permissions to create, modify and delete tables, as well as access to administrative functions. By using this command strategically, you can control and manage access rights within your database environment.

To run GRANT ALL PRIVILEGES in MariaDB, you need advanced system rights. This usually means having the SUPER privilege or permission to grant rights (GRANT OPTION). You need to log in with an administrative account and connect to the correct database instance. Define the username and host prefix carefully to avoid granting rights to unintended 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 privileges on a database is:

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

This statement gives the specified user full permissions 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 authenticate the user.

After executing the command, reload the privileges so the changes take effect:

FLUSH PRIVILEGES;

This ensures that the granted privileges are active immediately and stored correctly in the system.

How to create a user and assign privileges

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

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

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

Step 3: Allow the user to grant privileges (GRANT OPTION)

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

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

Step 4: Grant specific privileges to a database or table

You can grant only certain privileges 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 privileges 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 privileges should update automatically. To ensure they take effect immediately, run the following:

FLUSH PRIVILEGES;

Step 6: View granted privileges

To see the privileges assigned to a user, enter the following command:

SHOW GRANTS FOR 'newuser'@localhost;

This lists all the permissions for newuser in the MariaDB instance. The output includes both general privileges for databases or tables and specific permissions, such as the ability to pass on privileges (WITH GRANT OPTION).