How do you assign permissions to users in MySQL?

1 year ago
Olivia Parker
1 minute

To grant permissions to MySQL users, you can use the GRANT statement. The GRANT statement is used to give users specific permissions, and the syntax is as follows:

GRANT privileges ON database.table TO user@host IDENTIFIED BY 'password';

In this context, privileges refer to the granted permissions such as ALL, SELECT, INSERT, UPDATE, DELETE, etc.; database.table refers to the database and table that the authorization is for; user@host refers to the username and access host that the authorization is for; ‘password’ refers to the user’s password.

Here are a few examples:

  1. Grant all permissions to the user.
GRANT ALL PRIVILEGES ON database.* TO user@host IDENTIFIED BY 'password';
  1. Grant users SELECT permission.
GRANT SELECT ON database.table TO user@host IDENTIFIED BY 'password';
  1. Grant users the SELECT and INSERT permissions.
GRANT SELECT, INSERT ON database.table TO user@host IDENTIFIED BY 'password';
  1. Grant users all permissions and allow access from any host.
GRANT ALL PRIVILEGES ON database.* TO user@'%' IDENTIFIED BY 'password';

Before executing the GRANT statement, make sure to log in to MySQL as a user with authorization permissions. If you need to revoke a user’s permissions, you can use the REVOKE statement.

bannerAds