Views, Procedures and Tables That Are Owned by Roles

Views, procedures, and tables are more easily managed when they are owned by a user-extended role instead of a user.

Make users who need access to a table, view, or stored procedure members of the role that owns the object. This eliminates the need to qualify the object name when accessing.

For example, the table Employees is owned by the role personnel, of which Jeff is a member. When Jeff wants to refer to the Employees table, he need only specify the name of the table in SQL statements, for example:

SELECT * FROM EMPLOYEES

However, when John, who is not a member of Personnel, wants to refer to the Employees table, he must use the qualified name of the table, for example:

SELECT * FROM PERSONNEL.EMPLOYEES

Note: Since ownership of database objects is associated with a single user ID, when the owner is a role, ownership of the table is not inherited by members of the role.

System privileges should not be granted to roles that own objects. Instead:

create distinct roles with specific system privileges granted
grant users who require the specific system privileges membership to the applicable role
grant each distinct role to the role that owns the object.

This allows for complete control of the tasks performed by each user. Maintain authorized tasks by granting and revoking membership in the applicable role associated with the object.

For example, the table Sales is owned by the Sales1 role. Users Mary, Bob, Joe, Laurel, and Sally are granted membership to Sales1. Create Task1_role and granted it the system privileges necessary to complete a specific task. Grant Task1_role to Mary and Bob. Create Task2_role, grant it specific system privileges, and grant it to Joe and Sally. Finally, grant both Task1_role and Task2_role to Sales1. Though both roles are granted to Sales1, the underlying system privileges of Task1_role and Task2_role are not automatically inherited by the other members of Sales1. Mary and Bob can perform different tasks than Joe and Sally. Since Laurel has not been granted to either Task1_role or Task2_role, and no system privileges have been granted directly to Sales1, Laurel can perform no privileged tasks on the Sales table. This configuration allows you to maintain and control the tasks that can be performed by each user.