Creating and Managing Database Users in MySQL

Course: MySQL Training

Topic: Creating and Managing Database Users

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to Database User Management

Database user management is the process of creating, configuring, securing, and maintaining user accounts that access a database server. In MySQL, user accounts control who can connect to the server and which database operations they are permitted to perform.

In a real-world application, different people and applications require different levels of access. A reporting employee may need permission to read information, while an application account may need permission to insert and update records. A database administrator may require additional privileges to manage schemas, users, and database objects.

MySQL provides account-management statements such as CREATE USER, ALTER USER, DROP USER, GRANT, REVOKE, and SHOW GRANTS to manage accounts and permissions.

Why Is Database User Management Important?

• Protects sensitive database information.
• Controls which users can read or modify records.
• Prevents unauthorized database operations.
• Supports separate access levels for administrators, developers, and applications.
• Helps implement the principle of least privilege.
• Improves accountability by using individual accounts.
• Supports safer database administration and maintenance.

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand MySQL users and account names.
• Create database users using CREATE USER.
• Modify existing accounts using ALTER USER.
• Assign permissions using GRANT.
• Remove permissions using REVOKE.
• Inspect user privileges using SHOW GRANTS.
• Rename and delete user accounts.
• Understand the purpose of roles and resource limits.
• Apply database security best practices.
• Manage users in a practical application environment.

3. Understanding MySQL User Accounts

A MySQL user account is an identity used to authenticate a connection and determine the privileges available to that connection. In MySQL, an account is generally identified by a username and a host component.

Account Name Format

'username'@'host'

For example:

'app_user'@'localhost'

This identifies the account named app_user that connects from the local host.

Common Host Values

• localhost — identifies a local connection account.
• 127.0.0.1 — identifies a connection using the IPv4 loopback address; connection behavior can differ from a local socket connection.
• '%' — a wildcard host pattern that can match connections from multiple hosts, subject to MySQL account-matching rules.
• A specific hostname or IP address — restricts the account to a defined host pattern.

Security note: Avoid using a wildcard host unnecessarily. Prefer a specific host or appropriately restricted network configuration whenever possible.

4. What Is CREATE USER?

CREATE USER is a MySQL statement used to create a new account. It can define authentication settings and, where supported, account properties such as password-management rules, connection security requirements, resource limits, and account locking.

Basic Syntax

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

Example

CREATE USER 'report_user'@'localhost'
IDENTIFIED BY 'Replace_With_A_Strong_Password';

This statement creates a local account named report_user. The example password is a placeholder; replace it with a strong, unique credential and do not use the placeholder as a real production password.

Creating an account does not automatically grant it permission to read or modify application tables. Appropriate privileges must be assigned separately.

Required Privileges

The account executing CREATE USER must have the necessary administrative privileges, such as the global CREATE USER privilege, subject to MySQL's privilege requirements and configuration.

5. Creating Multiple Users

MySQL allows multiple accounts to be created in a single CREATE USER statement.

CREATE USER
    'report_user'@'localhost'
        IDENTIFIED BY 'Replace_With_Password_One',
    'app_user'@'localhost'
        IDENTIFIED BY 'Replace_With_Password_Two',
    'developer_user'@'localhost'
        IDENTIFIED BY 'Replace_With_Password_Three';

Use separate, strong credentials for each account. Avoid sharing one account among multiple people because individual accounts improve accountability and make access revocation easier.

6. Creating a User If It Does Not Already Exist

The IF NOT EXISTS option allows a script to continue without failing merely because an account already exists.

CREATE USER IF NOT EXISTS
    'report_user'@'localhost'
    IDENTIFIED BY 'Replace_With_A_Strong_Password';

If the account already exists, MySQL generates a warning rather than creating a duplicate account. This clause does not update the existing account's password or other properties. Use ALTER USER when an existing account needs to be changed.

7. Understanding Database Privileges

Privileges determine which actions an account may perform. MySQL supports privileges at different levels, including global, database, table, column, and stored-routine levels.

