{"id":11026,"date":"2025-03-12T11:20:13","date_gmt":"2025-03-12T11:20:13","guid":{"rendered":"https:\/\/mainvps.net\/blog\/?p=11026"},"modified":"2025-03-12T11:20:15","modified_gmt":"2025-03-12T11:20:15","slug":"how-to-create-a-new-user-and-grant-permissions-in-mysql","status":"publish","type":"post","link":"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/","title":{"rendered":"How to Create a New User and Grant Permissions in MySQL"},"content":{"rendered":"\n<p>Managing users and permissions is a vital aspect of MySQL database administration. Whether you&#8217;re setting up a fresh database or maintaining an existing one, understanding how to create new user in MySQL and grant the right permissions ensures both security and functionality. This guide will walk you through the entire process in a simple, human-friendly way, covering all MySQL versions.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-light-blue ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 eztoc-toggle-hide-by-default' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#What_is_MySQL_User_Management\" >What is MySQL User Management?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#Why_is_User_Management_Important\" >Why is User Management Important?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#Prerequisites\" >Prerequisites<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#How_to_Create_a_New_User_in_MySQL\" >How to Create a New User in MySQL<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#Step_1_Log_in_to_MySQL\" >Step 1: Log in to MySQL<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#Step_2_Create_a_New_User_in_MySQL\" >Step 2: Create a New User in MySQL<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#5x_MySQL_and_57\" >5.x MySQL  and 5.7<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#MySQL_8x\" >MySQL 8.x<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#Step_3_Grant_Permissions\" >Step 3: Grant Permissions<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#Grant_All_Permissions_on_a_Database\" >Grant All Permissions on a Database<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#Grant_Specific_Permissions\" >Grant Specific Permissions<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#Global_Permissions_All_Databases\" >Global Permissions (All Databases)<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#Step_4_Apply_Changes\" >Step 4: Apply Changes<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#Step_5_Check_Permissions\" >Step 5: Check Permissions<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-15\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#How_to_Revoke_Permissions\" >How to Revoke Permissions<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-16\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#How_to_Delete_a_MySQL_User\" >How to Delete a MySQL User<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-17\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#Role-Based_Permissions_MySQL_8x\" >Role-Based Permissions (MySQL 8.x)<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-18\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#Creating_a_Role\" >Creating a Role<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-19\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#Assigning_Role_to_User\" >Assigning Role to User<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-20\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#Common_Errors_Troubleshooting\" >Common Errors &amp; Troubleshooting<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-21\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#1_Access_Denied_Error_1045\" >1. Access Denied (Error 1045)<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-22\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#2_Permissions_Not_Applying\" >2. Permissions Not Applying<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-23\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#3_Cannot_Drop_User\" >3. Cannot Drop User<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-24\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#Backup_and_Restore_User_Privileges\" >Backup and Restore User Privileges<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-25\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#Security_Tips\" >Security Tips<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-26\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#Permission_Best_Practices\" >Permission Best Practices<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-27\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#FAQs\" >FAQs<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-28\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#1_What_is_the_difference_between_GRANT_and_REVOKE\" >1. What is the difference between GRANT and REVOKE?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-29\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#2_How_can_I_reset_a_MySQL_user_password\" >2. How can I reset a MySQL user password?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-30\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#3_Can_I_create_a_user_without_a_password\" >3. Can I create a user without a password?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-31\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#4_What_is_WITH_GRANT_OPTION\" >4. What is WITH GRANT OPTION?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-32\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#5_How_can_I_see_all_users_in_MySQL\" >5. How can I see all users in MySQL?<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-33\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"What_is_MySQL_User_Management\"><\/span>What is MySQL User Management?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>MySQL user management is the process of creating, and editing user accounts, as well as deleting them which communicate directly with the MySQL server. Additionally, it involves giving permissions that define what actions users can take on databases.<\/p>\n\n\n\n<p>Imagine MySQL users as employees of an office. Every user has a job and only requires access to a particular system or files based on their job. The proper management of users makes sure that no one gets access to more than they require.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Why_is_User_Management_Important\"><\/span>Why is User Management Important?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Security:<\/strong> Limit access to sensitive data.<\/li>\n\n\n\n<li><strong>Efficiency:<\/strong> Give users only the tools they need.<\/li>\n\n\n\n<li><strong>Accountability:<\/strong> Track who made changes to the database.<\/li>\n\n\n\n<li><strong>Scalability:<\/strong> Easily add or remove users as your team grows.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Prerequisites\"><\/span>Prerequisites<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Before diving in, make sure you have:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>MySQL installed (any version from MySQL 5.x to MySQL 8.x)<\/li>\n\n\n\n<li>Administrative privileges to access the MySQL server<\/li>\n\n\n\n<li>Basic knowledge of MySQL commands<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_to_Create_a_New_User_in_MySQL\"><\/span>How to Create a New User in MySQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Let&#8217;s break it down step by step.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Step_1_Log_in_to_MySQL\"><\/span>Step 1: Log in to MySQL<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>First, access your MySQL server using the root user or an admin user:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql -u root -p<\/code><\/pre>\n\n\n\n<p>Enter your root password when prompted.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"475\" src=\"https:\/\/mainvps.net\/blog\/wp-content\/uploads\/2025\/03\/image-13-1024x475.png\" alt=\"MYsql workbench \" class=\"wp-image-11058\" srcset=\"https:\/\/mainvps.net\/blog\/wp-content\/uploads\/2025\/03\/image-13-1024x475.png 1024w, https:\/\/mainvps.net\/blog\/wp-content\/uploads\/2025\/03\/image-13-300x139.png 300w, https:\/\/mainvps.net\/blog\/wp-content\/uploads\/2025\/03\/image-13-768x356.png 768w, https:\/\/mainvps.net\/blog\/wp-content\/uploads\/2025\/03\/image-13.png 1366w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Step_2_Create_a_New_User_in_MySQL\"><\/span>Step 2: Create a New User in MySQL <span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>The command to create a user varies slightly across different MySQL versions.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"5x_MySQL_and_57\"><\/span>5.x MySQL  and 5.7<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"MySQL_8x\"><\/span>MySQL 8.x<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p>MySQL 8.0 uses a new authentication plugin by default:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE USER 'username'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';<\/code><\/pre>\n\n\n\n<p>\ud83d\udc49 Replace <code>username<\/code> with the desired username and <code>password<\/code> with a secure password.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"468\" src=\"https:\/\/mainvps.net\/blog\/wp-content\/uploads\/2025\/03\/image-14-1024x468.png\" alt=\"create New user in MySQL \" class=\"wp-image-11059\" srcset=\"https:\/\/mainvps.net\/blog\/wp-content\/uploads\/2025\/03\/image-14-1024x468.png 1024w, https:\/\/mainvps.net\/blog\/wp-content\/uploads\/2025\/03\/image-14-300x137.png 300w, https:\/\/mainvps.net\/blog\/wp-content\/uploads\/2025\/03\/image-14-768x351.png 768w, https:\/\/mainvps.net\/blog\/wp-content\/uploads\/2025\/03\/image-14.png 1355w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Step_3_Grant_Permissions\"><\/span>Step 3: Grant Permissions<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Now let&#8217;s give the user access to certain databases.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Grant_All_Permissions_on_a_Database\"><\/span>Grant All Permissions on a Database<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p>If you want to give full access to a specific database:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Grant_Specific_Permissions\"><\/span>Grant Specific Permissions<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p>Only want the user to read and write data?<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'username'@'localhost';<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Global_Permissions_All_Databases\"><\/span>Global Permissions (All Databases)<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p>For full access across all databases:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Step_4_Apply_Changes\"><\/span>Step 4: Apply Changes<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Whenever you grant or revoke permissions, always run this command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>FLUSH PRIVILEGES;<\/code><\/pre>\n\n\n\n<p>This refreshes the permission settings without restarting the server.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Step_5_Check_Permissions\"><\/span>Step 5: Check Permissions<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Want to double-check what permissions a user has?<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SHOW GRANTS FOR 'username'@'localhost';<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_to_Revoke_Permissions\"><\/span>How to Revoke Permissions<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Mistakes happen, and sometimes you&#8217;ll need to revoke permissions:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>REVOKE INSERT, DELETE ON database_name.* FROM 'username'@'localhost';<\/code><\/pre>\n\n\n\n<p>Don&#8217;t forget to run:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>FLUSH PRIVILEGES;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_to_Delete_a_MySQL_User\"><\/span>How to Delete a MySQL User<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>If you no longer need a user, simply delete them:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DROP USER 'username'@'localhost';<\/code><\/pre>\n\n\n\n<p>\u2705 Always clean up unused users to avoid security risks.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Role-Based_Permissions_MySQL_8x\"><\/span>Role-Based Permissions (MySQL 8.x)<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>MySQL 8.x introduces <strong>Roles<\/strong> to simplify permission management. Instead of assigning permissions to each user, you can assign them to a role and then assign the role to users.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Creating_a_Role\"><\/span>Creating a Role<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE ROLE developer;\nGRANT SELECT, INSERT, UPDATE ON database_name.* TO developer;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Assigning_Role_to_User\"><\/span>Assigning Role to User<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>GRANT developer TO 'username'@'localhost';\nSET DEFAULT ROLE developer FOR 'username'@'localhost';<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Common_Errors_Troubleshooting\"><\/span>Common Errors &amp; Troubleshooting<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"1_Access_Denied_Error_1045\"><\/span>1. Access Denied (Error 1045)<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>This usually happens when the username, password, or host is incorrect. Double-check your credentials.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"2_Permissions_Not_Applying\"><\/span>2. Permissions Not Applying<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Always remember to run:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>FLUSH PRIVILEGES;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"3_Cannot_Drop_User\"><\/span>3. Cannot Drop User<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Make sure the user is not currently logged in before trying to delete them.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Backup_and_Restore_User_Privileges\"><\/span>Backup and Restore User Privileges<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>You can export user permissions using the <code>mysqldump<\/code> command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysqldump --user=root --password --all-databases --routines --triggers --events &gt; backup.sql<\/code><\/pre>\n\n\n\n<p>To restore the backup:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql --user=root --password &lt; backup.sql<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Security_Tips\"><\/span>Security Tips<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Enable <strong><a href=\"https:\/\/mainvps.net\/blog\/free-ssl-vs-paid-ssl-guide\/\">SSL<\/a><\/strong> <strong>connections<\/strong>.<\/li>\n\n\n\n<li>Set <strong>password expiration policies<\/strong>.<\/li>\n\n\n\n<li>Restrict access to users based on <strong>IP addresses<\/strong>.<\/li>\n\n\n\n<li>Use <strong>multi-factor authentication<\/strong> if possible.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Permission_Best_Practices\"><\/span>Permission Best Practices<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><th>Environment<\/th><th>Permissions<\/th><\/tr><tr><td>Development<\/td><td>Full access<\/td><\/tr><tr><td>Testing<\/td><td>Limited data manipulation<\/td><\/tr><tr><td>Production<\/td><td>Read-only or minimal write<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"FAQs\"><\/span>FAQs<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"1_What_is_the_difference_between_GRANT_and_REVOKE\"><\/span>1. What is the difference between <code>GRANT<\/code> and <code>REVOKE<\/code>?<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p><strong>GRANT<\/strong> gives permissions, while <strong>REVOKE<\/strong> removes them.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"2_How_can_I_reset_a_MySQL_user_password\"><\/span>2. How can I reset a MySQL user password?<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Use this command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"3_Can_I_create_a_user_without_a_password\"><\/span>3. Can I create a user without a password?<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Technically yes, but it&#8217;s a <strong>terrible idea<\/strong> for security reasons. Always use strong passwords.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"4_What_is_WITH_GRANT_OPTION\"><\/span>4. What is <code>WITH GRANT OPTION<\/code>?<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>It allows the user to grant permissions to other users \u2014 basically making them a mini-admin.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"5_How_can_I_see_all_users_in_MySQL\"><\/span>5. How can I see all users in MySQL?<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT user, host FROM mysql.user;\n<\/code><\/pre>\n\n\n\n<script>{\n  \"@context\": \"https:\/\/schema.org\",\n  \"@type\": \"FAQPage\",\n  \"mainEntity\": [\n    {\n      \"@type\": \"Question\",\n      \"name\": \"What is the difference between GRANT and REVOKE?\",\n      \"acceptedAnswer\": {\n        \"@type\": \"Answer\",\n        \"text\": \"GRANT gives permissions, while REVOKE removes them.\"\n      }\n    },\n    {\n      \"@type\": \"Question\",\n      \"name\": \"How can I reset a MySQL user password?\",\n      \"acceptedAnswer\": {\n        \"@type\": \"Answer\",\n        \"text\": \"Use this command: ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';\"\n      }\n    },\n    {\n      \"@type\": \"Question\",\n      \"name\": \"Can I create a user without a password?\",\n      \"acceptedAnswer\": {\n        \"@type\": \"Answer\",\n        \"text\": \"Technically yes, but it's a terrible idea for security reasons. Always use strong passwords.\"\n      }\n    },\n    {\n      \"@type\": \"Question\",\n      \"name\": \"What is WITH GRANT OPTION?\",\n      \"acceptedAnswer\": {\n        \"@type\": \"Answer\",\n        \"text\": \"It allows the user to grant permissions to other users \u2014 basically making them a mini-admin.\"\n      }\n    },\n    {\n      \"@type\": \"Question\",\n      \"name\": \"How can I see all users in MySQL?\",\n      \"acceptedAnswer\": {\n        \"@type\": \"Answer\",\n        \"text\": \"SELECT user, host FROM mysql.user;\"\n      }\n    }\n  ]\n}\n<\/script>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>MySQL user management isn&#8217;t just about creating users &#8211; it&#8217;s about securing your database while allowing individuals who are qualified to access the information they require. Following this tutorial, you can create users, grant permissions, and ensure that your MySQL database is protected and organized.<\/p>\n\n\n\n<p>If you&#8217;re using MySQL 5.x or the most recent <a href=\"https:\/\/dev.mysql.com\/doc\/relnotes\/mysql\/8.0\/en\/\" target=\"_blank\" rel=\"noopener\">MySQL 8.x<\/a> These steps will assist you in maintaining the stability of your system.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Managing users and permissions is a vital aspect of MySQL database administration. Whether you&#8217;re setting up a fresh database or maintaining an existing one, understanding how <a class=\"read-more-link\" href=\"https:\/\/mainvps.net\/blog\/how-to-create-a-new-user-and-grant-permissions-in-mysql\/\">Read More<\/a><\/p>\n","protected":false},"author":4,"featured_media":11062,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[200],"tags":[220],"class_list":["post-11026","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-website-management","tag-mysql"],"_links":{"self":[{"href":"https:\/\/mainvps.net\/blog\/wp-json\/wp\/v2\/posts\/11026","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mainvps.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mainvps.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mainvps.net\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/mainvps.net\/blog\/wp-json\/wp\/v2\/comments?post=11026"}],"version-history":[{"count":3,"href":"https:\/\/mainvps.net\/blog\/wp-json\/wp\/v2\/posts\/11026\/revisions"}],"predecessor-version":[{"id":11060,"href":"https:\/\/mainvps.net\/blog\/wp-json\/wp\/v2\/posts\/11026\/revisions\/11060"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/mainvps.net\/blog\/wp-json\/wp\/v2\/media\/11062"}],"wp:attachment":[{"href":"https:\/\/mainvps.net\/blog\/wp-json\/wp\/v2\/media?parent=11026"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mainvps.net\/blog\/wp-json\/wp\/v2\/categories?post=11026"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mainvps.net\/blog\/wp-json\/wp\/v2\/tags?post=11026"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}