Roles

Another way to avoid the difficulty of changing user privileges on a case-by-case basis is to use roles. The concept of a role in the database environment is similar to the group concept in an operating system. A role is a database feature that lets the DBA standardize and change the access privileges of many users by treating them as members of a class. (User-defined roles cannot be granted the database-level privileges Connect, Resource, or DBA, but roles can hold discretionary access privileges on database objects, including privileges on table objects, on fragments of tables, on user-defined data types, on user-defined routines, and on programming languages.)

For example, if you grant the Connect privilege to the PUBLIC group for each of the databases that handle company news and messages, you can create a role called news_mes to which you grant the Insert and Delete privileges on tables in which employees who are granted that role can add or delete rows. When a new employee arrives, you must only add that person to the news_mes role. By issuing the SET ROLE news_mes statement to enable that role, the new employee acquires the access privileges of the news_mes role. (Alternatively, you can define a user.sysdbopen procedure in each database where those privileges are needed, where user is the authorization identifier of the new employee, to execute the SET ROLE news_mes statement automatically when the user connects to the database.)

This process also works in reverse. To change the discretionary access privileges of everyone who has been granted the news_mes role, use the GRANT or REVOKE statements to change the privileges of that role in each database where the news_mes role is defined.