| A role is an object created in the database that contains group-like privileges. Roles can reduce security maintenance by not having to grant explicit privileges directly to a user. Group privilege management is much easier to handle with roles. A role's privileges can be changed, and such a change is transparent to the user . | If a user needs SELECT and UPDATE table privileges on a table at a specified time within an application, a role with those privileges can temporarily be assigned until the transaction is complete. When a role is first created, it has no real value other than being a role within a database. It can be granted to users or other roles. Let's say that a schema named APP01 grants the SELECT table privilege to the RECORDS_CLERK role on the EMPLOYEE_PAY table. Any user or role granted the RECORDS_CLERK role now would have SELECT privileges on the EMPLOYEE_PAY table. Likewise, if APP01 revoked the SELECT table privilege from the RECORDS_CLERK role on the EMPLOYEE_PAY table, any user or role granted the RECORDS_CLERK role would no longer have SELECT privileges on that table. | Roles are not supported by MySQL. The lack of role usage is a weakness in some implementations of SQL. | The CREATE ROLE Statement A role is created with the CREATE ROLE statement. CREATE ROLE role_name; Granting privileges to roles is the same as granting privileges to a user. Study the following example. CREATE ROLE RECORDS_CLERK; Role created. GRANT SELECT, INSERT, UPDATE, DELETE ON EMPLOYEE_PAY TO RECORDS_CLERK; Grant succeeded. GRANT RECORDS_CLERK TO USER1; Grant succeeded. The DROP ROLE Statement A role is dropped using the DROP_ROLE statement. DROP ROLE role_name; The following is an example: DROP ROLE RECORDS_CLERK; Role dropped. The SET ROLE Statement A role can be set for a user SQL session using the SET_ROLE statement. SET ROLE role_name; The following is an example: SET ROLE RECORDS_CLERK; Role set. You can set more than one role at once: SET ROLE RECORDS_CLERK, ROLE2, ROLE3; Role set. In some implementations, such as Oracle, all roles granted to a user are automatically default roles, which means the roles will be set and available to the user as soon as the user logs in to the database. |