Working with Roles
Granting privileges to individual users is acceptable if there are not that many. But imagine that the database consists of 300 tables and has 500 users. If everyone is to be given privileges, at least 500 GRANT statements are required. However, it is likely that many more statements are necessary, and this is very difficult to manage. That is why the concept of role has been added to SQL.
MySQL supports no roles.
A role is a defined set of privileges (the same privileges we have already described) that is granted to users. If the privileges of one role are altered (a table privilege is added, for example), the privileges of all users belonging to that role are changed automatically. It is easier to manage the privileges this way. A user may have several roles.
With the CREATE ROLE statement, new roles can be created.
To assign privileges to roles, the definition of the concept grantees has been extended so that all kinds of privileges can be assigned to roles.
Figure. Create the role SALES and give this role the SELECT and INSERT privileges on the PENALTIES table. Next, grant the SALES role to users ILENE, KELLY, JIM, and MARC.
Explanation: The first statement creates the new role. With the GRANT statement, table privileges are granted. The structure of this statement is the same as the one used for granting privileges to users. Next, with a special version of the GRANT statement, we give the role to the four users. It is now possible to extend the privileges of the SALES role with one statement instead of using an entire set of GRANT statements.
Roles can be removed with the DROP ROLE statement. And, of course, all privileges belonging to that role also are removed; in turn, the users lose their privileges.
Figure. Remove the role SALES.