# 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](https://www.ionos.co.uk/digitalguide/server/know-how/what-is-mariadb/), the `GRANT ALL PRIVILEGES` command gives a user full 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 on 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:

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

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

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

```sql
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:

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

The [MariaDB CREATE USER](https://www.ionos.co.uk/digitalguide/hosting/technical-matters/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.

```sql
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:

```sql
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:

```sql
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:

```sql
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 privileges should update automatically. To ensure they come into effect immediately, run the following:

```sql
FLUSH PRIVILEGES;
```

### Step 6: View granted privileges

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

```sql
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`).


This is a markdown version of: [https://www.ionos.co.uk/digitalguide/hosting/technical-matters/mariadb-grant-all-privileges/](https://www.ionos.co.uk/digitalguide/hosting/technical-matters/mariadb-grant-all-privileges/) for AI/LLM consumption.