1. Removing users from the database.

DROP USER 'USERNAME'@'HOSTNAME';

2. Creating users – 3 common ways:

A) The default way:

CREATE USER 'USERNAME'@'HOSTNAME' IDENTIFIED BY 'USERPASSWORD';

B) As an insert statements:

INSERT INTO `mysql`.`user` (Host,User,Password)
        VALUES('localhost','dummy','');
FLUSH PRIVILEGES; #it is neccessary, when creating users this way

C) Batch adding users. Create a file (useradd.sql for example) which contains all create users statements :

CREATE USER 'USERNAME'@'HOSTNAME' IDENTIFIED BY 'USERPASSWORD';
GRANT SELECT ON `DATABASE`.* TO 'USERNAME'@'HOSTNAME' ;  
CREATE USER 'USERNAME2'@'HOSTNAME2' IDENTIFIED BY 'USERPASSWORD2';
GRANT SELECT ON `DATABASE2`.* TO 'USERNAME2'@'HOSTNAME2' ;  

and then execute the script:

mysql < useradd.sql > useradd.log
#the output will get to file useradd.sql

2.1 Adding specific grants for users

Allow only insert,update,delete for one table:

GRANT INSERT,UPDATE,DELETE ON `database`.`table` TO 'user'@'host';

2.1 Adding specific grants for all tables starting with prefix

Allow only insert,update,delete for tables with the same prefix:

SELECT   CONCAT('GRANT SELECT ON DB.', TABLE_NAME, ' to ''USER'';') FROM     INFORMATION_SCHEMA.TABLES WHERE     TABLE_NAME LIKE 'PREFIX%';

3. List of mysql users

select user,host from mysql.user;

4. Check users grants

show grants for 'USER'@'HOSTNAME';

If You want to get a detailed information on permissions (user level, schema level, table level) for every user and every table:

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, GRANTEE
 , GROUP_CONCAT(DISTINCT PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE) PRIVILEGE_TYPES
FROM (
 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, GRANTEE, PRIVILEGE_TYPE
  FROM information_schema.TABLES
  INNER JOIN information_schema.user_privileges USING (TABLE_CATALOG)
  WHERE NOT TABLE_SCHEMA IN ('information_schema', 'performance_schema')
   AND GRANTEE=CONCAT("'", REPLACE(CURRENT_USER(), '@', "'@'"), "'")
 UNION ALL
 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, GRANTEE, PRIVILEGE_TYPE
  FROM information_schema.TABLES
  INNER JOIN information_schema.schema_privileges USING (TABLE_CATALOG, TABLE_SCHEMA)
  WHERE NOT TABLE_SCHEMA IN ('information_schema', 'performance_schema')
   AND GRANTEE=CONCAT("'", REPLACE(CURRENT_USER(), '@', "'@'"), "'")
 UNION ALL
 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, GRANTEE, PRIVILEGE_TYPE
  FROM information_schema.TABLES
  INNER JOIN information_schema.table_privileges USING (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
  WHERE NOT TABLE_SCHEMA IN ('information_schema', 'performance_schema')
   AND GRANTEE=CONCAT("'", REPLACE(CURRENT_USER(), '@', "'@'"), "'")
) u
 GROUP BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, GRANTEE
;

• Produce SQL to transfer grants between dbs

  PASS=‘DBPASS’; mysql -B -N  -u root -p$PASS -e “SELECT DISTINCT CONCAT(    ‘SHOW GRANTS FOR \“, user, ‘\‘@\“, host, ‘\‘;’    ) AS query FROM mysql.user” > show_grants
  

PASS=‘DBPASS’; cat show_grants | while read i; do echo “SQL : $i” ;echo “$i” | mysql -u root -p$PASS | sed ’s/(GRANT .)/\1;/;s/^(Grants for .)/## \1 ##/;/##/{x;p;x;}’ >> all_grants; done

And a one-liner for newer versions of mysql

select * from information_schema.user_privileges where grantee like "'USER'%";