Grant only the privileges required for the user's responsibilities. A user who only generates reports generally does not need permission to delete records or change table definitions.

8. Granting Database Privileges Using GRANT

The GRANT statement assigns privileges to a user or role.

8.1 Grant SELECT Permission

GRANT SELECT
ON company_db.*
TO 'report_user'@'localhost';

This grants read access to tables in company_db, subject to the specified account and MySQL privilege rules.

8.2 Grant Read and Write Permissions

GRANT SELECT, INSERT, UPDATE
ON company_db.*
TO 'app_user'@'localhost';

This account can read data, insert rows, and update rows in the specified database. It does not receive DELETE permission from this statement.

8.3 Grant Permission on a Specific Table

GRANT SELECT
ON company_db.employees
TO 'report_user'@'localhost';

This grants SELECT permission on the employees table rather than every table in the database.

8.4 Grant Permission on Specific Columns

GRANT SELECT (employee_id, employee_name)
ON company_db.employees
TO 'report_user'@'localhost';

Column-level privileges can be useful when a user needs access to selected columns instead of the entire table. Verify whether other existing privileges also allow access to additional columns.

8.5 Grant All Privileges on One Database

GRANT ALL PRIVILEGES
ON company_db.*
TO 'developer_user'@'localhost';

This grants the privileges covered by ALL at the specified database level. It does not automatically grant every global administrative privilege or the GRANT OPTION. Use broad grants only when they are justified by the user's role.

9. Understanding Privilege Levels

Use the narrowest practical privilege level. Database-level or table-level access is often safer than granting broad global privileges to application accounts.

10. Viewing User Privileges with SHOW GRANTS

The SHOW GRANTS statement displays the privilege and role assignments associated with an account.

Syntax

SHOW GRANTS FOR 'report_user'@'localhost';

Example output may resemble:

GRANT USAGE ON *.* TO 'report_user'@'localhost'
GRANT SELECT ON `company_db`.* TO 'report_user'@'localhost'

This is illustrative output. The exact statements displayed depend on the account's actual privileges and roles.

Why Use SHOW GRANTS?

• Verify whether the correct privileges were assigned.
• Review access before deploying an application.
• Investigate unexpected permission errors.
• Audit accounts during security reviews.
• Check permissions before changing or removing an account.

11. Modifying Accounts Using ALTER USER

The ALTER USER statement changes properties of an existing MySQL account. Depending on the MySQL version and configuration, it can modify authentication settings, password policies, account locking, and resource limits.

11.1 Change a User's Password

ALTER USER 'report_user'@'localhost'
IDENTIFIED BY 'Replace_With_A_New_Strong_Password';

Replace the placeholder with a secure password using an approved credential-management process. Do not store production passwords in public source code or shared documents.

11.2 Lock an Account

ALTER USER 'report_user'@'localhost'
ACCOUNT LOCK;

Account locking prevents the account from establishing new authenticated connections. It does not terminate sessions that are already connected.

11.3 Unlock an Account

ALTER USER 'report_user'@'localhost'
ACCOUNT UNLOCK;

Unlock an account only after confirming that the account should be allowed to connect again.

11.4 Set a Connection Limit

ALTER USER 'app_user'@'localhost'
WITH MAX_USER_CONNECTIONS 10;

This example sets a maximum of 10 simultaneous connections for the account. Resource limits depend on the MySQL version and server configuration.

12. Removing Privileges with REVOKE

The REVOKE statement removes previously granted privileges. It is useful when a user's responsibilities change or when access is no longer required.

12.1 Revoke SELECT Permission

REVOKE SELECT
ON company_db.*
FROM 'report_user'@'localhost';

This removes the specified database-level SELECT grant. Other grants or roles may still provide access.

12.2 Revoke Write Permissions

REVOKE INSERT, UPDATE
ON company_db.*
FROM 'app_user'@'localhost';

