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

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