Grant Privileges to MySQL User

Assign privileges and roles to MySQL user accounts and roles using MySQL GRANT statement. This is to be able for a MySQL user to perform MySQL basic operations such as add, edit, delete, and select records from a MySQL Database, a permission should be granted to authorize access to a MySQL Database to perform the said operations.

Requirements:

• MySQL
• MySQL User with Password
• MySQL Host
• MySQL Database
• Access MySQL Database From Terminal
• Create MySQL Database User

How To Grant Privileges to MySQL Database User.

Make sure you can access MySQL and have created a MySQL user already – Access MySQL Database From Terminal, Create MySQL Database User

The objective is to grant ALL PRIVILEGES to a user named USERNAME to the database named employees, and USERNAME is accessing employees from localhost.

GRANT
    ALL PRIVILEGES
ON
    employees.*
TO
    'USERNAME'@'localhost'
;

Where.

• ALL PRIVILEGES are the privileges that will be given to the user.
• employees is the MySQL DATABASE where the user will have the privileges to perform MySQL operations.
• USERNAME is the name of the user who will be given privileges.
• HOST is the server where the user will be allowed to logging in from.

Result.

Successful attempt will show message like below.

Query OK, 0 rows affected (0.095 sec)

Try to grant a SELECT privilege only.

GRANT SELECT ON employees.* TO 'USERNAME'@'localhost';

An error like below will appear if other privilege is attempted other than select.

ERROR 1142 (42000): INSERT command denied to user 'USERNAME'@'localhost' for table 'names'

References:

• Connecting to the MySQL Server
• MySQL GRANT Statement
• MySQL CREATE USER Statement
• Access MySQL Database From Terminal
• Create MySQL Database User