Grid Guides

Explore How System On Grid Can Work For You

Grid Guide Topics

How to Create a New User and Grant Permission in MySQL

Table Of Contents


    ## Table of Contents

    – [Introduction](#introduction
    – [Prerequisites](#prerequisites
    – [Creating a New User](#creating-a-new-user
    – [Granting Different User Permissions](#granting-different-user-permissions
    – [Conclusion](#conclusion

    ## Introduction

    [Mysql](https://www.mysql.com/ 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](https://systemongrid.com/guides/-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.