# MySQL/MariaDB User Management and Access Privileges

> MySQL user management: CREATE USER, GRANT, REVOKE, DROP USER. Database, table and column-level permissions. View privileges with SHOW GRANTS.

## Creating a MySQL User

```
-- Create a user that may connect from localhost only
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'StrongPassword123!';

-- For connections from any IP
CREATE USER 'myuser'@'%' IDENTIFIED BY 'StrongPassword123!';

-- For one specific IP
CREATE USER 'myuser'@'192.168.1.100' IDENTIFIED BY 'StrongPassword123!';
```

## Granting Privileges (GRANT)

```
-- All privileges on a single database
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';

-- Read-only
GRANT SELECT ON mydb.* TO 'readonly_user'@'localhost';

-- Read + write
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'localhost';

-- On one specific table
GRANT SELECT, INSERT ON mydb.orders TO 'reports_user'@'localhost';

-- Superuser (all privileges on all databases)
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- Apply the changes
FLUSH PRIVILEGES;
```

## Viewing Privileges

```
-- Privileges of a specific user
SHOW GRANTS FOR 'myuser'@'localhost';

-- List of all users
SELECT User, Host FROM mysql.user;

-- Current user
SHOW GRANTS;
```

## Revoking Privileges (REVOKE)

```
-- Revoke specific privileges
REVOKE INSERT, UPDATE, DELETE ON mydb.* FROM 'myuser'@'localhost';

-- Revoke all privileges
REVOKE ALL PRIVILEGES ON mydb.* FROM 'myuser'@'localhost';
FLUSH PRIVILEGES;
```

## Changing a User Password

```
-- MySQL 5.7 / MariaDB
SET PASSWORD FOR 'myuser'@'localhost' = PASSWORD('NewPassword456!');

-- MySQL 8.0+
ALTER USER 'myuser'@'localhost' IDENTIFIED BY 'NewPassword456!';
FLUSH PRIVILEGES;
```

## Dropping a User

```
DROP USER 'myuser'@'localhost';
FLUSH PRIVILEGES;
```

## Creating a User for a Web Application

```
-- Create a database and a user for a new site
CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'SecurePass789!';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,
    CREATE TEMPORARY TABLES, LOCK TABLES ON myapp_db.* TO 'myapp_user'@'localhost';
FLUSH PRIVILEGES;
```

**Principle of least privilege:** give users only the rights they really need. For web applications: SELECT, INSERT, UPDATE, DELETE. No CREATE, DROP or ALTER — unless the application requires them.

## Common Access Privilege Errors

| Error | Cause | Fix |
| --- | --- | --- |
| ERROR 1045 (28000): Access denied for user 'app'@'10.0.0.5' | The user was created as `'app'@'localhost'`, but the connection comes from a different host or IP | Create a separate user entry for that host, or use `'app'@'%'` restricted at the firewall level |
| ERROR 1142 (42000): SELECT command denied to user | The user was not granted SELECT on a specific table or database | Check `SHOW GRANTS FOR 'user'@'host';` and grant the missing privilege |
| ERROR 1396 (HY000): Operation CREATE USER failed | A user with that name and host already exists | Use `CREATE USER IF NOT EXISTS` or DROP USER first |

## The '%' Host Mask — a Common Configuration Mistake

A privilege for `'user'@'%'` allows connecting from any IP address if the MySQL server listens for external connections (`bind-address` not restricted to 127.0.0.1). For a web application user that always runs on the same server as the database, the only correct choice is `'user'@'localhost'`, not `'%'`: this prevents a connection to the database even if the password leaks to an external IP, in case port 3306 is ever accidentally exposed. Confirming the port is closed externally does not replace a correctly configured user host mask — these are two independent layers of defense.

## When GRANT ALL Is Not a Solution but a Problem

`GRANT ALL PRIVILEGES ON *.* WITH GRANT OPTION` only makes sense for an administrative account used by a human, not for an application's service account. An application that only reads and writes data does not need `DROP`, `SHUTDOWN`, or `CREATE USER` privileges — in case of a SQL injection or a leaked password from .env, the attacker gets exactly the privilege set granted to that user. A minimal set for a typical web application is SELECT, INSERT, UPDATE, DELETE, plus CREATE TEMPORARY TABLES and LOCK TABLES if an ORM needs them.

## Verifying Actual Privileges After Setup

```
# Full list of the user's privileges as the server sees them
mysql -u root -p -e "SHOW GRANTS FOR 'app_user'@'localhost';"

# Verify the user genuinely cannot see other databases
mysql -u app_user -p -e "SHOW DATABASES;"

# Find users with dangerously broad privileges
mysql -u root -p -e "
SELECT User, Host, Super_priv, Grant_priv, Shutdown_priv
FROM mysql.user
WHERE Super_priv = 'Y' OR Grant_priv = 'Y';"
```

Run the last query periodically as an audit: if an application's service user shows up in the list instead of just the administrative account, that is a reason to review its privileges under the principle of least privilege. Recovering access after a lost password is covered in the article on [resetting the root password](https://www.zevshost.net/en/kb/mysql-root-password-reset/).

---

Source: https://www.zevshost.net/en/kb/mysql-user-privileges/
Updated: 2026-09-30
