Introduction
MySQL is a reliable, quick, and easy-to-use database management system that is used and backed by most of the known organizations, such as Netflix, GitHub, YouTube, Facebook, and many more. MySQL has a root user that has all the authority to access and modify the database.
Database administrators have all the privileges and authority to create and manage users to access the database depending upon the project requirements. In this tutorial, you will learn how to create a new user and grant permissions in MySQL. Let us get started with creating a new user account.
Create a User in MySQL
Every user has some username and password to log in to MySQL and access the database. Every user has a set of permissions, deciding what actions a user can perform over a table or a database. It ensures the security of the database as not all users can access all the tables. It is the primary intent for creating user accounts in MySQL.
Follow the below steps to create a new user in MySQL:
- Firstly, launch the MySQL Terminal Window and then Shell as a root user. Enter the below command to launch shell as the root user:
Sudo mysql –u root –p
- Enter the root password and then click Enter. The prompt should appear as:
mysql>
- Create a new user with the command given below:
CREATE USER <username> IDENTIFIED BY <password>;
In the command above, enter the username and password as per your choice.
As an alternative, you can also create a user while specifying the machine that hosts the database. It can get done in one of the following ways depending upon the type of machine:
- When working on a Machine, having MySQL, enter the below command:
CREATE USER <username>@localhost IDENTIFIED BY <password>;
- When connecting to MySQL machine remotely, enter the below command:
CREATE USER <username>@<ip_address> IDENTIFIED BY <password>
In place of <ip_address>, enter the IP address of the remote machine.
- When creating a user to connect from any machine, enter the below command:
CREATE USER <username>@’%’ IDENTIFIED BY ‘password’;
How to Grant Permissions to Users in MySQL
Now you have created a user account in MySQL, but that user has no access. The user account cannot access any database or a table. You need to specify the permissions to the user account to access all or a specific account. There are different types of permissions that can be given to a user.
The below table contains the list of all permissions:
PermissionDescriptionALL PRIVILEGESTo grant all privileges to the userCREATEUser has permission to create tables and databaseDROPUser has permission to drop tables and databaseDELETEUser has permission to delete rows from the table definedINSERTUser has permission to insert rows into the table definedSELECTUser has permission to read the databaseUPDATEUser has permission to update table
Below is the syntax to grant permissions to the users:
GRANT <permission1>, <permission2> ON <Database_Name>.<Table_Name> TO <DataBase_User>@localhost;
Refer to the below examples to understand it well:
- Grant Create and select permissions to the user accessing machine with MySQL:
GRANT CREATE, SELECT ON * TO <username>@localhost;
- Grant all the permissions to a user to access mentioned database:
GRANT ALL PRIVILEGES ON <database>.* TO <username>@localhost;
- Grant select permissions to a user over a specified table:
GRANT SELECT ON <database>.<table> TO <username>@localhost;
- Grant insert, delete, and select permissions to a user to access a table:
GRANT INSERT, SELECT, DELETE ON <Database>.<Table> TO <username>@localhost;
Revoke permissions from a user
Similarly, the permissions can be revoked from the user using the REVOKE keyword. Below is the syntax to revoke permissions:
REVOKE <permission> ON <database>.<table> FROM <username>@localhost;
Below are few examples to revoke permissions:
- Revoke select and insert permissions from a user for a table:
REVOKE SELECT, INSERT ON <database>.<table> FROM <username>@localhost;
- Revoke all permissions from a user over all tables:
REVOKE ALL PRIVILEGES ON <database>.<table> FROM <username>@localhost;
Display User Permissions:
You can also check the permissions granted to a given user. Below is the syntax to check the permissions:
SHOW GRANTS FOR <username>@localhost;
The above command displays all the permissions a user has.
Remove a user account from MySQL
You can also delete the user account using the DROP command. Below is the syntax for that:
DROP USER <username>@localhost;
Conclusion
The database is a crucial part of any website that stores all the data. Its security is of the utmost importance. To access and manage the data in the database, a reliable and efficient database management system is required. MySQL is one such database, which is easy to use, secure, and quite efficient. This tutorial explained the basic commands to create new users in MySQL and grant access to the users.
For more such informational articles, you can check out E2E Cloud. E2E Cloud is one of the largest cloud providers in India that helps in the one-click deployment of cloud instances and provides solutions as per the customer requirements at economical charges.
