How to Create User Accounts in MySQL Database

To create user accounts in MySQL, you’ll need appropriate privileges. Here’s how you can create a user:

1. Log in to MySQL as a User with Sufficient Privileges:

mysql -u username -p

Replace username with your MySQL username. You’ll be prompted for your MySQL password.

2. Create a New User:

CREATE USER 'new_username'@'localhost' IDENTIFIED BY 'password';

• Replace new_username with the desired username.
• 'localhost' indicates that this user can only connect from the local machine. For remote access, replace it with the appropriate IP or % for all hosts.
• Replace password with the desired password for the user.

3. Grant Privileges to the User:

You can grant specific privileges or use ALL PRIVILEGES for full access.

For example, to grant all privileges on a specific database:

GRANT ALL PRIVILEGES ON database_name.* TO 'new_username'@'localhost';

4. Reload Privileges:

After creating or modifying user accounts, you should reload the privileges to apply the changes:

FLUSH PRIVILEGES;

Example with Specific Privileges:

If you want to grant specific privileges (e.g., select, insert, update) on a specific database, use the following syntax:

GRANT privilege_type(s) ON database_name.table_name TO 'new_username'@'localhost';

For example, to grant SELECT and INSERT privileges:

GRANT SELECT, INSERT ON database_name.table_name TO 'new_username'@'localhost';

Create User and Grant on All Databases:

If you want to create a user with privileges across all databases:

CREATE USER 'new_username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'new_username'@'localhost';

Viewing Existing Users:

You can view existing users by running:

SELECT user, host FROM mysql.user;

Removing a User:

To remove a user:

DROP USER 'username'@'localhost';

Replace username with the username you want to remove.

Important Notes:

• Always use strong passwords for your users.
• Grant only the necessary privileges to each user to follow the principle of least privilege.
• Avoid using the root user for day-to-day tasks for security reasons.

Remember to replace placeholders like new_username, database_name, and password with your actual values. Creating users with appropriate privileges helps ensure secure and controlled access to your MySQL database.