How to assign permissions to MySQL or MariaDB users

The GRANT statement is used to grant privileges to MySQL users, allowing them to perform certain actions on databases, tables, and other objects in the MySQL server.

It is important to note that the GRANT command should be used with caution, as granting too many privileges to a user can compromise the security of the database. It is best practice to grant the minimum set of privileges necessary to perform a specific task.

It is also possible to revoke privileges that have been granted using the REVOKE command.

1. Basic syntax.

   GRANT [privilege1, privilege2, ...]
   ON [database_name.]table_name
   TO 'username'@'hostname'
   [IDENTIFIED BY 'password']

   1. [privilege1, privilege2, …]: A list of privileges to be granted to the user. Some common privileges include SELECT, INSERT, UPDATE, DELETE, CREATE, and DROP.
   2. [database_name.]table_name: The name of the database and table for which the privileges are being granted. If the database name is omitted, the privileges will apply to all tables in the default database.
   3. 'username'@'hostname': The username and hostname of the user to whom the privileges are being granted. The hostname can be a wildcard (e.g. %) to grant privileges to all users connecting from any host.
   4. IDENTIFIED BY 'password': An optional clause that allows you to set a password for the user.
2. The basic example.

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

   privileges: The privileges to be granted, such as SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, etc. Multiple privileges can be specified using a comma-separated list.
   database.table: The database and/or table on which the privileges should be granted. If only the database is specified, the privileges will apply to all tables in the database. If the table is omitted, the privileges will apply to all tables in the specified database.
   user@host: The username and host for which the privileges should be granted. The host can be a specific IP address or hostname, or a wildcard character (“%”) to indicate any host.
   IDENTIFIED BY 'password': The password for the user. If no password is specified, the user will not be required to enter a password to access the database.

3. Grant SELECT privilege on all tables in a database to a user.

   GRANT SELECT ON mydatabase.* TO 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

4. Grant multiple privileges on a specific table in a database to a user.

   GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.mytable TO 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

5. Grant all privileges on a specific database to a user with wildcard host.

   GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword';

6. Grant all privileges on all databases to a user.

   GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost' IDENTIFIED BY 'mypassword';