How To Create a New User and Grant Permissions in MySQL

How to Create a New User and Grant Permissions in MySQL

Creating a new user and granting the appropriate permissions is an essential part of managing MySQL databases. MySQL allows you to create users with specific privileges, ensuring that each user has the necessary access without compromising the security of your database.

1. Creating a New MySQL User

To create a new user in MySQL, you use the CREATE USER statement.

Syntax:

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

• username: The name of the new user.
• host: The host from which the user will connect (e.g., 'localhost' for local connections, '%' for any host).
• password: The password for the new user.

Example:

CREATE USER 'john'@'localhost' IDENTIFIED BY 'secure_password';

This creates a new user named john who can connect from the localhost with the password secure_password.

2. Granting Permissions to the New User

Once the user is created, you need to grant specific permissions to the user. MySQL allows you to grant different levels of access, such as read-only, full access, or more granular permissions like access to specific databases or tables.

Granting Global Permissions

To grant global permissions, use the GRANT statement. These permissions apply to all databases on the server.

Syntax:

GRANT permission_type ON *.* TO 'username'@'host';

• permission_type: The type of permission(s) you want to grant. You can specify individual permissions (e.g., SELECT, INSERT, UPDATE, etc.), or use ALL PRIVILEGES to grant all permissions.
• *.*: The wildcard (*.*) represents all databases and all tables.

Example:

GRANT ALL PRIVILEGES ON *.* TO 'john'@'localhost';

This grants the user john full access (all privileges) to all databases and tables on the MySQL server.

Granting Specific Database Permissions

You can grant permissions on a specific database. This is useful when you want to limit access to only certain databases.

Syntax:

GRANT permission_type ON database_name.* TO 'username'@'host';

Example:

GRANT SELECT, INSERT, UPDATE ON sales_db.* TO 'john'@'localhost';

This grants the user john the ability to SELECT, INSERT, and UPDATE tables in the sales_db database.

Granting Permissions on Specific Tables

If you want to grant access to specific tables within a database, you can specify the table name in the GRANT statement.

Syntax:

GRANT permission_type ON database_name.table_name TO 'username'@'host';

Example:

GRANT SELECT, UPDATE ON sales_db.orders TO 'john'@'localhost';

This grants the user john the ability to SELECT and UPDATE the orders table in the sales_db database.

3. Granting Administrative Permissions

If you want to grant a user administrative privileges, such as the ability to create databases, users, or manage privileges, you can grant the GRANT OPTION along with other permissions.

Example:

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

This grants the user john full access to all databases and tables and allows them to grant privileges to other users.

4. Reloading Privileges

After granting permissions, you need to reload the privilege tables to apply the changes.

FLUSH PRIVILEGES;

This ensures that MySQL recognizes the newly granted permissions.

5. Checking User Privileges

To verify the privileges granted to a user, you can run the following query:

SHOW GRANTS FOR 'username'@'host';

Example:

SHOW GRANTS FOR 'john'@'localhost';

This will display all the permissions granted to the user john from localhost.

6. Revoking Permissions

If you need to revoke certain permissions from a user, you can use the REVOKE statement. For example, if you want to revoke the UPDATE permission for the sales_db.orders table:

REVOKE UPDATE ON sales_db.orders FROM 'john'@'localhost';

This will remove the UPDATE permission for the orders table in the sales_db database.

To revoke all permissions granted to a user, you can use:

REVOKE ALL PRIVILEGES ON *.* FROM 'john'@'localhost';

7. Deleting a User

If you no longer need a user, you can delete them from MySQL with the DROP USER statement.

Syntax:

DROP USER 'username'@'host';

Example:

DROP USER 'john'@'localhost';

This will remove the user john from localhost entirely from the MySQL server.

Conclusion

Creating a new user and granting permissions in MySQL is a fundamental aspect of managing database access. By following the steps outlined above, you can control which users have access to your databases, tables, and specific actions within them. It's important to follow the principle of least privilege, granting users only the permissions they need to perform their tasks.