{"id":11326,"date":"2018-06-22T10:29:13","date_gmt":"2018-06-22T08:29:13","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/"},"modified":"2018-06-22T10:29:13","modified_gmt":"2018-06-22T08:29:13","slug":"mysql-8-0-roles-are-finally-there","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/","title":{"rendered":"MySQL 8.0 &#8211; Roles are finally there"},"content":{"rendered":"<p>Roles have been existing on many RDBMS for a long time by now. Starting from version 8.0, this functionality is finally there for MySQL.<br \/>\nThe most important advantage is to define only once a role that includes a &#8220;set of permissions&#8221;, then assign it to each user, avoiding wasting time declaring them individually.<\/p>\n<p>In MySQL, a role can be created like a user, but without the &#8220;identified by&#8221; clause and without login:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nmysqld2-(root@localhost) [(none)]&gt; CREATE ROLE 'r_sakila_read';\nQuery OK, 0 rows affected (0.03 sec)\nmysqld2-(root@localhost) [(none)]&gt; select user,host,authentication_string from mysql.user;\n+------------------+-----------+------------------------------------------------------------------------+\n| user             | host      | authentication_string                                                  |\n+------------------+-----------+------------------------------------------------------------------------+\n| r_sakila_read    | %         |                                                                        |\n| multi_admin      | localhost | $A$005$E?D\/&gt;efE+Rt12omzr.78VnfR3kxj8KLG.aP84gdPMxW7A\/7uG3D80B          |\n| mysql.infoschema | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                              |\n| mysql.session    | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                              |\n| mysql.sys        | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                              |\n| root             | localhost | {u]E\/m)qyn3YRk2u.JKdxj9\/6Krd8uqNtHRzKA38cG5qyC3ts5                     |\n+------------------+-----------+------------------------------------------------------------------------+\n<\/pre>\n<p>After that you can grant some privileges to this role, as you usually do for users:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nmysqld2-(root@localhost) [(none)]&gt; grant select on sakila.* to 'r_sakila_read';\nQuery OK, 0 rows affected (0.10 sec)\nmysqld2-(root@localhost) [(none)]&gt; show grants for r_sakila_read;\n+---------------------------------------------------+\n| Grants for r_sakila_read@%                        |\n+---------------------------------------------------+\n| GRANT USAGE ON *.* TO `r_sakila_read`@`%`         |\n| GRANT SELECT ON `sakila`.* TO `r_sakila_read`@`%` |\n+---------------------------------------------------+\n2 rows in set (0.00 sec)\n<\/pre>\n<p>Now you can create your user:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nmysqld2-(root@localhost) [(none)]&gt; create user 'u_sakila1'@localhost identified by 'qwepoi123098';\nERROR 1819 (HY000): Your password does not satisfy the current policy requirements\n<\/pre>\n<p>And yes, check your password policy because of the new validate_password component (starting from version 8.0, it replaces the old validate_password plugin, but the concept remains the same):<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nmysqld2-(root@localhost) [(none)]&gt; show variables like 'validate_password_%';\n+--------------------------------------+--------+\n| Variable_name                        | Value  |\n+--------------------------------------+--------+\n| validate_password_check_user_name    | ON     |\n| validate_password_dictionary_file    |        |\n| validate_password_length             | 8      |\n| validate_password_mixed_case_count   | 1      |\n| validate_password_number_count       | 1      |\n| validate_password_policy             | MEDIUM |\n| validate_password_special_char_count | 1      |\n+--------------------------------------+--------+\n7 rows in set (0.01 sec)\nmysqld2-(root@localhost) [(none)]&gt; create user 'u_sakila1'@localhost identified by 'QwePoi123098!';\nQuery OK, 0 rows affected (0.08 sec)\n<\/pre>\n<p>In my example I have by default a MEDIUM level for checking passwords which means &#8220;Length; numeric, lowercase\/uppercase, and special characters&#8221; (I will better talk about validate_password component in an upcoming blog). Let&#8217;s go back to roles&#8230;<\/p>\n<p>Grant the created role to your created user (as you usually grant a privilege): <\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nmysqld2-(root@localhost) [(none)]&gt; grant 'r_sakila_read' to 'u_sakila1'@localhost;\nQuery OK, 0 rows affected (0.01 sec)\nmysqld2-(root@localhost) [(none)]&gt; flush privileges;\nQuery OK, 0 rows affected (0.02 sec)\n<\/pre>\n<p>At this point if you check privileges of your user through a USING clause, you will get information about the granted roles and also privileges associated with each role:  <\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nmysqld2-(root@localhost) [(none)]&gt; show grants for 'u_sakila1'@localhost using 'r_sakila_read';\n+-------------------------------------------------------+\n| Grants for u_sakila1@localhost                        |\n+-------------------------------------------------------+\n| GRANT USAGE ON *.* TO `u_sakila1`@`localhost`         |\n| GRANT SELECT ON `sakila`.* TO `u_sakila1`@`localhost` |\n| GRANT `r_sakila_read`@`%` TO `u_sakila1`@`localhost`  |\n+-------------------------------------------------------+\n3 rows in set (0.00 sec)\n<\/pre>\n<p>Now if you try to connect with your user and do a select of data on the database on which you have a read privilege, you will discover that something is still missing:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nmysqld2-(root@localhost) [(none)]&gt;  system mysql -u u_sakila1 -p\nmysqld2-(u_sakila1@localhost) [(none)]&gt; use sakila;\nERROR 1044 (42000): Access denied for user 'u_sakila1'@'localhost' to database 'sakila'\nmysqld2-(u_sakila1@localhost) [(none)]&gt; SELECT CURRENT_ROLE();\n+----------------+\n| CURRENT_ROLE() |\n+----------------+\n| NONE           |\n+----------------+\n1 row in set (0.00 sec)\n<\/pre>\n<p>Why?<br \/>\nBecause you have to define which roles will be active when the user authenticates. You you can do that by adding the &#8220;DEFAULT ROLE <em>role<\/em>&#8221; during the user creation (starting from version 8.0.3), or even later through the following statement:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nmysqld2-(root@localhost) [(none)]&gt; set default role r_sakila_read to 'u_sakila1'@localhost;\nQuery OK, 0 rows affected (0.08 sec)\n<\/pre>\n<p>Otherwise, starting from version 8.0.2, you can directly let the server activate by default all roles granted to each user, setting the activate_all_roles_on_login variable to ON:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nmysqld2-(root@localhost) [(none)]&gt; show variables like '%activate%';\n+-----------------------------+-------+\n| Variable_name               | Value |\n+-----------------------------+-------+\n| activate_all_roles_on_login | OFF   |\n+-----------------------------+-------+\n1 row in set (0.00 sec)\nmysqld2-(root@localhost) [(none)]&gt; set global activate_all_roles_on_login=ON;\nQuery OK, 0 rows affected (0.00 sec)\nmysqld2-(root@localhost) [(none)]&gt; show variables like '%activate%';\n+-----------------------------+-------+\n| Variable_name               | Value |\n+-----------------------------+-------+\n| activate_all_roles_on_login | ON    |\n+-----------------------------+-------+\n1 row in set (0.01 sec)\n<\/pre>\n<p>So if you check again, all works correctly:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nmysqld2-(root@localhost) [mysql]&gt; select * from role_edges;\n+-----------+----------------+-----------+-----------+-------------------+\n| FROM_HOST | FROM_USER      | TO_HOST   | TO_USER   | WITH_ADMIN_OPTION |\n+-----------+----------------+-----------+-----------+-------------------+\n| %         | r_sakila_read  | localhost | u_sakila1 | N                 |\n+-----------+----------------+-----------+-----------+-------------------+\n4 rows in set (0.00 sec)\nmysqld2-(root@localhost) [(none)]&gt;  system mysql -u u_sakila1 -p\nmysqld2-(u_sakila1@localhost) [(none)]&gt; use sakila\nmysqld2-(u_sakila1@localhost) [sakila]&gt; connect\nConnection id:    29\nCurrent database: sakila\nmysqld2-(u_sakila1@localhost) [sakila]&gt; select CURRENT_ROLE();\n+---------------------+\n| CURRENT_ROLE()      |\n+---------------------+\n| `r_sakila_read`@`%` |\n+---------------------+\n1 row in set (0.00 sec)\n<\/pre>\n<p>Enjoy your roles now! \ud83d\ude09<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Roles have been existing on many RDBMS for a long time by now. Starting from version 8.0, this functionality is finally there for MySQL. The most important advantage is to define only once a role that includes a &#8220;set of permissions&#8221;, then assign it to each user, avoiding wasting time declaring them individually. In MySQL, [&hellip;]<\/p>\n","protected":false},"author":42,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,1316,149],"tags":[],"type_dbi":[],"class_list":["post-11326","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-mysql","category-security"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>MySQL 8.0 - Roles are finally there - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL 8.0 - Roles are finally there\" \/>\n<meta property=\"og:description\" content=\"Roles have been existing on many RDBMS for a long time by now. Starting from version 8.0, this functionality is finally there for MySQL. The most important advantage is to define only once a role that includes a &#8220;set of permissions&#8221;, then assign it to each user, avoiding wasting time declaring them individually. In MySQL, [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-22T08:29:13+00:00\" \/>\n<meta name=\"author\" content=\"Elisa Usai\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Elisa Usai\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/\"},\"author\":{\"name\":\"Elisa Usai\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/ac5847ee8d7bad4196e72660c1377b1f\"},\"headline\":\"MySQL 8.0 &#8211; Roles are finally there\",\"datePublished\":\"2018-06-22T08:29:13+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/\"},\"wordCount\":325,\"commentCount\":0,\"articleSection\":[\"Database Administration &amp; Monitoring\",\"MySQL\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/\",\"name\":\"MySQL 8.0 - Roles are finally there - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2018-06-22T08:29:13+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/ac5847ee8d7bad4196e72660c1377b1f\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL 8.0 &#8211; Roles are finally there\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/ac5847ee8d7bad4196e72660c1377b1f\",\"name\":\"Elisa Usai\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/d41c7e94b7e4cd42bdcc5b82003c00562de8da0cf0a1081fbe832d47e3a828ff?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d41c7e94b7e4cd42bdcc5b82003c00562de8da0cf0a1081fbe832d47e3a828ff?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d41c7e94b7e4cd42bdcc5b82003c00562de8da0cf0a1081fbe832d47e3a828ff?s=96&d=mm&r=g\",\"caption\":\"Elisa Usai\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/elisa-usai\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"MySQL 8.0 - Roles are finally there - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/","og_locale":"en_US","og_type":"article","og_title":"MySQL 8.0 - Roles are finally there","og_description":"Roles have been existing on many RDBMS for a long time by now. Starting from version 8.0, this functionality is finally there for MySQL. The most important advantage is to define only once a role that includes a &#8220;set of permissions&#8221;, then assign it to each user, avoiding wasting time declaring them individually. In MySQL, [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/","og_site_name":"dbi Blog","article_published_time":"2018-06-22T08:29:13+00:00","author":"Elisa Usai","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Elisa Usai","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/"},"author":{"name":"Elisa Usai","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/ac5847ee8d7bad4196e72660c1377b1f"},"headline":"MySQL 8.0 &#8211; Roles are finally there","datePublished":"2018-06-22T08:29:13+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/"},"wordCount":325,"commentCount":0,"articleSection":["Database Administration &amp; Monitoring","MySQL","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/","url":"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/","name":"MySQL 8.0 - Roles are finally there - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-06-22T08:29:13+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/ac5847ee8d7bad4196e72660c1377b1f"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/mysql-8-0-roles-are-finally-there\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"MySQL 8.0 &#8211; Roles are finally there"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/ac5847ee8d7bad4196e72660c1377b1f","name":"Elisa Usai","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/d41c7e94b7e4cd42bdcc5b82003c00562de8da0cf0a1081fbe832d47e3a828ff?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/d41c7e94b7e4cd42bdcc5b82003c00562de8da0cf0a1081fbe832d47e3a828ff?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d41c7e94b7e4cd42bdcc5b82003c00562de8da0cf0a1081fbe832d47e3a828ff?s=96&d=mm&r=g","caption":"Elisa Usai"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/elisa-usai\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11326","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/42"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=11326"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11326\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11326"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11326"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11326"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11326"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}