MySQL Users & Privileges

By default, MySQL ships with a single root account that has complete, unrestricted control over everything on the server. For production systems — or any environment shared between applications or team members — relying on the root account for every task is a serious security risk.

MySQL's user and privilege system lets you create dedicated accounts with only the access they actually need. A reporting tool can be restricted to SELECT only, a web application can be limited to a single database, and a developer can be given access to a test environment without touching production data.

---

How MySQL Identifies a User

In MySQL, a user account is identified by two parts: the username and the host they are connecting from. These are written together as 'username'@'host'.

The host part controls where connections are allowed from:

• 'app_user'@'localhost' — can only connect from the same machine
• 'app_user'@'192.168.1.10' — can only connect from that specific IP address
• 'app_user'@'%' — can connect from any host (use with caution)

---

Viewing Existing Users

You can list all user accounts on the server by querying the mysql.user system table:

SELECT user, host FROM mysql.user;

A typical result looks like this:

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | localhost |
| mysql.sys        | localhost |
+------------------+-----------+

---

Creating a New User

Use CREATE USER to add a new account to the server. You must specify the username, host, and a password:

CREATE USER 'username'@'host' IDENTIFIED BY 'strong_password';

Examples

-- A local application user
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'S3cure!Pass#2024';

-- A remote user allowed from a specific IP
CREATE USER 'analyst'@'10.0.0.5' IDENTIFIED BY 'An@lyst$Pass99';

-- Use IF NOT EXISTS to avoid an error if the user already exists
CREATE USER IF NOT EXISTS 'webapp'@'localhost' IDENTIFIED BY 'S3cure!Pass#2024';

---

Privilege Levels

Before granting privileges, it helps to understand the four levels at which MySQL permissions can be applied:

---

Granting Privileges

After creating a user, they have no access to anything by default. Use GRANT to give them specific permissions:

GRANT privilege_type ON scope TO 'username'@'host';

Common Privilege Types

Privilege	What it allows
SELECT	Read data from tables
INSERT	Add new rows to tables
UPDATE	Modify existing rows
DELETE	Remove rows from tables
CREATE	Create new databases or tables
DROP	Delete databases or tables
ALTER	Modify table structure
INDEX	Create or drop indexes
ALL PRIVILEGES	All permissions except GRANT OPTION

Grant Examples

-- Read-only access to a specific database
GRANT SELECT ON shop_db.* TO 'analyst'@'10.0.0.5';

-- Full access to a specific database (common for a web application)
GRANT ALL PRIVILEGES ON shop_db.* TO 'webapp'@'localhost';

-- Only SELECT and INSERT on a single table
GRANT SELECT, INSERT ON shop_db.orders TO 'webapp'@'localhost';

-- Full global access (use only for admin accounts)
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'localhost';

After granting privileges, apply them immediately with:

FLUSH PRIVILEGES;

---

Viewing a User's Privileges

To check what permissions a specific user has been granted, use SHOW GRANTS:

-- View your own current privileges
SHOW GRANTS;

-- View privileges for a specific user
SHOW GRANTS FOR 'webapp'@'localhost';

The output lists each GRANT statement that was applied to that user, making it easy to audit access.

---

Revoking Privileges

REVOKE removes a privilege that was previously granted. The syntax mirrors GRANT but uses FROM instead of TO:

REVOKE privilege_type ON scope FROM 'username'@'host';

-- Remove INSERT permission on the orders table
REVOKE INSERT ON shop_db.orders FROM 'webapp'@'localhost';

-- Remove all privileges on a database
REVOKE ALL PRIVILEGES ON shop_db.* FROM 'analyst'@'10.0.0.5';

---

Changing a User's Password

To update a user's password, use ALTER USER:

ALTER USER 'webapp'@'localhost' IDENTIFIED BY 'NewStr0ng!Pass#25';

To change the password of the currently logged-in user:

ALTER USER USER() IDENTIFIED BY 'NewStr0ng!Pass#25';

---

Dropping a User

When a user account is no longer needed, remove it with DROP USER. This deletes the account and all its associated privileges:

DROP USER 'analyst'@'10.0.0.5';

-- Use IF EXISTS to avoid an error if the user does not exist
DROP USER IF EXISTS 'analyst'@'10.0.0.5';

---

Key Points to Remember

• A MySQL user is identified by both a username and a host — 'user'@'host'.
• New users have no privileges by default — you must explicitly GRANT access.
• Use GRANT to assign permissions and REVOKE to remove them.
• Privilege scope ranges from global (*.*) to a specific table (db.table).
• Use SHOW GRANTS FOR 'user'@'host' to audit what a user can do.
• Never use the root account for application connections — always create a dedicated user with the minimum required privileges.