Users Management
Create user
CREATE USER 'user'@'host' IDENTIFIED BY 'password';Use '%' to match any host
Delete user
DROP USER 'user'@'host';Update user password
ALTER USER 'user'@'host' IDENTIFIED BY 'password';Update user host configuration
UPDATE mysql.user SET Host='${new_host}' WHERE Host='${old_host}' AND User='user';
UPDATE mysql.db SET Host='${new_host}' WHERE Host='${old_host}' AND User='user';
FLUSH PRIVILEGES;Show connected user
SHOW PROCESSLIST;Privileges
| Privilege | Description |
|---|---|
| SELECT | Ability to perform SELECT statements on the table. |
| INSERT | Ability to perform INSERT statements on the table. |
| UPDATE | Ability to perform UPDATE statements on the table. |
| DELETE | Ability to perform DELETE statements on the table. |
| INDEX | Ability to create an index on an existing table. |
| CREATE | Ability to perform CREATE TABLE statements. |
| ALTER | Ability to perform ALTER TABLE statements to change the table definition. |
| DROP | Ability to perform DROP TABLE statements. |
| GRANT | OPTION Allows you to grant the privileges that you possess to other users. |
| ALL | Grants all permissions except GRANT OPTION. |
Add
GRANT ${PRIVILEGES} ON `db`.`table` TO `user`@`host`;
FLUSH PRIVILEGES;Remove
REVOKE ${PRIVILEGES} ON `db`.`table` FROM `user`@`host`;
FLUSH PRIVILEGES;Kill all process for specific user
List process
SHOW PROCESSLIST WHERE user='${USER}';Get a set off kill command to run
SELECT CONCAT('KILL ',id,';') AS run_this FROM information_schema.processlist WHERE user='${USER}' INTO OUTFILE '/var/lib/mysql-files/user.txt';Kill all connection
SOURCE /var/lib/mysql-files/user.txt;