{"id":30211,"date":"2026-04-16T13:55:28","date_gmt":"2026-04-16T11:55:28","guid":{"rendered":"https:\/\/contabo.com\/blog\/?p=30211"},"modified":"2026-04-24T14:02:06","modified_gmt":"2026-04-24T12:02:06","slug":"mysql-user-and-grant-privileges","status":"publish","type":"post","link":"https:\/\/contabo.com\/blog\/mysql-user-and-grant-privileges\/","title":{"rendered":"MySQL Create User and Grant Privileges: Full Guide"},"content":{"rendered":"\n<p>Running your entire MySQL instance under the root account is the database equivalent of leaving your server&#8217;s front door unlocked. Here&#8217;s how to create users with the right privileges and nothing more.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-prerequisites-mysql-access-and-setup\">Prerequisites: MySQL Access and Setup<\/h2>\n\n\n\n<p>You need an existing MySQL installation and the ability to log in as root or a user with GRANT OPTION privileges:<\/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. From here you can create users, assign privileges, and manage the entire access control layer.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-create-a-mysql-user\">How to Create a MySQL User<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-create-user-statement-syntax\">CREATE USER Statement Syntax<\/h3>\n\n\n\n<p>The full syntax for creating a MySQL user:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE USER 'username'@'host' IDENTIFIED BY 'password';<\/code><\/pre>\n\n\n\n<p>The host portion controls where connections can originate. localhost restricts to local connections only. % allows connections from any host. You can also specify an IP address or subnet.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-creating-a-user-with-a-password\">Creating a User with a Password<\/h3>\n\n\n\n<p>Create a local user named appuser:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongPassword123!';<\/code><\/pre>\n\n\n\n<p>On MySQL 8.0+, the default authentication plugin is caching_sha2_password. Older clients may need mysql_native_password instead:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE USER 'appuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'StrongPassword123!';<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-creating-a-user-for-remote-access\">Creating a User for Remote Access<\/h3>\n\n\n\n<p>Allow connections from any host by using % as the host value:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'RemotePassword456!';<\/code><\/pre>\n\n\n\n<p>For tighter control, restrict to a specific IP:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE USER 'remoteuser'@'192.168.1.100' IDENTIFIED BY 'RemotePassword456!';<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-grant-privileges-in-mysql\">How to Grant Privileges in MySQL<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-granting-all-privileges-on-a-database\">Granting All Privileges on a Database<\/h3>\n\n\n\n<p>Give a user full access to a specific database:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>GRANT ALL PRIVILEGES ON mydatabase.* TO 'appuser'@'localhost';<\/code><\/pre>\n\n\n\n<p>The *.* syntax grants privileges across all databases, which you should only use for administrative accounts.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-granting-specific-privileges\">Granting Specific Privileges<\/h3>\n\n\n\n<p>Least-privilege is the right approach. A read-only reporting user needs SELECT only:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>GRANT SELECT ON mydatabase.* TO 'readonly_user'@'localhost';<\/code><\/pre>\n\n\n\n<p>An application that only reads and writes records:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'appuser'@'localhost';<\/code><\/pre>\n\n\n\n<p>Available privileges include: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, EXECUTE, and more.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-applying-privilege-changes-with-flush-privileges\">Applying Privilege Changes with FLUSH PRIVILEGES<\/h3>\n\n\n\n<p>After granting privileges, flush the grant tables to ensure the changes take immediate effect:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>FLUSH PRIVILEGES;<\/code><\/pre>\n\n\n\n<p>MySQL 8.0 applies GRANT and REVOKE automatically, but running FLUSH PRIVILEGES is still good practice when directly modifying system tables.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-view-granted-privileges\">How to View Granted Privileges<\/h2>\n\n\n\n<p>Check what privileges a user currently has:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SHOW GRANTS FOR 'appuser'@'localhost';<\/code><\/pre>\n\n\n\n<p>For the currently logged-in user:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SHOW GRANTS FOR CURRENT_USER();<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-revoke-and-drop-mysql-users\">How to Revoke and Drop MySQL Users<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-revoking-specific-privileges\">Revoking Specific Privileges<\/h3>\n\n\n\n<p>Remove write access while keeping read access:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>REVOKE INSERT, UPDATE, DELETE ON mydatabase.* FROM 'appuser'@'localhost';<\/code><\/pre>\n\n\n\n<p>Then flush:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>FLUSH PRIVILEGES;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-deleting-a-mysql-user-account\">Deleting a MySQL User Account<\/h3>\n\n\n\n<p>Remove a user entirely:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DROP USER 'appuser'@'localhost';<\/code><\/pre>\n\n\n\n<p>This removes the user account and all associated privileges. Verify the user is gone:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT User, Host FROM mysql.user;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-mysql-user-security-best-practices\">MySQL User Security Best Practices<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Never use the root account for application connections. Create dedicated users per application.<\/li>\n\n\n\n<li>Grant only the privileges the application actually needs. A CMS needs SELECT, INSERT, UPDATE, DELETE. It doesn&#8217;t need DROP or ALTER.<\/li>\n\n\n\n<li>Use strong, unique passwords. Store them in environment variables or a secrets manager, not in code.<\/li>\n\n\n\n<li>Restrict host access. Use localhost or a specific IP rather than % wherever possible.<\/li>\n\n\n\n<li>Audit your user list periodically: SELECT User, Host FROM mysql.user;<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-faq-mysql-create-user\">FAQ: MySQL Create User<\/h2>\n\n\n\n<div class=\"schema-faq wp-block-yoast-faq-block\"><div class=\"schema-faq-section\" id=\"faq-question-1777031969648\"><strong class=\"schema-faq-question\">How do I create a new user in MySQL?<\/strong> <p class=\"schema-faq-answer\">Run: CREATE USER &#8216;username&#8217;@&#8217;localhost&#8217; IDENTIFIED BY &#8216;password&#8217;; Then grant the appropriate privileges with GRANT &#8230; ON database.* TO &#8216;username&#8217;@&#8217;localhost&#8217;;<\/p> <\/div> <div class=\"schema-faq-section\" id=\"faq-question-1777031978183\"><strong class=\"schema-faq-question\">How do I grant all privileges in MySQL?<\/strong> <p class=\"schema-faq-answer\">Run: GRANT ALL PRIVILEGES ON databasename.* TO &#8216;username&#8217;@&#8217;host&#8217;; Follow with FLUSH PRIVILEGES; to apply immediately.<\/p> <\/div> <div class=\"schema-faq-section\" id=\"faq-question-1777031986664\"><strong class=\"schema-faq-question\">How do I see what privileges a MySQL user has?<\/strong> <p class=\"schema-faq-answer\">Run: SHOW GRANTS FOR &#8216;username&#8217;@&#8217;host&#8217;; This lists all grants assigned to that user.<\/p> <\/div> <div class=\"schema-faq-section\" id=\"faq-question-1777031994608\"><strong class=\"schema-faq-question\">How do I revoke privileges in MySQL?<\/strong> <p class=\"schema-faq-answer\">Run: REVOKE privilege_type ON database.* FROM &#8216;username&#8217;@&#8217;host&#8217;; Specify exactly which privileges to remove. Then FLUSH PRIVILEGES;<\/p> <\/div> <\/div>\n","protected":false},"excerpt":{"rendered":"<p>Running your entire MySQL instance under the root account is the database equivalent of leaving your server&#8217;s front door unlocked. Here&#8217;s how to create users with the right privileges and nothing more. Prerequisites: MySQL Access and Setup You need an existing MySQL installation and the ability to log in as root or a user with [&hellip;]<\/p>\n","protected":false},"author":44,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"set","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[18],"tags":[],"ppma_author":[3402],"class_list":["post-30211","post","type-post","status-publish","format-standard","hentry","category-tutorials"],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"Milan Ivanovic","author_link":"https:\/\/contabo.com\/blog\/author\/milan\/"},"uagb_comment_info":0,"uagb_excerpt":"Running your entire MySQL instance under the root account is the database equivalent of leaving your server&#8217;s front door unlocked. Here&#8217;s how to create users with the right privileges and nothing more. Prerequisites: MySQL Access and Setup You need an existing MySQL installation and the ability to log in as root or a user with&hellip;","authors":[{"term_id":3402,"user_id":0,"is_guest":1,"slug":"contabro","display_name":"ContaBro","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/?s=96&d=mm&r=g","0":null,"1":"","2":"","3":"","4":"","5":"","6":"","7":"","8":""}],"_links":{"self":[{"href":"https:\/\/contabo.com\/blog\/wp-json\/wp\/v2\/posts\/30211","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/contabo.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/contabo.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/contabo.com\/blog\/wp-json\/wp\/v2\/users\/44"}],"replies":[{"embeddable":true,"href":"https:\/\/contabo.com\/blog\/wp-json\/wp\/v2\/comments?post=30211"}],"version-history":[{"count":1,"href":"https:\/\/contabo.com\/blog\/wp-json\/wp\/v2\/posts\/30211\/revisions"}],"predecessor-version":[{"id":30212,"href":"https:\/\/contabo.com\/blog\/wp-json\/wp\/v2\/posts\/30211\/revisions\/30212"}],"wp:attachment":[{"href":"https:\/\/contabo.com\/blog\/wp-json\/wp\/v2\/media?parent=30211"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/contabo.com\/blog\/wp-json\/wp\/v2\/categories?post=30211"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/contabo.com\/blog\/wp-json\/wp\/v2\/tags?post=30211"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/contabo.com\/blog\/wp-json\/wp\/v2\/ppma_author?post=30211"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}