MySQL User Management

MySQL user management involves creating, modifying, and removing database user accounts. Each user account controls who can connect to the MySQL server and from where. Managing users is essential for securing the database — different users should have only the access they need to perform their work.

Viewing Existing Users

SELECT user, host FROM mysql.user;

MySQL stores user information in the built-in mysql.user table. The host column specifies from which machine the user can connect (localhost means local connections only; % means any host).

Creating a New User

Syntax

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

Examples

-- User who can only connect from the same machine
CREATE USER 'ravi'@'localhost' IDENTIFIED BY 'Secure@123';

-- User who can connect from any machine
CREATE USER 'sneha'@'%' IDENTIFIED BY 'Pass@456';

-- User allowed from a specific IP address
CREATE USER 'karan'@'192.168.1.10' IDENTIFIED BY 'DBPass@789';

Setting and Changing Passwords

Change Password for a User

ALTER USER 'ravi'@'localhost' IDENTIFIED BY 'NewPass@999';

Change Own Password

ALTER USER USER() IDENTIFIED BY 'MyNewPass@111';

Renaming a User

RENAME USER 'ravi'@'localhost' TO 'ravi_kumar'@'localhost';

Dropping a User

DROP USER 'sneha'@'%';

This removes the user account and all their privileges. The data in the tables is not affected.

Locking and Unlocking a User Account

-- Lock a user (prevents login without deleting the account)
ALTER USER 'karan'@'192.168.1.10' ACCOUNT LOCK;

-- Unlock a user
ALTER USER 'karan'@'192.168.1.10' ACCOUNT UNLOCK;

Viewing User Privileges

SHOW GRANTS FOR 'ravi'@'localhost';

The root User

The root user is the default superuser in MySQL with full access to all databases and operations. It is created during installation. Best practice is to keep the root account for administration only and create separate limited accounts for applications.

User Account Components

Practical Example: Create a Read-Only App User

-- Step 1: Create the user
CREATE USER 'app_reader'@'localhost' IDENTIFIED BY 'ReadOnly@2024';

-- Step 2: Grant only SELECT permission on a specific database
GRANT SELECT ON school_db.* TO 'app_reader'@'localhost';

-- Step 3: Apply the changes
FLUSH PRIVILEGES;

This user can only read data from school_db. They cannot insert, update, delete, or access any other database.

Key Points

• MySQL users are identified by both a username and a host.
• Use CREATE USER to add new accounts and DROP USER to remove them.
• Passwords are changed with ALTER USER ... IDENTIFIED BY.
• Accounts can be locked without deletion using ACCOUNT LOCK.
• Always create dedicated, limited-privilege accounts for applications rather than using root.