{"id":344,"date":"2026-10-29T15:10:00","date_gmt":"2026-10-29T15:10:00","guid":{"rendered":"https:\/\/backendside.com\/blog\/?p=344"},"modified":"2026-11-21T11:27:37","modified_gmt":"2026-11-21T11:27:37","slug":"mysql-users-roles-privileges-security","status":"publish","type":"post","link":"https:\/\/backendside.com\/blog\/2026\/10\/29\/mysql-users-roles-privileges-security\/","title":{"rendered":"Users, Roles and Privileges: Locking Down a MySQL Server the Right Way"},"content":{"rendered":"<p class=\"lead\">Database security problems are rarely exotic. Far more often they are an application connecting as <code>root<\/code>, an account that can log in from any host on the internet, or a single shared login used by five people and three scripts. MySQL and MariaDB have a precise, layered permission system that makes all of this avoidable &mdash; if you know how accounts, privileges and roles fit together. This guide covers exactly that, with the syntax for MySQL 8.x and MariaDB 10.11\/11.x and the differences between them.<\/p>\n<h2>Accounts are user <em>and<\/em> host<\/h2>\n<p>The first surprise for newcomers: a MySQL account is not just a name. It is <code>'user'@'host'<\/code>, and <code>'app'@'localhost'<\/code> and <code>'app'@'%'<\/code> are two completely separate accounts with separate passwords and privileges. The host part controls where the account may connect from:<\/p>\n<table>\n<thead>\n<tr>\n<th>Host part<\/th>\n<th>Meaning<\/th>\n<th>Typical use<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td><code>'localhost'<\/code><\/td>\n<td>Same machine only (socket or loopback)<\/td>\n<td>Apps running on the database server, admin work<\/td>\n<\/tr>\n<tr>\n<td><code>'10.0.0.25'<\/code><\/td>\n<td>One specific IP<\/td>\n<td>A single application server<\/td>\n<\/tr>\n<tr>\n<td><code>'10.0.0.%'<\/code><\/td>\n<td>Any host in that subnet<\/td>\n<td>A pool of app servers on a private network<\/td>\n<\/tr>\n<tr>\n<td><code>'%'<\/code><\/td>\n<td>Anywhere<\/td>\n<td>Avoid for privileged accounts; pair with a firewall and TLS if you must<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>CREATE USER 'shop_app'@'10.0.0.%' IDENTIFIED BY 'a-long-random-passphrase';\n\n-- See who exists and where they can connect from\nSELECT user, host FROM mysql.user ORDER BY user, host;<\/code><\/pre>\n<h2>Least privilege: grant only what is needed<\/h2>\n<p>Privileges can be granted at several levels &mdash; global (<code>*.*<\/code>), database (<code>shop.*<\/code>), table (<code>shop.orders<\/code>) and even column. The principle is simple: give each account the smallest set that lets it do its job, at the narrowest level.<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>-- The web app reads and writes data, nothing more\nGRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'10.0.0.%';\n\n-- A reporting user that can only read two tables\nCREATE USER 'reports'@'10.0.0.40' IDENTIFIED BY 'another-long-passphrase';\nGRANT SELECT ON shop.orders    TO 'reports'@'10.0.0.40';\nGRANT SELECT ON shop.customers TO 'reports'@'10.0.0.40';\n\n-- Check the result\nSHOW GRANTS FOR 'shop_app'@'10.0.0.%';<\/code><\/pre>\n<p>Note what the application does <em>not<\/em> get: <code>CREATE<\/code>, <code>DROP<\/code>, <code>ALTER<\/code>, <code>FILE<\/code>, <code>PROCESS<\/code> or anything global. Run schema migrations with a separate, more privileged account that the running application never sees. If the app is ever compromised through SQL injection, the damage is limited to the data it could already touch &mdash; it cannot drop tables or read files from the server&rsquo;s disk.<\/p>\n<h3>Privileges that deserve extra suspicion<\/h3>\n<table>\n<thead>\n<tr>\n<th>Privilege<\/th>\n<th>Why it is dangerous<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td><code>ALL PRIVILEGES ON *.*<\/code><\/td>\n<td>Effectively root on every database<\/td>\n<\/tr>\n<tr>\n<td><code>GRANT OPTION<\/code><\/td>\n<td>Lets the account hand its privileges to others<\/td>\n<\/tr>\n<tr>\n<td><code>FILE<\/code><\/td>\n<td>Read and write files on the server host via <code>LOAD DATA<\/code> \/ <code>SELECT ... INTO OUTFILE<\/code><\/td>\n<\/tr>\n<tr>\n<td><code>SUPER<\/code><\/td>\n<td>Broad admin powers; deprecated in MySQL 8 in favour of narrower dynamic privileges such as <code>SYSTEM_VARIABLES_ADMIN<\/code><\/td>\n<\/tr>\n<tr>\n<td><code>PROCESS<\/code><\/td>\n<td>See every running statement, including other users&rsquo; queries<\/td>\n<\/tr>\n<tr>\n<td>Privileges on the <code>mysql<\/code> schema<\/td>\n<td>Direct access to the account tables themselves<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>To take something back, use <code>REVOKE<\/code> with the same level you granted at:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>REVOKE DELETE ON shop.* FROM 'shop_app'@'10.0.0.%';<\/code><\/pre>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">MyDBDesk &mdash; Privileges You Can See, Tick and Un-tick<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">GRANT syntax is easy to get subtly wrong. <strong>MyDBDesk<\/strong>&rsquo;s <strong>Privileges<\/strong> tab lists users and MariaDB roles with their grants, lets you create or drop users, and changes access in one <strong>Edit privileges<\/strong> editor that opens with what the user already has ticked &mdash; you just tick and un-tick. Applying makes only the changes you made, and privileges the editor cannot show are left alone. No manual GRANT statements.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/mydbdesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore MyDBDesk &rarr;<\/a>\n<\/div>\n<h2>Roles: grant to the job, not the person<\/h2>\n<p>Granting the same five privileges to twelve analysts one by one is error-prone, and so is remembering to remove them all when someone changes team. <strong>Roles<\/strong> are named bundles of privileges: grant privileges to the role once, then grant the role to people. Both MySQL 8 and MariaDB support them, with small but important syntax differences.<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>-- Works on both\nCREATE ROLE analyst;\nGRANT SELECT ON shop.* TO analyst;\nGRANT analyst TO 'sara'@'10.0.0.%';\n\n-- MySQL 8: roles are not active at login until you say so\nSET DEFAULT ROLE analyst TO 'sara'@'10.0.0.%';\n--   (or set activate_all_roles_on_login = ON server-wide)\n\n-- MariaDB: note FOR instead of TO\nSET DEFAULT ROLE analyst FOR 'sara'@'10.0.0.%';\n\n-- Inside a session, either engine:\nSET ROLE analyst;\nSELECT CURRENT_ROLE();<\/code><\/pre>\n<p>The most common confusion: after granting a role on MySQL, the user logs in and gets &ldquo;access denied&rdquo;. The role was granted but never activated &mdash; set a default role and the problem disappears. Under the hood, MySQL roles are accounts with a host part (<code>analyst<\/code> means <code>'analyst'@'%'<\/code>), while MariaDB roles are a separate kind of object with no host.<\/p>\n<p>When someone leaves the team, <code>REVOKE analyst FROM 'sara'@'10.0.0.%';<\/code> removes everything in one statement, and changing what analysts may see is a single <code>GRANT<\/code> to the role.<\/p>\n<h2>Encrypt connections, and require it<\/h2>\n<p>Passwords and query results cross the network in clear text unless the connection uses TLS. Modern MySQL generates certificates automatically on first start, and MariaDB 11.4 and later enables TLS by default too; older MariaDB setups usually need certificates configured by hand. Either way, you can <em>require<\/em> encryption per account so a misconfigured client fails loudly instead of silently falling back:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>ALTER USER 'reports'@'10.0.0.40' REQUIRE SSL;\n\n-- From a connected session, confirm the link is encrypted\nSHOW STATUS LIKE 'Ssl_cipher';<\/code><\/pre>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">MyDBDesk &mdash; Secure Connections From Your Desk<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">A locked-down server deserves a careful client. In <strong>MyDBDesk<\/strong> each saved connection picks its own <strong>SSL mode<\/strong> &mdash; Off, Preferred, Required, or certificate-verified &mdash; with an optional CA certificate, so an account created with <code>REQUIRE SSL<\/code> just works. Saved connection passwords are <strong>encrypted and kept on your own device<\/strong>, never written in plain text, and the app runs offline with no telemetry or cloud sync.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/mydbdesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore MyDBDesk &rarr;<\/a>\n<\/div>\n<h2>Passwords, lockouts and dormant accounts<\/h2>\n<p>A few account options are worth knowing:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>-- Disable an account without deleting it (both engines)\nALTER USER 'contractor'@'%' ACCOUNT LOCK;\n\n-- MySQL 8.0.19+: lock for a day after 5 failed logins\nALTER USER 'shop_app'@'10.0.0.%'\n  FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;\n\n-- MariaDB: the equivalent is the server-wide max_password_errors variable\n\n-- Force a password change at next login (both)\nALTER USER 'sara'@'10.0.0.%' PASSWORD EXPIRE;<\/code><\/pre>\n<p>Locking is better than dropping when you are unsure whether something still uses an account: a locked account fails fast and visibly, and unlocking is one statement.<\/p>\n<h2>Day-one hardening checklist<\/h2>\n<ol>\n<li><strong>Run the secure-installation script<\/strong> (<code>mysql_secure_installation<\/code>, or <code>mariadb-secure-installation<\/code> on MariaDB). It removes anonymous users and the test database and lets you secure root.<\/li>\n<li><strong>Keep root local.<\/strong> No <code>'root'@'%'<\/code>. On Linux MariaDB, root often authenticates through <code>unix_socket<\/code> &mdash; only the system root user can log in as database root, which is a sensible default.<\/li>\n<li><strong>Bind to the right interface.<\/strong> If only local apps connect, set <code>bind-address = 127.0.0.1<\/code>. Otherwise bind to a private address and firewall port 3306 &mdash; never expose it to the whole internet.<\/li>\n<li><strong>One account per application and per person<\/strong>, each with least privilege. Shared logins make auditing impossible.<\/li>\n<li><strong>Require TLS<\/strong> for any account that connects over a network.<\/li>\n<li><strong>Review regularly.<\/strong> List accounts with broad grants and ask whether each still needs them:<\/li>\n<\/ol>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>-- Accounts with global privileges worth questioning\nSELECT user, host\nFROM mysql.user\nWHERE Super_priv = 'Y' OR File_priv = 'Y' OR Grant_priv = 'Y';\n\n-- Accounts that can connect from anywhere\nSELECT user, host FROM mysql.user WHERE host = '%';<\/code><\/pre>\n<p>Keep application secrets out of source control as well: the strongest GRANT design does little if the app password is sitting in a public repository.<\/p>\n<h2>Key takeaways<\/h2>\n<ul>\n<li>An account is <strong><code>'user'@'host'<\/code><\/strong>; restrict the host to where the account genuinely connects from.<\/li>\n<li>Apply <strong>least privilege<\/strong>: applications get data privileges on their own database only; migrations use a separate account.<\/li>\n<li>Treat <strong><code>ALL ON *.*<\/code>, <code>GRANT OPTION<\/code>, <code>FILE<\/code>, <code>SUPER<\/code> and <code>PROCESS<\/code><\/strong> as admin-only.<\/li>\n<li>Use <strong>roles<\/strong> to grant by job; on MySQL remember <code>SET DEFAULT ROLE ... TO<\/code>, on MariaDB <code>SET DEFAULT ROLE ... FOR<\/code>.<\/li>\n<li><strong>Require TLS<\/strong> for networked accounts, lock dormant ones, and keep root local.<\/li>\n<li>Review accounts and grants on a schedule &mdash; permissions only ever drift wider on their own.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>One application account with ALL PRIVILEGES and a root password on a sticky note is how most MySQL breaches start. A practical guide to accounts, least-privilege GRANTs, roles on MySQL and MariaDB, encrypted connections and the hardening steps worth doing on day one.<\/p>\n","protected":false},"author":1,"featured_media":413,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,2],"tags":[28,24,25,23],"class_list":["post-344","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","category-windows","tag-database-security","tag-mariadb","tag-mydbdesk","tag-mysql"],"_links":{"self":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/344","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/comments?post=344"}],"version-history":[{"count":1,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/344\/revisions"}],"predecessor-version":[{"id":414,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/344\/revisions\/414"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/media\/413"}],"wp:attachment":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/media?parent=344"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/categories?post=344"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/tags?post=344"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}