How to assign permissions to MySQL or MariaDB users

Managing user permissions is a crucial aspect of database administration. Proper permission management ensures that users can only perform tasks they're authorized for, safeguarding the data integrity and security of your database.

In MySQL and MariaDB, users are granted permissions to access databases, tables, and even specific columns based on what they need to achieve. The primary command used to assign permissions is the GRANT statement. The permissions can range from reading (SELECT) to making modifications (INSERT, UPDATE, DELETE) and even administrative tasks like creating or deleting databases.

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.

1. Basic syntax.

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

   Parameter	Description
   [privilege1, privilege2, …]	A list of privileges to be granted to the user. Some common privileges include SELECT, INSERT, UPDATE, DELETE, CREATE, and DROP.
   [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.
   '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.
   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';

   Parameter	Description
   privileges	A list of privileges to be granted to the user. Some common privileges include SELECT, INSERT, UPDATE, DELETE, CREATE, and DROP.
   database.table	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.
   user@host	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.
   IDENTIFIED BY 'password'	An optional clause that allows you to set a password for the user.

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';