MySQL Grant Command Syntax and Examples

To Give all the permission to iics user to school database:
GRANT ALL ON school.* TO 'iics'@'localhost';

Give the SELECT Privileges to “invoice” table of “db2” database:
GRANT SELECT ON db2.invoice TO 'iics'@'localhost';

Give the USAGE Privileges to all database and tables:
GRANT USAGE ON *.* TO 'iics'@'localhost';

Give All the Privileges to all database and tables:
GRANT ALL ON *.* TO 'iics'@'localhost';

Give INSERT,SELECT Privileges to all database and tables:
GRANT SELECT, INSERT ON *.* TO 'iics'@'localhost';

Give INSERT,SELECT Privileges to specific database and tables:
GRANT SELECT, INSERT ON mydb.* TO 'iics'@'localhost';

Give INSERT,SELECT Privileges to specific columns of the tables:
GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'iics'@'localhost';

Give ALL Privileges to all database and tables with new password:
GRANT ALL PRIVILEGES ON *.* TO db_user @'%' IDENTIFIED BY 'db_passwd';

Give MULTIPLE Privileges to all tables of customer database:
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON customer.* TO 'custom'@'localhost';

To Show all the GRANT Permissions to Current Login User:
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();


To Show all the GRANT Permissions for specific user:
SHOW GRANTS FOR 'iics'@'localhost';

To list all the privileges supported by MySQL:
SHOW PRIVILEGES;

To flush all the privileges:
FLUSH PRIVILEGES;