How to set user permissions and access control in MySQL?

To set user permissions and access control in MySQL, you can use the GRANT and REVOKE statements. Here are the steps for setting user permissions and access control in MySQL.

1. Connect to MySQL Server: Use MySQL client to connect to MySQL server.
2. To create a user: Before setting permissions for a new user, you must first create them. Use the CREATE USER statement to do so.

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

1. Granting permissions: Use the GRANT statement to assign permissions to users. You can specify permissions for specific databases or tables, as well as grant users global permissions.

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

For example, assigning SELECT permissions to users.

GRANT SELECT ON database_name.table_name TO 'username'@'hostname';

1. Refresh Permissions: After setting up user permissions, it is necessary to refresh permissions in order for the changes to take effect.

FLUSH PRIVILEGES;

1. Revoke permission: If you need to remove certain permissions from a user, you can use the REVOKE statement.

REVOKE permission_type ON database_name.table_name FROM 'username'@'hostname';

For instance, revoking a user’s SELECT permission:

REVOKE SELECT ON database_name.table_name FROM 'username'@'hostname';

By following the above steps, you can set user permissions and access control in MySQL. Make sure to assign appropriate permissions only to the users who need them to ensure the security of the database.