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'
Leave a Reply