02
Jul 2021How To Create, Remove and Grant Permission to User in MySQL
Description
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.