How to Create MySQL Users Accounts and Grant Privileges

Managing MySQL User Accounts and Privileges

Introduction MySQL is the most popular open-source relational database management system. It allows the creation of multiple user accounts and grants appropriate privileges to manage databases securely. This guide explains how to create MySQL user accounts and manage privileges effectively.

Prerequisites

Before proceeding, ensure you have MySQL or MariaDB installed on your system. All commands should be executed in the MySQL shell with administrative privileges (root user or equivalent).

To access the MySQL shell, use the following command:

mysql -u root -p

If no password is set for the root user, omit the -p flag.

Creating a New MySQL User Account

A MySQL user account consists of a username and a hostname.

Syntax:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password';

• Replace newuser with the desired username.

• Replace user_password with a secure password.

• localhost restricts access to the local system. Use a specific IP address or % for remote access.

Examples:

-- Allow access only from localhost:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'securepassword';

-- Allow access from a specific IP:
CREATE USER 'newuser'@'10.8.0.5' IDENTIFIED BY 'securepassword';

-- Allow access from any host:
CREATE USER 'newuser'@'%' IDENTIFIED BY 'securepassword';

Granting Privileges to a MySQL User

MySQL allows assigning various privileges to user accounts.

Common Privileges:

• ALL PRIVILEGES – Grants full access.

• CREATE – Allows creating databases and tables.

• DROP – Allows dropping databases and tables.

• DELETE – Allows deleting table rows.

• INSERT – Allows inserting new rows.

• SELECT – Allows reading database records.

• UPDATE – Allows modifying existing rows.

Granting Privileges Syntax:

GRANT privilege1, privilege2 ON database_name.table_name TO 'username'@'host';

Examples:

-- Grant all privileges on a specific database:
GRANT ALL PRIVILEGES ON mydatabase.* TO 'newuser'@'localhost';

-- Grant all privileges on all databases:
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';

-- Grant privileges on a specific table:
GRANT ALL PRIVILEGES ON mydatabase.mytable TO 'newuser'@'localhost';

-- Grant multiple specific privileges on a database:
GRANT SELECT, INSERT, DELETE ON mydatabase.* TO 'newuser'@'localhost';

After granting privileges, apply changes using:

FLUSH PRIVILEGES;

Displaying User Privileges

To view the privileges assigned to a specific user:

SHOW GRANTS FOR 'newuser'@'localhost';

Example Output:

+-----------------------------------------------------------+
| Grants for newuser@localhost                              |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'newuser'@'localhost'               |
| GRANT ALL PRIVILEGES ON `mydatabase`.* TO 'newuser'@'localhost' |
+-----------------------------------------------------------+

Revoking Privileges

To remove specific privileges from a user:

REVOKE privilege1, privilege2 ON database_name.* FROM 'newuser'@'localhost';

To revoke all privileges:

REVOKE ALL PRIVILEGES ON mydatabase.* FROM 'newuser'@'localhost';

Deleting a MySQL User Account

To remove a user account along with its privileges:

DROP USER 'newuser'@'localhost';

Conclusion

This guide covers the basics of creating MySQL user accounts and managing privileges. By properly configuring user roles and access, you can enhance database security and maintain efficient user management.