Working with Roles






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.

Portability

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.

<create role statement> ::=
   CREATE ROLE <role name>


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.

<grantees> ::=
   PUBLIC                               |
   <user name> [ { , <user name> }... ] |
   <role name> [ { , <role name> }... ]


A special version of the GRANT statement has also been created to assign roles to users.

<grant statement> ::=
   <grant role statement>

<grant role statement> ::=
   GRANT <role name> [ { , <role name> }... ]
   TO <grantees>


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.

CREATE ROLE SALES

GRANT   SELECT, INSERT
ON      PENALTIES
TO      SALES

GRANT SALES TO ILENE, KELLY, JIM, 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.

DROP ROLE SALES

Exercise 23.7:

Create the users JOE, JACO, and CHRIS with the password JAZZ. Then, create the role ADMIN and give this role all privileges on the COMMITTEE_MEMBERS table. Grant this new role to the users just created.



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows