MySQL is one of the most popular and recognizable databases in today’s world. Therefore, anyone working for enterprise data or IT should have basic knowledge of MYSQL. Oracle is the developer of the MYSQL database management system. It uses structured query language. Moreover, It is the 3rd layer of LAMP STACK. Through this guide, you’ll get an idea of how you can create a user MySQL and how to grant privileges needed to perform the different tasks.
Table of Contents:
Prerequisites to Follow Before You Create a User on MySQL:
There are some prerequisites that you need to follow before you dive into MySQL creating a user.
First, before you create a user MySQL, you must have access to MySQL creating user database.
This guide assumes that the user can have it installed and running on Ubuntu 20.04. This step is a must no matter how you access the database.
If you do not have MySQL creating user database access, make sure to visit our guide on the way to get MySQL and access create user MySQL command line. The method of creating users in MYSQL and granting privileges is the same through all the operating systems.
Method to create user MySQL to Grant Privileges
A username and password are provided to you when you begin working on MySQL. This username and password will give you access to the root as well as complete control of tables and databases. However, there may be times when you would have to provide access to your database to somebody else.
For example, when you hire experts when updating your database, you must give them access. But you don’t want them to change or delete any critical information. So how do we stop that from happening? This will help you track all the user’s activity, and they won’t be able to delete or change any number or entity.
In the given section of the article, we will tell you how you can create a user MySQL. Grant Privileges to any person. You can learn about the user privileges by giving access control to non-main or non-working users. Also, it is not advised.
How to Build a Secure MySQL Account?
To create a user MySQL account, go through these steps:
- go to create user MySQL command line and enter MySQL server: MySQL
the command line prompt will show mysql>.
- Since you are to create a user MySQL account, write the following on creating the user MySQL command line.
CREATE USER ‘new_user1’@Local User and new_user1 is a new entities that we give to our account, whereas the password section is used to set a password.
- Users can undo these with a unique username and password.
- Now, to grant privileges from the database. type the following command;
“GRANT PRIVILEGES WITH *. * ‘new_user1’@’localhost’;
- This command shown above will also help you create a super user who would have access same access as the root user.
- If you want these privileges to effect immediately, type the query ” flush privileges.”
Now your local user account can access the database like the main user.
How to Give Privileges To Each User On a MySQL?
This section of the article explains how you can Grant Privileges uniquely to each working user. At the time of giving database and table names, use a “.” with no space to separate them. This gives the main user control over specific data.
Some of the standard privileges are as follows:
- CREATE — the users have access to build a table or a unique database
- INSERT — the users have access to enter new data or numbers in the table.
- SELECT — the users have access to get the rollover data.
- UPDATE — the users have access to change data in the query or database
- DELETE — the users have access to remove the entries found on the table
- DROP — the users have access to delete entries from Database tables
To grant the rest of the privileges to your new user, simply type PERMISSION_TYPE along by adding the keyword or a unique phrase.
- Use a comma to separate multiple privileges.
- For example, if you wish to assign Select and Create to the MYSQL account, then.
- Now add it in the query TO ‘new_user1’@’localhost’;
- This above command will give access to new_user1 of create and select on the database of books and authors
- There may be times you wish to take back the privileges in that case. Use the revoke command.
- for example
- REVOKE CREATE, SELECT
- ON books. Authors (FROM ‘new_user1’@’localhost’)
Why Explore More About MySQL Database First?
Image by iStockPhoto
To explore MySQL and how MySQL creating user works, you first need to have an idea about a database. A database relates to a storage place to store data for making up a website and apps.
It stores data like account details, passwords, emails, etc.
However, the data will be managed in order. That is why we use a database storage track. Also, the systems connect directly with any database. It will let developers store, structure, delete, and modify any piece of data. MySQL is a famous name for database tracking systems. It is very easy to use and provides community support. Handling user access and rights requires a lot of manual effort.
The database needs to be managed very efficiently, especially when you have different MYSQL models. Managing these databases can be hard. Therefore, This manual focuses on a self-managed MySQL setup where you can access the database at the root level. While many commands in self-managed MySQL work in hosted environments like AWS RDS or cloud SQL. However, there can be restrictions on the commands you can run and access.
A database is a form of vital accessory of any website. It can store several data and apps that need to be secure. The database is handled by the management and tracking system, which can help in communicating with the website and application more efficiently.
MYSQL is very reliable and easy to use; therefore, it is recommended by a lot of people.
With this guideline, you have access to how to add new users. Grant and revoke their privilege’s in the database. Now you can explore by setting different privileges. Since you know the basics, you can also learn some higher-level configurations!