User permissions can be granted specifically or generally. Permissions can be granted for all databases, a specific database and all tables within that database, a specific database and a specific table or even at the column level.
Common privileges:
- ALL PRIVILEGES: Used to grant all privileges to the user account.
- INSERT: This allows the user to insert rows into a table.
- SELECT: Allows users to read a database.
- UPDATE: Allows users to update table rows.
- CREATE: Allows users to create a database and table.
- DELETE: This allows the user to delete rows from a table.
- DROP: Allows users to delete a database and table.
After you change permissions, you should execute a “FLUSH PRIVILEGES;” command.
- Grant all permissions to all databases and all tables within those databases:
1 2
GRANT ALL ON *.* TO 'myuser'@'localhost'; GRANT ALL ON *.* TO 'myuser'@'%';
- Grant all permissions to a specific database and all tables within that database:
1 2
GRANT ALL ON database_name.* TO 'myuser'@'localhost'; GRANT ALL ON database_name.* TO 'myuser'@'%';
- Grant all permissions to a specific database and a specific table within that database:
1 2
GRANT ALL ON database_name.table_name TO 'myuser'@'localhost'; GRANT ALL ON database_name.table_name TO 'myuser'@'%';
Source: https://dev.mysql.com/doc/refman/8.0/en/grant.html
Source: https://www.atlantic.net/dedicated-server-hosting/how-to-create-a-new-user-and-grant-permissions-in-mysql8-on-centos8/