Grid Guides

Explore How System On Grid Can Work For You

How to Create a New User and Grant Permission in MySQL


Table of Contents

Introduction

Mysql is open source and database relational management system. The users will store, organize, retrieve the data. It is also based on SQL(Structured query language). It is also used in various and wide range of web applications.

Prerequisites

You should have covered all the necessary editings that need to do in MySQL as the root user and also given access to all the databases which we covered in our guide, A Basic MySQL Tutorial.

Creating a New User

Mysql will organize the data in the database. There are different ways to create the user with custom permissions.

Start of a new user within Mysql shell

mysql>  CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; 

Here, the new user will not have permissions to do with the database. If the user will try to login with the help of the password then they will not be able to reach the Mysql shell. The new user will be the user account that will be created.

The user is provided with the access to the information needed.

mysql> GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost'; 

Here, The command helps the user to read, edit, execute and perform the tasks over all the databases and tables.

Granting of the new user with full root access to the database. It helps some MySQL concepts, but it is impractical of most of the use cases and may put the database at high risk.

The permissions that are required for the set up of the new user and make sure to reload the databases.

mysql>  FLUSH PRIVILEGES;

Granting Different User Permissions

Here are some flexible different permissions for the user

ALL PRIVILEGES

The main function of the MySQL privilege system is to authenticate a user to connect with the host.

CREATE

It allows to create new databases or tables

DROP

It allows to delete new databases or tables

DELETE

It allows deleting rows from tables

INSERT

It allows inserting rows into tables

SELECT

It allows to use this command to read through databases

UPDATE

It allows updating the tables

GRANT OPTION

It allows to grant or remove other privileges

Here is the Permission or grant to a specific user and framework follows ;

mysql> GRANT type_of_permission ON database_name.table_name TO ‘username’@'localhost’;

It is made sure that asterisk (*) is kept in the place of database name or table name for the access of any of the database or table name. Flush privileges command is used to update or change the permission.

Granting of revoking permission;

mysql>  REVOKE type_of_permission ON database_name.table_name FROM ‘username’@‘localhost’;

The syntax is required to be changed FROM, replace of TO is done over here.

Granting of the current permissions is done by the following:

 SHOW GRANTS username;

DROP is used to delete a user;

mysql> DROP USER ‘username’@‘localhost’;

Logging out of the user;

 mysql> quit

Logging back into this terminal again;

$  mysql -u [username] -p

Conclusion

we conclude that adding new users and also granting them permissions in MySQL. we can experiment and learn the various settings of the database and also higher level configurations.