MySQL user management – Tidbits from the Web

Create a user that is allowed to log in from anywhere (with or without password ‘123’):

CREATE USER 'testuser'@'%' IDENTIFIED BY '123';
CREATE USER 'testuser'@'%';

1 2	CREATE USER 'testuser'@'%' IDENTIFIED BY '123'; CREATE USER 'testuser'@'%';

Create a user that is allowed to log in only from localhost:

CREATE USER 'testuser'@'localhost' IDENTIFIED BY '123';

1	CREATE USER 'testuser'@'localhost' IDENTIFIED BY '123';

Remove a user:

DROP USER 'testuser'@'%';

1	DROP USER 'testuser'@'%';

Show permissions for a user:

SHOW GRANTS FOR 'testuser'@'localhost';

1	SHOW GRANTS FOR 'testuser'@'localhost';

Grant all privileges on a specific database to a user:

CREATE DATABASE testdb;
GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost';

1 2	CREATE DATABASE testdb; GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost';

Grant all privileges on all databasesto a user (optionally with right to grant to other users):

GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost' WITH GRANT OPTION;

1 2	GRANT ALL PRIVILEGES ON . TO 'testuser'@'localhost'; GRANT ALL PRIVILEGES ON . TO 'testuser'@'localhost' WITH GRANT OPTION;

Remove permissions of a user (w.r.t. a specific database):

REVOKE ALL PRIVILEGES FROM 'testuser'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser'@'localhost';

1 2	REVOKE ALL PRIVILEGES FROM 'testuser'@'localhost'; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser'@'localhost';

Remove permissions of a user globally:

REVOKE ALL PRIVILEGES ON testdb.* FROM 'testuser'@'%';

1	REVOKE ALL PRIVILEGES ON testdb.* FROM 'testuser'@'%';

References