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'@'%';
Create a user that is allowed to log in only from localhost:
CREATE USER 'testuser'@'localhost' IDENTIFIED BY '123';
Remove a user:
DROP USER 'testuser'@'%';
Show permissions for a user:
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';
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;
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';
Remove permissions of a user globally:
REVOKE ALL PRIVILEGES ON testdb.* FROM 'testuser'@'%';