If you have ever developed a web or application, you may need mysql database. Databases are the virtual storage that is needed to build a web application. They are used to store user information, such as username, password, email address, and so on. Basically, any information you want to store for future use may be stored in databases.
Of course, this data must be stored in order. Therefore the data management system should be used. These systems communicate with the database and allow programmers to structure, store, back up, and edit data.
MySQL is one such database management system. It is one of the most popular systems, because of its ease of use and supported by a large community. In fact, its popularity attracts many large companies such as Facebook or Twitter to a certain extent.
In this tutorial, we will go through the basics of the MySQL Database and show you how to create a user in the MySQL Database. You will also learn how to assign permissions to MySQL users. The command line will be used in this tutorial.
Step 1 – Create MySQL User for MySQL database and assign all permissions
Once you start using the MySQL database, you will be provided with a username and password. These credentials will give you “root access”. A root user has full access to database and tables in the databases.
But over time, you will need to give access to the database to someone else without allowing them full use. For example, companies that hire programmers to maintain the database, but do not want them to have the right to delete or edit sensitive information, will need to provide a non-root user login. This way, the company has control over the programmer who can and can not do with the data.
Creating a new user is also very simple in MySQL. We will tell you how to create a MySQL user and how to assign full privileges to your database. In fact, it’s not a good idea to assign permissions to a user – not root, but it’s the easiest way for beginners. To create a new user, follow these steps:
- Access the command line and fill the MySQL server:
mysql
2. If you want to execute these commands on the VPS server, you will need to make a connection to SSH and run this command.
CREATE USER ‘non-root’@’localhost’ IDENTIFIED BY ‘123’;
With this command, ‘non-root’ is the name of the new user and ‘123’ is the password for this user. You need to replace two values with your own information, in parentheses.
3. Just creating this user is not enough. You need to assign permissions to it. To assign full privileges to the newly created user, execute the following command:
GRANT ALL PRIVILEGES ON * . * TO ‘non-root’@’localhost’;
4. For a change to take place immediately, use the following command:
FLUSH PRIVILEGES;
It is done! Your newly created user has full rights as the user root
Step 2 – Assign certain permissions to the MySQL user
As mentioned above, it is not safe to assign root privileges to a non-root user. Over time, you will need to assign each permission level to each user type. MySQL allows you to assign permissions to a user with a simple command line:
GRANT [permission type] ON [database name].[table name] TO ‘non-root’@’localhost’;
You simply replace ‘permission type’ with the value of the permissions you want to assign to the user. You must also specify the database and table name that you want to grant non-root user permissions. The same as the example on ‘non-roo’ should be replaced instead of the one you specify:
MySQL has a variety of permissions, listed below
CREATE – Allows the user to create databases/tables
SELECT – Allows the user to access the data
INSERT – Allows users to create rows in the table
UPDATE – Allows the user to edit the entries in the table
DELETE – Allows users to delete entries in the table
DROP – Allows the user to completely delete the table/database
To use the options just replace [permission type] with the appropriate keywords. To apply different types of permissions, exclude them with commas. For example, you can assign CREATE and SELECT permissions to a non-root user database with the following command:
GRANT CREATE, SELECT ON * . * TO ‘non-root’@’localhost’;
Of course, you will also need to recover the permissions granted to a user. You can do this with the following command:
REVOKE [permission type] ON [database name]. [Table name] FROM ‘non-root’ @ ‘localhost’;
For example, to recover all permissions for a non-root user:
REVOKE ALL PRIVILEGES ON *.* FROM ‘non-root’@’localhost’;
Finally, you can delete the user:
DROP USER ‘non-root’@‘localhost’;
Remember, in order to execute these permissions you need root privileges. Also, be sure to execute FLUSH PRIVILEGES after making any changes to the permissions.
Conclude
In this tutorial, we learned the basic permissions of MySQL database, and how to create a new MySQL user. In particular, we learn about:
Create new user and assign full privileges
Assign certain permissions to users, and revoke permissions and delete users.
Assigning permissions is a fundamental step in developing a web application, however, it is an effective security measure for managing the database and operating it. Most modern applications depend on the database, which makes MySQL database privilege assign/retrieve the most important.
