GNU/Linux, Open Source, Cloud Computing, DevOps and more...

ᐈ How to create a user in MySQL/MariaDB and grant permissions on a specific database

10 comments

Logos de MariaDB y MySQLCreating a MySQL or MariaDB user and granting permissions to him to access a specific database and be able to write data on it is a very usual task that is necessary to perform each time you install a new application based on any of these database engines, like web applications running on top of LAMP stack. Whether it is a simple WordPress, or a more complex application tailor made, one way or another you will always have to complete these steps at some point before its deployment.

These commands are valid to be executed both from the generic MySQL console or command line interface and from any other tool that allows to execute SQL commands against a MySQL instance such as phpMyAdmin or MySQL Workbench. They can also be run in MariaDB with identical result.


Error: Your Requested widget " ai_widget-6" is not in the widget list.
  • [do_widget_area above-nav-left]
    • [do_widget_area above-nav-right]
      • [do_widget_area footer-1]
        • [do_widget id="wpp-4"]
      • [do_widget_area footer-2]
        • [do_widget id="recent-posts-4"]
      • [do_widget_area footer-3]
        • [do_widget id="recent-comments-3"]
      • [do_widget_area footer-4]
        • [do_widget id="archives-4"]
      • [do_widget_area logo-bar]
        • [do_widget id="oxywidgetwpml-3"]
      • [do_widget_area menu-bar]
        • [do_widget id="search-3"]
      • [do_widget_area sidebar]
        • [do_widget id="search-4"]
        • [do_widget id="ai_widget-2"]
        • [do_widget id="categories-5"]
        • [do_widget id="ai_widget-3"]
        • [do_widget id="ai_widget-4"]
        • [do_widget id="ai_widget-5"]
      • [do_widget_area sub-footer-1]
        • [do_widget id="text-4"]
      • [do_widget_area sub-footer-2]
        • [do_widget_area sub-footer-3]
          • [do_widget_area sub-footer-4]
            • [do_widget_area upper-footer-1]
              • [do_widget id="search-2"]
              • [do_widget id="recent-posts-2"]
              • [do_widget id="recent-comments-2"]
              • [do_widget id="archives-2"]
              • [do_widget id="categories-2"]
              • [do_widget id="meta-2"]
            • [do_widget_area upper-footer-2]
              • [do_widget_area upper-footer-3]
                • [do_widget_area upper-footer-4]
                  • [do_widget_area widgets_for_shortcodes]
                    • [do_widget id="search-5"]
                    • [do_widget id="ai_widget-6"]
                  • [do_widget_area wp_inactive_widgets]
                    • [do_widget id="wpp-2"]
                    • [do_widget id="text-1"]
                    • [do_widget id="recent-posts-3"]
                    • [do_widget id="categories-3"]
                    • [do_widget id="archives-3"]
                    • [do_widget id="icl_lang_sel_widget-3"]

                  1. Database creation

                  mysql> CREATE DATABASE `mydb`;

                  2. User creation

                  mysql> CREATE USER 'myuser'@localhost IDENTIFIED BY 'mypassword';

                  3. Grant permissions to access and use the MySQL server

                  Only allow access from localhost (this is the most secure and common configuration you will use for a web application):

                  mysql> GRANT USAGE ON *.* TO 'myuser'@localhost IDENTIFIED BY 'mypassword';

                  To allow access to MySQL server from any other computer on the network:

                  mysql> GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword';

                  In MySQL 8 or higher we will not add the IDENTIFIED BY ‘mipassword’ part.

                  4. Grant all privileges to a user on a specific database

                  MySQL 5.7 and earlier versions:

                  mysql> GRANT ALL privileges ON `mydb`.* TO 'myuser'@localhost IDENTIFIED BY 'mypassword';

                  MySQL 8 and higher versions:

                  mysql> GRANT ALL ON `mydb`.* TO 'myuser'@localhost;

                  As in the previous command, if you want the user to work with the database from any location you will have to replace localhost with ‘%’.

                  5. Apply changes made

                  To be effective the new assigned permissions you must finish with the following command:

                  mysql> FLUSH PRIVILEGES;

                  6. Verify your new user has the right permissions

                  mysql> SHOW GRANTS FOR 'myuser'@localhost;     
                  +--------------------------------------------------------------+ 
                  | Grants for myuser@localhost                                  | 
                  +--------------------------------------------------------------+ 
                  | GRANT USAGE ON *.* TO 'myuser'@'localhost'                   | 
                  | GRANT ALL PRIVILEGES ON `mydb`.* TO 'myuser'@'localhost'     | 
                  +--------------------------------------------------------------+ 
                  2 rows in set (0,00 sec)

                  If you made a mistake at some point you can undo all the steps above by executing the following commands, taking the precaution of replacing localhost with ‘%’ if you also changed it in the previous commands:

                  DROP USER myuser@localhost;
                  DROP DATABASE mydb;

                  Finally, here is a very simple and small Linux script in Bash that will help you to do all this in a much faster and direct way. Simply change your user and database names, and that’s it:

                  #! /bin/bash
                  
                  newUser='testuser'
                  newDbPassword='testpwd'
                  newDb='testdb'
                  host=localhost
                  #host='%'
                  
                  # MySQL 5.7 and earlier versions 
                  #commands="CREATE DATABASE \`${newDb}\`;CREATE USER '${newUser}'@'${host}' IDENTIFIED BY '${newDbPassword}';GRANT USAGE ON *.* TO '${newUser}'@'${host}' IDENTIFIED BY '${newDbPassword}';GRANT ALL privileges ON \`${newDb}\`.*
                  TO '${newUser}'@'${host}' IDENTIFIED BY '${newDbPassword}';FLUSH PRIVILEGES;"
                  
                  # MySQL 8 and higher versions
                  commands="CREATE DATABASE \`${newDb}\`;CREATE USER '${newUser}'@'${host}' IDENTIFIED BY '${newDbPassword}';GRANT USAGE ON *.* TO '${newUser}'@'${host}';GRANT ALL ON \`${newDb}\`.* TO '${newUser}'@'${host}';FLUSH PRIVILEGES;"
                  
                  echo "${commands}" | /usr/bin/mysql -u root -p
                   

                  About the author

                  Daniel López Azaña
                  Freelance AWS Cloud Solution Architect & Linux Sysadmin

                  Entrepreneur, a generator of ideas and restless mind. Passionate about new technologies, especially Linux systems and Open Source Software. I also like to write about Technology News, Cloud Computing, AWS, DevOps, DevSecOps, System Security, Web Development and Programming, SEO, Science, Innovation, Entrepreneurship, etc.

                  Danielᐈ How to create a user in MySQL/MariaDB and grant permissions on a specific database

                  Related Posts

                  10 comments

                  Join the conversation
                  • Vinicius - 25/02/2018 reply

                    Excellent article! Thank you very much

                  • Sergio Pérez - 29/06/2018 reply

                    Thank you very much. It worked perfectly.

                  • lara - 25/03/2019 reply

                    Thank it’s work with my version.

                  • Michael - 03/10/2019 reply

                    Flush privileges is not necessary if you use GRANT. Only if you update the privileges tables direct.

                  • Kejal - 09/06/2020 reply

                    Thanks Bro…. Working

                  • amin - 26/08/2020 reply

                    Thanks man. saved me!

                  • Ollie - 09/10/2020 reply

                    None of my users have grant privileges and phpadmin also shows no privileges. So, I can’t do anything. How do I create a user and grant that user privileges when I don’t have a user with the privileges to do that.

                    Daniel - 27/10/2020 reply

                    You can’t. PhpMyAdmin is just a layer on top of MySQL, and any action you perform will require the appropriate level of privileges in MySQL to be able to do it. If you need to create a new user first you must have a valid user allowed to create other users.

                  • luk - 15/03/2021 reply

                    great but now … how to grant only select for specific user. Only ! select. i can not find the solution 🙁

                    Daniel - 20/03/2021 reply

                    GRANT SELECT ON database.* TO user@’localhost’;

                  Leave a Reply

                  Your email address will not be published.