How To Create, Remove and Grant Permission to User in MySQL

Before we begin you may want to look here at how to set up MySQL Server. If you had not done so. 

Introduction

MySQL is an open-source, relational database management system typically running at 3306 port as default. Since MySQL is open source, it allows programmers to customize MySQL to fit according to their requirements.

It supports standard structured query language. It is used by a large number of web applications.

Book MySQL tutors starting from Rs. 250/hr or monthly basis

How to Create a User

After Setup, we have a MySQL Server running with the root user with full privileges to all the databases.

Open terminal and type mysql -u root -p to enter MySQL CLI(Command Line Interference) followed by your password.

Now Type CREATE USER "newuser"@"localhost" IDENTIFIED BY "password"; to create a user

The User newuser currently has no permission to access any database so we use the following command to give permission 

GRANT ALL PRIVILEGES ON * . * TO "newuser"@"localhost";

The First * means the database and the second * table respectively, e.g. if we have a database named tempdb with table name records, we use the command to grand privileges to only this particular database and table.

Now to Implement those Changes we use FLUSH PRIVILEGES; 

Changes are now in effect

Grant Different types of Permissions

Here is a list of the most common permissions that you can use.

a) ALL PRIVILEGES- allow full access to a database 

b) CREATE- creates new tables or databases

c) DROP- Deletes tables or databases

d) DELETE- Delete rows from tables

e) INSERT- Insert rows into tables

f) SELECT- read through databases

g) UPDATE- update table rows

h) GRANT OPTION- grant or remove other users privileges

How to Grant and Revoke Privileges

To Grant Privileges, we use the syntax GRANT type_of_permission ON db_name.table_name TO "username"@"localhost";

Note! You must use flush privileges after every command for the changes to take place.

To revoke use REVOKE type_of_permission ON db_name.table_name FROM "username"@"localhost";

To view all permissions of the user use SHOW GRANTS FOR "username"@"localhost";

To Delete a user use DROP USER "username"@"localhost";

For Installation refer to our blog How to Install MySQL.