Some useful MySQL commands

Posted by Admin on 2023-05-25 10:53:26:

Grant privileges* on table:

To grant SELECT, INSERT, UPDATE, and DELETE privileges on table name: data_table to a user name jdoe, you would run the following GRANT statement:

GRANT SELECT, INSERT, UPDATE, DELETE ON data_table.* TO 'jdoe'@'localhost';

*Privileges can be any of: SELECT, INSERT, UPDATE, DELETE, INDEX, CREATE, ALTER, DROP, GRANT, ALL

Revoke privileges on table:

REVOKE DELETE, UPDATE ON data_table FROM 'jdoe'@'localhost';

If you want to know what privileges a user has use the 'SHOW GRANTS' command:

SHOW GRANTS FOR 'jdoe'@'localhost';

To create a new user:

CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'some_strong_password';
GRANT SELECT, SHOW VIEW ON data_table.* TO 'readonly_user'@'localhost';
flush privileges;

To delete a user:

DROP USER ‘jdoe’@’localhost’;

Change a password for a user:

SET PASSWORD FOR 'jdoe'@'localhost' = PASSWORD('foobar');