MySQL – Create, Delete User Accounts and Grant Privileges (create/drop user, grant, show grants)

Find code and diagrams at: https://www.EliTheComputerGuy.com

MySQL has its own user accounts and security policies. Remember that most times “hackers” are only able to compromise a database using the privileges assigned to the compromised account.

CREATE USER ‘user’@’localhost’ IDENTIFIED BY ‘password’; – Create user with Password
DROP USER ‘user’@’localhost’ – Delete user
GRANT ‘permission’ on DATABASE.TABLE to ‘user@localhost’; – Grant privileges to user account
GRANT ALL PRIVILEGES ON *.* TO ‘user’@’localhost’; – Gives all permissions to ALL tables on ALL databases to user
GRANT ALL PRIVILEGES ON *.* TO ‘user’@’localhost’ WITH GRANT OPTION; – “with grant option” gives ability for account to give privileges to other accounts.
GRANT ‘permission'(column1,column2) on DATABASE.TABLE to user@localhost; – Grants privilege to columns in a table.
show grants; – shows privilege of current user
show grant for user; shows privilege for a specific user
select user, host from mysql.user; – display users and hosts for MySQL Server
REVOKE ‘privilege’ on DATABASE.TABLE FROM user@localhost;
mysql -u username -p – Login to MySQL from Linux
Privileges:

ALL PRIVILEGES – grants all privileges to the MySQL user
CREATE – allows the user to create databases and tables
DROP – allows the user to drop databases and tables
DELETE – allows the user to delete rows from specific MySQL table
INSERT – allows the user to insert rows into specific MySQL table
SELECT – allows the user to read the database
UPDATE – allows the user to update table rows
Full list of Privileges: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html