Close Menu
    Facebook X (Twitter) Instagram
    TecAdmin
    • Home
    • FeedBack
    • Submit Article
    • About Us
    Facebook X (Twitter) Instagram
    TecAdmin
    You are at:Home»Databases»MariaDB»How To Create a New MySQL User with Grant Permissions

    How To Create a New MySQL User with Grant Permissions

    By RahulJanuary 28, 20213 Mins Read

    MySQL is a relational database management system used for storing data in tabular format. It provides high flexibility for the user account and grant permissions.

    This tutorial will provide you a short overview to create MySQL user account and grant permissions on database.

    Create A MySQL User with Permissions

    Here we are running all queries as root account having full privileges to all databases. You can create MySQL user account with required privileges.

    1. Let’s create a new MySQL user within MySQL shell:
      mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'pa$$word';
      

      Here newuser is the username to be created. The localhost is defines that this user is only accessible from localhost only. To connect MySQL from any remote host, change the localhost with remote system ip address. You can also use % to allow any remote host to connect with this account.

    2. Now, assign the required privileges to the newly created MySQL user.
      mysql> GRANT ALL ON *.* TO 'newuser'@'localhost';
      

      Read next step to know more about various grant option for mysql account.

    3. After adding or modifying any privileges, make sur to reload the privilege’s to apply changes in running MySQL instance.
      mysql> FLUSH PRIVILEGES;
      

    Grant MySQL User Permissions

    Here is the frequently used options with assigning privileges to user in MySQL.

    • ALL – This will allow a mysql user the complete access to the specified database or full access to all databases
    • SELECT – Allow user to select data from tables
    • INSERT – Allow user to insert data into tables
    • UPDATE – Allow user to update data in tables
    • DELETE – Allow user to delete rows in tables
    • CREATE – Allow user to create new database and tables
    • DROP – Allow user to delete databases and tables
    • ALTER – Allow user to alter the structure of a table or to create triggers on a table.
    • GRANT OPTION -Allow user to grant or remove other user privileges

    Use the following option to grant all privileges on specific database to newuse@localhost.

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

    Use the following option to grant specific permissions like SELECT,INSERT,DELETE on a specific database to newuse@localhost.

    mysql> GRANT SELECT,INSERT,DELETE ON dbname.* TO 'newuser'@'localhost';
    

    All all priviledges to user@localhsot on specific database including permissions to grant other users.

    mysql> GRANT ALL ON dbname.* TO 'newuser'@'localhost' WITH GRANT OPTION;
    

    You can also view the allowed permission to a user in MySQL.

    mysql> SHOW GRANTS FOR 'newuser'@'localhost';
    

    Login to MySQL Shell

    Login to MySQL shell with newly created user and password.

    mysql -u newuser -p
    
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4817
    Server version: 5.7.32-0ubuntu0.16.04.1 (Ubuntu)
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql>
    

    create user grant mariadb MySQL privilege user
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp

    Related Posts

    Working with Python FastAPI and MySQL

    Resolving MySQL ERROR 1041 (HY000): Out of Memory

    A Comprehensive Guide to Optimizing MySQL Table

    View 4 Comments

    4 Comments

    1. Trevor on August 31, 2022 7:39 am

      Thanks this was very helpful for me.

      Reply
    2. Peter on May 8, 2015 4:34 am

      I am trying this section, but when i put in the first GRANT ALL ON command, it goes to a subsection, which i believe is an input request. i have no idea what it wants, ive tried password and username but nope. It looks like

      MariaDB [(None)] > GRANT ALL ON owncloud.* to ‘owncloud’@’localhost’ IDENTIFIED BY ‘Secret_Password’
      -> //this is blank, but if i type here, it just keeps it an makes a new line, like this
      ->

      Im guessing this is a simple case of not knowing Ubuntu very well, or something similar. Sorry, for the time 🙂

      Reply
      • Peter on May 8, 2015 4:36 am

        Nevermind haha. I had previously done it and it had somewhat worked, i had just gone to test it and thought it hadnt. I used the correct internal IP to access the server and it works perfect. Thanks if you read my comment anyway, have fun 🙂

        Reply
      • Rahul on May 10, 2015 8:08 am

        Hi Peter,

        Command looks fine. Just make sure you are using correct single quotes (‘) and semi colon (;) at end of query.

        Reply

    Leave A Reply Cancel Reply

    Advertisement
    Recent Posts
    • How to Change Port in Next.Js
    • Ubuntu 24.04 LTS: The Future of Open-Source Excellence
    • How to Execute Linux Commands in Python
    • Creating MySQL User with GRANT OPTION
    • Where to find crontab (cron) logs in Ubuntu & Debian
    Facebook X (Twitter) Instagram Pinterest
    © 2023 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

    Type above and press Enter to search. Press Esc to cancel.