The account no longer has those specific privileges through this grant. Always check for other applicable privileges or active roles when verifying effective access.

Important Difference Between REVOKE and DROP USER

• REVOKE: Removes specified privileges or role assignments.
• DROP USER: Removes the account itself.

13. Renaming a User Account

MySQL supports the RENAME USER statement for changing an account name.

RENAME USER
    'old_user'@'localhost'
TO
    'new_user'@'localhost';

Before renaming an account, review application connection settings, scheduled jobs, stored objects, and operational procedures that may depend on the old account name.

14. Deleting User Accounts with DROP USER

The DROP USER statement removes an account that is no longer needed.

Syntax

DROP USER 'report_user'@'localhost';

Delete Only If the Account Exists

DROP USER IF EXISTS
    'report_user'@'localhost';

Before deleting an account, confirm that applications and users no longer depend on it. Removing an account is different from locking it: dropping the account removes it from MySQL's account-management system, while locking prevents new authenticated connections without deleting the account.

15. Understanding MySQL Roles

A role is a named collection of privileges that can be granted to users. Roles make permission management easier when multiple users need the same access.

15.1 Create a Read-Only Role

CREATE ROLE 'company_read_role';

GRANT SELECT
ON company_db.*
TO 'company_read_role';

15.2 Create a Read-Write Role

CREATE ROLE 'company_write_role';

GRANT SELECT, INSERT, UPDATE
ON company_db.*
TO 'company_write_role';

15.3 Assign a Role to a User

GRANT 'company_read_role'
TO 'report_user'@'localhost';

A role's privileges are effective when the role is active for the session. Configure the appropriate default role where required, and verify the effective privileges in the target MySQL version.

15.4 Set a Default Role

SET DEFAULT ROLE 'company_read_role'
TO 'report_user'@'localhost';

Roles help avoid repeatedly assigning identical privileges to individual users. They also simplify access reviews when a job responsibility changes.

16. Applying the Principle of Least Privilege

The principle of least privilege means giving each account only the permissions required to complete its tasks. It is a central database security practice.

17. Database User Security Best Practices

• Create separate accounts for different people, services, and applications.
• Use strong, unique credentials and approved password-management procedures.
• Do not use the root account for routine application connections.
• Grant only the minimum required privileges.
• Avoid broad global privileges for ordinary application users.
• Restrict host access where practical.
• Use secure connections, including TLS, when required by the deployment environment.
• Do not place passwords directly in source code, public repositories, or unsecured configuration files.
• Review privileges periodically and remove unnecessary access.
• Lock or remove accounts when they are no longer required.
• Audit access changes according to organizational policies.
• Use roles when multiple users need the same permission set.
• Test privilege changes with an appropriate non-administrative account.
• Keep database backups and recovery procedures current.

18. Common Errors and Troubleshooting

19. Practical Exercise: Create and Manage Users

Objective: Create a MySQL user, assign limited privileges, inspect the grants, and then modify access.

Step 1: Create a Database

CREATE DATABASE company_db;

Step 2: Create a Sample Table

CREATE TABLE company_db.employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50)
);

Step 3: Create a User

CREATE USER 'report_user'@'localhost'
IDENTIFIED BY 'Replace_With_A_Strong_Password';

Step 4: Grant Read Access

GRANT SELECT
ON company_db.*
TO 'report_user'@'localhost';

Step 5: Verify the Grants

SHOW GRANTS FOR 'report_user'@'localhost';

Step 6: Test the Account

Connect using the report_user account with its configured credentials. Confirm that a SELECT query works and that INSERT or DELETE operations are denied unless the account has another applicable grant.

Step 7: Revoke Read Access

REVOKE SELECT
ON company_db.*
FROM 'report_user'@'localhost';

Step 8: Verify Again

SHOW GRANTS FOR 'report_user'@'localhost';

Review the result and confirm that the specified privilege has been removed. Remember that other grants or active roles can still provide access.

20. Mini Project: User Access Management System

Project Title: Managing User Accounts and Permissions for a Company Database

