Easy MySQL Database Administration, Creating Users and Assigning Privileges

Whether you are setting up a web application, a development environment, or a production server, the same core tasks come up every time — create a database, create a user, and grant that user the right permissions. This guide walks through all of it clearly, with real examples you can adapt immediately.

Before You Begin — Connecting to MySQL

All commands in this guide are run inside the MySQL shell. Connect as the root user (or any user with administrative privileges):

1

mysql -u root -p

You will be prompted for the root password. Once authenticated, your prompt changes to:

mysql>

Everything below is typed at this mysql> prompt unless stated otherwise.

Part 1 — Working with Databases

Create a Database

1

CREATE DATABASE myapp_db;

With an explicit character set and collation (recommended for modern applications):

1
2
3

CREATE DATABASE myapp_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

utf8mb4 supports the full Unicode range including emoji. Always prefer it over the older utf8 in MySQL, which is actually a limited 3-byte variant.

List All Databases

1

SHOW DATABASES;

Select a Database to Work In

1

USE myapp_db;

Check Which Database Is Currently Active

1

SELECT DATABASE();

Drop (Delete) a Database

1

DROP DATABASE myapp_db;

Warning: DROP DATABASE is immediate and irreversible. There is no undo. Always take a backup first.

Part 2 — Creating Users

Create a User

1

CREATE USER 'username'@'host' IDENTIFIED BY 'StrongPassword123!';

The host part controls where the user is allowed to connect from:

Examples:

1
2
3
4
5
6
7
8

-- Local-only user (most common for web apps on the same server)
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'SecurePass!99';

-- User allowed to connect from anywhere (use with caution)
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'StrongRemotePass!';

-- User restricted to a specific IP
CREATE USER 'deploy'@'10.0.0.5' IDENTIFIED BY 'DeployPass#77';

List All Users

1

SELECT User, Host FROM mysql.user;

Check the Currently Logged-In User

1

SELECT USER();

Change a User’s Password

1

ALTER USER 'username'@'localhost' IDENTIFIED BY 'NewStrongPassword!';

Apply the change immediately:

1

FLUSH PRIVILEGES;

Rename a User

1

RENAME USER 'old_name'@'localhost' TO 'new_name'@'localhost';

Drop (Delete) a User

1

DROP USER 'username'@'localhost';

Part 3 — Granting Privileges

Privileges control exactly what a user is allowed to do. MySQL has a rich set of granular permissions — from reading data all the way up to managing the server itself.

Grant All Privileges on a Specific Database

The most common setup for a web application user:

1
2

GRANT ALL PRIVILEGES ON myapp_db.* TO 'webapp'@'localhost';
FLUSH PRIVILEGES;

myapp_db.* means every table (and future tables) in the myapp_db database.

Grant Specific Privileges

For tighter security, grant only the permissions the user actually needs:

1
2

GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'webapp'@'localhost';
FLUSH PRIVILEGES;

Privilege Reference

Grant on a Specific Table

Restrict a user to a single table within a database:

1
2

GRANT SELECT, INSERT ON myapp_db.orders TO 'reports'@'localhost';
FLUSH PRIVILEGES;

Grant Global Privileges (All Databases)

Use *.* to grant across the entire MySQL server — only for administrative accounts:

1
2

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

WITH GRANT OPTION allows this user to grant their own privileges to others. Only assign this to trusted administrators.

Part 4 — Inspecting Privileges

Show Privileges for a Specific User

1

SHOW GRANTS FOR 'webapp'@'localhost';

Sample output:

+-------------------------------------------------------------------------------------+
| Grants for webapp@localhost                                                         |
+-------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `webapp`@`localhost`                                          |
| GRANT ALL PRIVILEGES ON `myapp_db`.* TO `webapp`@`localhost`                        |
+-------------------------------------------------------------------------------------+

Show Privileges for the Current User

1
2
3

SHOW GRANTS;
-- or
SHOW GRANTS FOR CURRENT_USER();

Part 5 — Revoking Privileges

Revoking is the reverse of granting — same syntax, different keyword.

Revoke Specific Privileges

1
2

REVOKE DELETE ON myapp_db.* FROM 'webapp'@'localhost';
FLUSH PRIVILEGES;

Revoke All Privileges on a Database

1
2

REVOKE ALL PRIVILEGES ON myapp_db.* FROM 'webapp'@'localhost';
FLUSH PRIVILEGES;

Revoke Global Privileges

1
2

REVOKE ALL PRIVILEGES, GRANT OPTION ON *.* FROM 'admin'@'localhost';
FLUSH PRIVILEGES;

Note: After revoking, always run FLUSH PRIVILEGES to ensure changes take effect immediately without requiring a server restart.

Part 6 — A Complete Real-World Setup

Here is the full sequence for setting up a new application database from scratch — everything in one block:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- 1. Create the database
CREATE DATABASE myapp_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- 2. Create an application user (local access only)
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'VeryStr0ng#Pass!';

-- 3. Grant full access to the application database
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost';

-- 4. Apply changes
FLUSH PRIVILEGES;

-- 5. Confirm
SHOW GRANTS FOR 'myapp_user'@'localhost';

If you also need a read-only reporting user:

1
2
3

CREATE USER 'myapp_readonly'@'localhost' IDENTIFIED BY 'ReadOnly#Pass99';
GRANT SELECT ON myapp_db.* TO 'myapp_readonly'@'localhost';
FLUSH PRIVILEGES;

Part 7 — Managing the root User

Checking root’s Authentication Plugin

On newer MySQL versions, root uses the auth_socket or caching_sha2_password plugin, which may prevent normal password login:

1

SELECT User, Host, plugin FROM mysql.user WHERE User = 'root';

Switch root to Password Authentication

1
2
3

ALTER USER 'root'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'NewRootPassword!';
FLUSH PRIVILEGES;

Or use the older mysql_native_password plugin if your application or driver requires it:

1
2
3

ALTER USER 'root'@'localhost'
  IDENTIFIED WITH mysql_native_password BY 'NewRootPassword!';
FLUSH PRIVILEGES;

Best practice: Do not use root for application database access. Always create a dedicated user with only the privileges that application actually needs.

Quick Reference Cheat Sheet

Security Best Practices

Use localhost whenever possible. Unless your application server and database server are on different machines, always create users with @'localhost'. Allowing connections from % opens the database to the entire network.

Follow the principle of least privilege. A web application that only reads and writes data does not need CREATE, DROP, or ALTER privileges. Grant only what is necessary — if it does not need it, do not give it.

Never use root for application access. The root user has unrestricted access to everything. A SQL injection vulnerability in your application could compromise the entire database server if it runs as root.

Use strong passwords. MySQL passwords should be long, random, and include mixed characters. Store them in environment variables or a secrets manager — never hardcode them in source code.

Audit privileges regularly. Run SELECT User, Host FROM mysql.user; and SHOW GRANTS FOR 'user'@'host'; periodically. Remove users and privileges that are no longer needed.

Restrict remote access at the firewall level. Even if a user exists with @'%', your firewall should block MySQL’s port (3306) from untrusted networks. Database access should never be exposed to the public internet.