{"id":5504,"date":"2017-01-17T00:47:00","date_gmt":"2017-01-17T07:47:00","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5504"},"modified":"2023-11-13T19:50:12","modified_gmt":"2023-11-14T02:50:12","slug":"mysql-roles","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-roles\/","title":{"rendered":"The Ultimate Guide to MySQL Roles"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you will learn how to use MySQL roles to streamline privilege management.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Introduction to MySQL roles<\/h2>\n\n\n\n<p>Typically, a MySQL database server may have multiple users with the same set of privileges. <\/p>\n\n\n\n<p>Previously, the only way to <a href=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-grant\/\">grant <\/a>and <a href=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-revoke\/\">revoke <\/a>the same privileges to multiple users was to modify the privileges of each user individually, which was time-consuming.<\/p>\n\n\n\n<p>To streamline this process, MySQL introduced a new concept known as role. A role is essentially a named collection of privileges.<\/p>\n\n\n\n<p>Just like user accounts, you can grant privileges to roles and revoke privileges from them. This feature helps you to simplify privilege management significantly.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"667\" height=\"303\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/09\/MySQL-Role.png\" alt=\"\" class=\"wp-image-8877\" srcset=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/09\/MySQL-Role.png 667w, https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/09\/MySQL-Role-200x91.png 200w\" sizes=\"auto, (max-width: 667px) 100vw, 667px\" \/><\/figure>\n\n\n\n<p>If you want to grant the same set of privileges to multiple users, follow these steps:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>First, create a new role.<\/li>\n\n\n\n<li>Second, grant privileges to the role.<\/li>\n\n\n\n<li>Third, grant the role to the users.<\/li>\n<\/ul>\n\n\n\n<p>When you want to change the privileges of the users, you only need to change the privileges of the granted role. These changes will apply to all users to whom the role has been granted.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">MySQL roles example<\/h2>\n\n\n\n<p>First, <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-create-table\/\">create a new database<\/a> named <code>crm<\/code>, which stands for Customer Relationship Management.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">DATABASE<\/span> crm;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Next, switch the current database to the <code>crm<\/code> database:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">USE<\/span> crm;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Then, create <code>customers<\/code> table inside the <code>CRM<\/code> database.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> customers(\n    <span class=\"hljs-keyword\">id<\/span> <span class=\"hljs-built_in\">INT<\/span> PRIMARY <span class=\"hljs-keyword\">KEY<\/span> AUTO_INCREMENT,\n    first_name <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">255<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>, \n    last_name <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">255<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>, \n    phone <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">15<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    email <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">255<\/span>)\n);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>After that, <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-insert-multiple-rows\/\">insert two rows<\/a> into the <code>customers<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span> customers(first_name,last_name,phone,email)\n<span class=\"hljs-keyword\">VALUES<\/span>(<span class=\"hljs-string\">'John'<\/span>,<span class=\"hljs-string\">'Doe'<\/span>,<span class=\"hljs-string\">'(408)-987-7654'<\/span>,<span class=\"hljs-string\">'john.doe@mysqltutorial.org'<\/span>),\n      (<span class=\"hljs-string\">'Lily'<\/span>,<span class=\"hljs-string\">'Bush'<\/span>,<span class=\"hljs-string\">'(408)-987-7985'<\/span>,<span class=\"hljs-string\">'lily.bush@mysqltutorial.org'<\/span>);\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Finally, verify the insert by using the following <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-select-from\/\"><code>SELECT<\/code><\/a> statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> * <span class=\"hljs-keyword\">FROM<\/span> customers;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"435\" height=\"59\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/09\/mysql-role-sample-table.png\" alt=\"mysql role - sample table\" class=\"wp-image-8872\" srcset=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/09\/mysql-role-sample-table.png 435w, https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/09\/mysql-role-sample-table-200x27.png 200w\" sizes=\"auto, (max-width: 435px) 100vw, 435px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Creating roles<\/h2>\n\n\n\n<p>Suppose you are developing an application that utilizes the <code>CRM<\/code> database. To interact with the <code>CRM<\/code> database, you need to create accounts for developers who need full access to the database. Additionally, you need to create accounts for users who only need read access and others who require both read and write access.<\/p>\n\n\n\n<p>To avoid granting privileges to each user account individually, create a set of roles and grant the appropriate roles to each user account.<\/p>\n\n\n\n<p>To create new roles, you use the <code>CREATE ROLE<\/code> statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">ROLE<\/span> \n    crm_dev, \n    crm_read, \n    crm_write;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The role name is similar to the user account and consists of two parts: the name and host:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">role_name@host_name<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>If you omit the host part, it defaults to &#8216;%&#8217; which means any host.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Granting privileges to roles<\/h2>\n\n\n\n<p>To grant privileges to a role, you use <code>GRANT<\/code> statement. The following statement grants all privileges to <code>crm_dev<\/code> role:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">GRANT<\/span> <span class=\"hljs-keyword\">ALL<\/span> \n<span class=\"hljs-keyword\">ON<\/span> crm.* \n<span class=\"hljs-keyword\">TO<\/span> crm_dev;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The following statement grants <code>SELECT<\/code> privilege to <code>crm_read<\/code> role:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">GRANT<\/span> <span class=\"hljs-keyword\">SELECT<\/span> \n<span class=\"hljs-keyword\">ON<\/span> crm.* \n<span class=\"hljs-keyword\">TO<\/span> crm_read;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The following statement grants <code>INSERT<\/code>, <code>UPDATE<\/code>, and <code>DELETE<\/code> privileges to <code>crm_write<\/code> role:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">GRANT<\/span> <span class=\"hljs-keyword\">INSERT<\/span>, <span class=\"hljs-keyword\">UPDATE<\/span>, <span class=\"hljs-keyword\">DELETE<\/span>\n<span class=\"hljs-keyword\">ON<\/span> crm.* \n<span class=\"hljs-keyword\">TO<\/span> crm_write;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Assigning roles to user accounts<\/h2>\n\n\n\n<p>Suppose you need one user account as the developer, one user account with read-only access, and two user accounts that can have read\/write access.<\/p>\n\n\n\n<p>To create new users, you use <a href=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-create-user\/\"><code>CREATE USER<\/code><\/a> statements as follows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-comment\">-- developer user <\/span>\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">USER<\/span> crm_dev1@localhost <span class=\"hljs-keyword\">IDENTIFIED<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-string\">'Secure$1782'<\/span>;\n\n<span class=\"hljs-comment\">-- read access user<\/span>\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">USER<\/span> crm_read1@localhost <span class=\"hljs-keyword\">IDENTIFIED<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-string\">'Secure$5432'<\/span>;    \n\n<span class=\"hljs-comment\">-- read\/write users<\/span>\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">USER<\/span> crm_write1@localhost <span class=\"hljs-keyword\">IDENTIFIED<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-string\">'Secure$9075'<\/span>;   \n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">USER<\/span> crm_write2@localhost <span class=\"hljs-keyword\">IDENTIFIED<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-string\">'Secure$3452'<\/span>;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>To assign roles to users, you use the <code>GRANT<\/code> statement.<\/p>\n\n\n\n<p>The following statement grants the <code>crm_rev<\/code> role to the user account <code>crm_dev1@localhost<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">GRANT<\/span> crm_dev \n<span class=\"hljs-keyword\">TO<\/span> crm_dev1@localhost;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The following statement grants the <code>crm_read<\/code> role to the user account <code>crm_read1@localhost<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">GRANT<\/span> crm_read \n<span class=\"hljs-keyword\">TO<\/span> crm_read1@localhost;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The following statement grants the <code>crm_read<\/code> and <code>crm_write<\/code> roles to the user accounts <code>crm_write1@localhost<\/code> and <code>crm_write2@localhost<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-14\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">GRANT<\/span> crm_read, \n    crm_write \n<span class=\"hljs-keyword\">TO<\/span> crm_write1@localhost, \n    crm_write2@localhost;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-14\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>To verify the role assignments, you use the <code><a href=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-show-grants\/\">SHOW GRANTS<\/a><\/code> statement as the following example:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-15\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SHOW<\/span> <span class=\"hljs-keyword\">GRANTS<\/span> <span class=\"hljs-keyword\">FOR<\/span> crm_dev1@localhost;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-15\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The statement returned the following result set:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"326\" height=\"56\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/09\/mysql-role-show-grants.png\" alt=\"\" class=\"wp-image-8874\" srcset=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/09\/mysql-role-show-grants.png 326w, https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/09\/mysql-role-show-grants-200x34.png 200w\" sizes=\"auto, (max-width: 326px) 100vw, 326px\" \/><\/figure>\n\n\n\n<p>As you can see, it just returned granted roles. To display the privileges represented by these roles, you use the <code>USING<\/code> clause with the names of the granted roles as follows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-16\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SHOW<\/span> <span class=\"hljs-keyword\">GRANTS<\/span> \n<span class=\"hljs-keyword\">FOR<\/span> crm_write1@localhost \n<span class=\"hljs-keyword\">USING<\/span> crm_write;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-16\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The statement returns the following output:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"410\" height=\"78\" src=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/09\/mysql-role-show-grants-with-using-clause.png\" alt=\"mysql role - show grants with using clause\" class=\"wp-image-8875\" srcset=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/09\/mysql-role-show-grants-with-using-clause.png 410w, https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2019\/09\/mysql-role-show-grants-with-using-clause-200x38.png 200w\" sizes=\"auto, (max-width: 410px) 100vw, 410px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Setting default roles<\/h2>\n\n\n\n<p>If you connect to MySQL using the <code>crm_read1<\/code> user account and try to access the <code>CRM<\/code> database:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-17\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">&gt;mysql -u crm_read1 -p\nEnter password: ***********\nmysql&gt;<span class=\"hljs-keyword\">USE<\/span> crm;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-17\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The statement issued the following error message:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-18\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">ERROR 1044 (42000): Access denied for user 'crm_read1'@'localhost' to database 'crm'\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-18\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>This is because when you granted roles to a user account, it didn&#8217;t automatically activate the roles when the user account connects to the database server.<\/p>\n\n\n\n<p>If you invoke the <code>CURRENT_ROLE()<\/code> function, it will return <code>NONE<\/code>, indicating that no active roles are set:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-19\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> current_role();<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-19\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-20\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">+<span class=\"hljs-comment\">----------------+<\/span>\n| current_role() |\n+<span class=\"hljs-comment\">----------------+<\/span>\n| NONE           |\n+<span class=\"hljs-comment\">----------------+<\/span>\n1 row in <span class=\"hljs-keyword\">set<\/span> (<span class=\"hljs-number\">0.00<\/span> sec)\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-20\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>To specify which roles should be active each time a user account connects to the database server, you can use the <code>SET DEFAULT ROLE<\/code> statement.<\/p>\n\n\n\n<p>The following statement sets the default role for the <code>crm_read1@localhost<\/code> account to include all its assigned roles:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-21\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SET<\/span> <span class=\"hljs-keyword\">DEFAULT<\/span> <span class=\"hljs-keyword\">ROLE<\/span> <span class=\"hljs-keyword\">ALL<\/span> <span class=\"hljs-keyword\">TO<\/span> crm_read1@localhost;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-21\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Now, if you connect to the MySQL database server using the <code>crm_read1<\/code> user account and then invoke the <code>CURRENT_ROLE()<\/code> function, you will see the default roles for <code>crm_read1<\/code> user account.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-22\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">&gt;mysql -u crm_read1 -p\nEnter password: ***********\nmysql&gt; <span class=\"hljs-keyword\">select<\/span> current_role();\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-22\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-23\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">+<span class=\"hljs-comment\">----------------+<\/span>\n| current_role() |\n+<span class=\"hljs-comment\">----------------+<\/span>\n| `crm_read`@`%` |\n+<span class=\"hljs-comment\">----------------+<\/span>\n1 row in <span class=\"hljs-keyword\">set<\/span> (<span class=\"hljs-number\">0.00<\/span> sec)\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-23\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>You can test the privileges of <code>crm_read<\/code> account by switching the current database to <code>CRM<\/code> and executing a <code>SELECT<\/code> statement and a <code>DELETE<\/code> statement as follows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-24\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">mysql&gt; <span class=\"hljs-keyword\">use<\/span> crm;\nDatabase changed\nmysql&gt; <span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-keyword\">COUNT<\/span>(*) <span class=\"hljs-keyword\">FROM<\/span> customers;\n+<span class=\"hljs-comment\">----------+<\/span>\n| COUNT(*) |\n+<span class=\"hljs-comment\">----------+<\/span>\n|        2 |\n+<span class=\"hljs-comment\">----------+<\/span>\n1 row in <span class=\"hljs-keyword\">set<\/span> (<span class=\"hljs-number\">0.00<\/span> sec)\n\nmysql&gt; <span class=\"hljs-keyword\">DELETE<\/span> <span class=\"hljs-keyword\">FROM<\/span> customers;\nERROR 1142 (42000): <span class=\"hljs-keyword\">DELETE<\/span> command denied <span class=\"hljs-keyword\">to<\/span> <span class=\"hljs-keyword\">user<\/span> <span class=\"hljs-string\">'crm_read1'<\/span>@<span class=\"hljs-string\">'localhost'<\/span> <span class=\"hljs-keyword\">for<\/span> <span class=\"hljs-keyword\">table<\/span> <span class=\"hljs-string\">'customers'<\/span>\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-24\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>It worked as expected. When we issued the <code>DELETE<\/code> statement, MySQL issued an error because the <code>crm_read1<\/code> user account had only read access.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Setting active roles<\/h2>\n\n\n\n<p>A user account can modify the current user&#8217;s effective privileges within the current session by specifying which granted roles are active.<\/p>\n\n\n\n<p>The following statement set the active role to <code>NONE<\/code>, indicating that no role is active:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-25\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SET<\/span> <span class=\"hljs-keyword\">ROLE<\/span> <span class=\"hljs-keyword\">NONE<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-25\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>To set active roles to all granted roles, you use the following statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-26\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SET<\/span> <span class=\"hljs-keyword\">ROLE<\/span> <span class=\"hljs-keyword\">ALL<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-26\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>To set active roles to default roles established by the <code>SET DEFAULT ROLE<\/code> statement, you can use the following statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-27\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SET<\/span> <span class=\"hljs-keyword\">ROLE<\/span> <span class=\"hljs-keyword\">DEFAULT<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-27\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>To set named roles as active, you can use the following statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-28\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SET<\/span> <span class=\"hljs-keyword\">ROLE<\/span> \n    granted_role_1\n    &#91;,granted_role_2, ...]\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-28\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Revoking privileges from roles<\/h2>\n\n\n\n<p>To revoke privileges from a specific role, you use the <code><a href=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-revoke\/\">REVOKE<\/a><\/code> statement. The <code>REVOKE<\/code> statement affects not only the role but also any account that has been granted the role.<\/p>\n\n\n\n<p>For example, to temporarily make all read\/write users read-only, you can modify the <code>crm_write<\/code> role as follows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-29\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">REVOKE<\/span> <span class=\"hljs-keyword\">INSERT<\/span>, <span class=\"hljs-keyword\">UPDATE<\/span>, <span class=\"hljs-keyword\">DELETE<\/span> \n<span class=\"hljs-keyword\">ON<\/span> crm.* \n<span class=\"hljs-keyword\">FROM<\/span> crm_write;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-29\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>To restore the privileges, you&#8217;ll need to re-grant them as follows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-30\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">GRANT<\/span> <span class=\"hljs-keyword\">INSERT<\/span>, <span class=\"hljs-keyword\">UPDATE<\/span>, <span class=\"hljs-keyword\">DELETE<\/span> \n<span class=\"hljs-keyword\">ON<\/span> crm.* \n<span class=\"hljs-keyword\">FOR<\/span> crm_write;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-30\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Removing roles<\/h2>\n\n\n\n<p>To delete one or more roles, you use the <code>DROP ROLE<\/code> statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-31\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">DROP<\/span> <span class=\"hljs-keyword\">ROLE<\/span> role_name&#91;, role_name, ...];<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-31\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Similar to the <code>REVOKE<\/code> statement, the <code>DROP ROLE<\/code> statement revokes roles from every user account to which they were granted.<\/p>\n\n\n\n<p>For example, to remove the <code>crm_read<\/code> and <code>crm_write<\/code> roles, you can use the following statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-32\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">DROP<\/span> <span class=\"hljs-keyword\">ROLE<\/span> crm_read, crm_write;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-32\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Copying privileges from one user account to another<\/h2>\n\n\n\n<p>MySQL treats user accounts like roles; as a result, you can grant a user account to another user account like granting a role to that user account. This allows you to copy privileges from one user to another.<\/p>\n\n\n\n<p>Suppose you want another developer account for the <code>CRM<\/code> database:<\/p>\n\n\n\n<p>First, create the new user account:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-33\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">USER<\/span> crm_dev2@localhost \n<span class=\"hljs-keyword\">IDENTIFIED<\/span> <span class=\"hljs-keyword\">BY<\/span> <span class=\"hljs-string\">'Secure$6275'<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-33\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Second, copy privileges from the <code>crm_dev1<\/code> user account to <code>crm_dev2<\/code> user account as follows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-34\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">GRANT<\/span> crm_dev1@localhost \n<span class=\"hljs-keyword\">TO<\/span> crm_dev2@localhost;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-34\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Summary<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use roles to simplify and streamline privilege management within your database.<\/li>\n\n\n\n<li>Use the <code>CREATE ROLE<\/code> statement to create named collections of privileges.<\/li>\n\n\n\n<li>Use the <code>GRANT<\/code> statement to grant privileges to a role.<\/li>\n\n\n\n<li>Use the <code>SHOW GRANTS<\/code> statement to display the granted privileges of a role.<\/li>\n\n\n\n<li>Use the <code>REVOKE<\/code> statement to revoke privileges from a role.<\/li>\n\n\n\n<li>Use the <code>DROP ROLE<\/code> statement to delete a role.<\/li>\n<\/ul>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful? <\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"5504\"\n\t\t\t\tdata-post-url=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-roles\/\"\n\t\t\t\tdata-post-title=\"The Ultimate Guide to MySQL Roles\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"5504\"\n\t\t\t\tdata-post-url=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-roles\/\"\n\t\t\t\tdata-post-title=\"The Ultimate Guide to MySQL Roles\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>In this tutorial, you will learn how to use MySQL roles to simplify the privilege managements.<\/p>\n","protected":false},"author":2,"featured_media":5511,"parent":441,"menu_order":33,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-5504","page","type-page","status-publish","has-post-thumbnail","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>The Ultimate Guide To MySQL Roles By Examples<\/title>\n<meta name=\"description\" content=\"This tutorial shows you how to use MySQL roles, which are named collections of privileges, that help you simplify the privilege managements.\" \/>\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.mysqltutorial.org\/mysql-roles\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Ultimate Guide To MySQL Roles By Examples\" \/>\n<meta property=\"og:description\" content=\"This tutorial shows you how to use MySQL roles, which are named collections of privileges, that help you simplify the privilege managements.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.mysqltutorial.org\/mysql-roles\/\" \/>\n<meta property=\"og:site_name\" content=\"MySQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-14T02:50:12+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/01\/mysql-roles-statement.png\" \/>\n\t<meta property=\"og:image:width\" content=\"150\" \/>\n\t<meta property=\"og:image:height\" content=\"128\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-roles\\\/\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-roles\\\/\",\"name\":\"The Ultimate Guide To MySQL Roles By Examples\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-roles\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-roles\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.mysqltutorial.org\\\/wp-content\\\/uploads\\\/2017\\\/01\\\/mysql-roles-statement.png\",\"datePublished\":\"2017-01-17T07:47:00+00:00\",\"dateModified\":\"2023-11-14T02:50:12+00:00\",\"description\":\"This tutorial shows you how to use MySQL roles, which are named collections of privileges, that help you simplify the privilege managements.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-roles\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-roles\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-roles\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/wp-content\\\/uploads\\\/2017\\\/01\\\/mysql-roles-statement.png\",\"contentUrl\":\"https:\\\/\\\/www.mysqltutorial.org\\\/wp-content\\\/uploads\\\/2017\\\/01\\\/mysql-roles-statement.png\",\"width\":150,\"height\":128},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-roles\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL Administration\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-administration\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"The Ultimate Guide to MySQL Roles\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\",\"name\":\"MySQL Tutorial\",\"description\":\"A comprehensive MySQL Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.mysqltutorial.org\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"The Ultimate Guide To MySQL Roles By Examples","description":"This tutorial shows you how to use MySQL roles, which are named collections of privileges, that help you simplify the privilege managements.","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.mysqltutorial.org\/mysql-roles\/","og_locale":"en_US","og_type":"article","og_title":"The Ultimate Guide To MySQL Roles By Examples","og_description":"This tutorial shows you how to use MySQL roles, which are named collections of privileges, that help you simplify the privilege managements.","og_url":"https:\/\/www.mysqltutorial.org\/mysql-roles\/","og_site_name":"MySQL Tutorial","article_modified_time":"2023-11-14T02:50:12+00:00","og_image":[{"width":150,"height":128,"url":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/01\/mysql-roles-statement.png","type":"image\/png"}],"twitter_misc":{"Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.mysqltutorial.org\/mysql-roles\/","url":"https:\/\/www.mysqltutorial.org\/mysql-roles\/","name":"The Ultimate Guide To MySQL Roles By Examples","isPartOf":{"@id":"https:\/\/www.mysqltutorial.org\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-roles\/#primaryimage"},"image":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-roles\/#primaryimage"},"thumbnailUrl":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/01\/mysql-roles-statement.png","datePublished":"2017-01-17T07:47:00+00:00","dateModified":"2023-11-14T02:50:12+00:00","description":"This tutorial shows you how to use MySQL roles, which are named collections of privileges, that help you simplify the privilege managements.","breadcrumb":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-roles\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.mysqltutorial.org\/mysql-roles\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.mysqltutorial.org\/mysql-roles\/#primaryimage","url":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/01\/mysql-roles-statement.png","contentUrl":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2017\/01\/mysql-roles-statement.png","width":150,"height":128},{"@type":"BreadcrumbList","@id":"https:\/\/www.mysqltutorial.org\/mysql-roles\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.mysqltutorial.org\/"},{"@type":"ListItem","position":2,"name":"MySQL Administration","item":"https:\/\/www.mysqltutorial.org\/mysql-administration\/"},{"@type":"ListItem","position":3,"name":"The Ultimate Guide to MySQL Roles"}]},{"@type":"WebSite","@id":"https:\/\/www.mysqltutorial.org\/#website","url":"https:\/\/www.mysqltutorial.org\/","name":"MySQL Tutorial","description":"A comprehensive MySQL Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.mysqltutorial.org\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/5504","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/comments?post=5504"}],"version-history":[{"count":4,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/5504\/revisions"}],"predecessor-version":[{"id":12407,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/5504\/revisions\/12407"}],"up":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/441"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/media\/5511"}],"wp:attachment":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/media?parent=5504"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}