Project Scenario

A company maintains an employee database used by administrators, application services, and reporting staff. Each group needs a different level of access. Your task is to create and manage accounts that follow the principle of least privilege.

Project Tasks

1. Create a database named company_db.
2. Create an employees table and insert sample records.
3. Create separate accounts for reporting, application operations, and development.
4. Grant each account only the permissions required for its tasks.
5. Use SHOW GRANTS to verify account privileges.
6. Create a role for a common permission set and assign it to suitable users.
7. Change a user's password using ALTER USER.
8. Temporarily lock an account and test the connection behavior.
9. Revoke a privilege and verify the result.
10. Document how each account is secured and maintained.

Expected Deliverables

• SQL scripts for creating the database and accounts.
• A privilege matrix describing each account's access.
• SHOW GRANTS output for the configured users.
• Results of account and permission tests.
• A brief security review identifying any unnecessary privileges.

21. Interview Questions and Answers

Q1. What is a MySQL user account?

Answer: A MySQL user account is an identity used to authenticate connections and determine which operations are permitted. The account is identified by a username and host component.

Q2. Which statement creates a user?

Answer: The CREATE USER statement creates a new MySQL account.

Q3. Does creating a user automatically grant table access?

Answer: No. A newly created account has no application privileges by default. Appropriate permissions or roles must be assigned.

Q4. What is the purpose of GRANT?

Answer: GRANT assigns privileges or roles to a MySQL user account or role.

Q5. What is the purpose of REVOKE?

Answer: REVOKE removes specified privileges or role assignments.

Q6. What does SHOW GRANTS do?

Answer: It displays the privileges and role grants associated with a specified account.

Q7. What is the difference between ALTER USER and DROP USER?

Answer: ALTER USER changes properties of an existing account, while DROP USER removes the account.

Q8. What is the principle of least privilege?

Answer: It means granting users only the permissions they need to perform their assigned tasks.

Q9. Why should applications avoid using the root account?

Answer: The root account has broad administrative capabilities. A compromised application using that account could cause extensive damage. A dedicated application account should have limited permissions.

Q10. What is a MySQL role?

Answer: A role is a named collection of privileges that can be assigned to users, making permission management more consistent.

Q11. What is the difference between locking and dropping a user?

Answer: Locking prevents the account from establishing new authenticated connections without deleting it. Dropping removes the account from MySQL's account-management system.

Q12. Why should user privileges be reviewed regularly?

Answer: Regular reviews help identify excessive access, remove permissions that are no longer needed, and reduce the risk of unauthorized database operations.

22. Quick Revision Checklist

• Understand the MySQL username and host format.
• Use CREATE USER to create accounts.
• Use GRANT to assign privileges.
• Use SHOW GRANTS to inspect permissions.
• Use ALTER USER to change account properties.
• Use REVOKE to remove specified privileges.
• Use RENAME USER to rename an account.
• Use DROP USER to remove an account.
• Understand database-, table-, and column-level privileges.
• Use roles to manage shared permission sets.
• Apply the principle of least privilege.
• Protect credentials and restrict host access.
• Test access changes using an appropriate account.

23. Conclusion

Creating and Managing Database Users is a fundamental part of MySQL administration and database security. Statements such as CREATE USER, ALTER USER, GRANT, REVOKE, SHOW GRANTS, and DROP USER provide the tools needed to create accounts, assign appropriate permissions, review access, and manage account lifecycles.

For secure database operations, use individual accounts, strong credentials, restricted host access, carefully scoped privileges, and regular access reviews. Roles can simplify permission management, while practical testing helps confirm that users can perform their required tasks without receiving unnecessary access.

24. Additional Learning Resources

• Official MySQL Documentation: CREATE USER
• Official MySQL Documentation: GRANT Statement
• Official MySQL Documentation: Account Management Statements
• Official MySQL Documentation: Using Roles
• Explore MySQL Training at JustAcademy
• Register for a Course Demo