3.9 Roles A role is a named collection of privileges. A role may be assigned to a user, but a user cannot be assigned to a role. For example, users can log in to the database; roles cannot. A user can own objects while a role cannot. The function of a role is to group logically associated privileges and allow those privileges to be passed to a user by referencing the role. Consequently, when a user is assigned (granted) a role, that user inherits all the privileges assigned to the role. If the role's privileges are later changed, then the new privileges will be in effect the next time the user logs in to the database. In an Oracle system, grants are issued on individual tables to individual users. This sounds very simple, but when hundreds of tables and users are involved, the implementation and management can be very complex. The usual situation is for a manager to ask the DBA to give ralph the same privileges that mary has. If grants have been made to individual users, the first task will be to find out what privileges mary has, and then create a script to duplicate those privileges, and finally to run that script to give ralph the privileges. The action of giving an employee the same privileges as another employee is much easier if roles are used. The DBA creates a role and grants some privileges to it. These privileges can be a mix of system and object privileges. In a financial system, there might be a FINCLERK role. This role could include the CREATE SESSION privilege to allow the users to log on, and also some grants to enable table access. In turn , this role is granted to all the users who need the financial clerk privileges. In many systems, only one or two roles are needed by most users. The task of identifying the privileges held by a specific user now becomes a simple matter. The DBA can query the data dictionary view, DBA_ROLE_PRIVS, and specify the user of interest, as shown in the next section. (Chapter 4, describes this view and other data dictionary views.) Figure 3.2 shows the difference between assigning individual privileges to each user and assigning the privileges to a role and then assigning the role to each person. Figure 3.2. Role implementation  Let's say that you are an application developer who works for a car dealership . You have developed an application to track the parts ordered, received, and used to make automobile repairs . The mechanics can access the application to enter the parts which they have put into a customer's car. The parts department manager can enter new part information, including the cost of the part, into the database through the same application. The parts department clerks can look up the parts information to verify if a part is currently in stock but cannot modify any of the parts information. You create three roles: -
The MECHANICS role, with SELECT and UPDATE privileges on the parts information tables -
The MANAGERS role, with SELECT, INSERT, and UPDATE privileges on the parts information tables -
The CLERKS role, with SELECT privileges on the parts information tables If the dealership later decides that clerks should be permitted to insert information on new parts into the database, you will just need to grant INSERT to the CLERKS role. The next time a clerk logs in to the database, she will be able to insert information to the parts tables. 3.9.1 Determining Privileges Granted to a User To determine what privileges mary has, the DBA would issue the following command: SQL> SELECT * 2> FROM dba_role_privs 3> WHERE grantee = 'MARY'; GRANTEE GRANTED_ROLE ADM DEF ------------------------------ ------------------------------ --- --- MARY CONNECT NO YES MARY FINCLERK NO YES In this case, mary has two roles, CONNECT (the Oracle-supplied default role) and FINCLERK (an in-house-created role); see the "Oracle-Supplied Roles" section for information about CONNECT. 3.9.2 Determining Privileges Granted to a Role To determine what privileges have been granted to a role, the DBA would issue the following commands: SQL> SELECT * 2> FROM dba_sys_privs 3> WHERE grantee = `CONNECT'; GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- CONNECT ALTER SESSION NO CONNECT CREATE CLUSTER NO CONNECT CREATE DATABASE LINK NO CONNECT CREATE SEQUENCE NO CONNECT CREATE SESSION NO CONNECT CREATE SYNONYM NO CONNECT CREATE TABLE NO CONNECT CREATE VIEW NO 8 rows selected. SQL> SELECT * 2> FROM dba_tab_privs 3> WHERE grantee = `CONNECT'; no rows selected In this case, there were no object privileges granted to the CONNECT role. In other cases, there will be. Thus, both DBA views (DBA_SYS_PRIVS and DBA_TAB_PRIVS) should always be checked to determine the exact composition of the role of interest. If new privileges are granted to a role, those privileges are inherited by all users who hold the role. Likewise, any privilege that is revoked is revoked from all users holding the role. The "ADM" column shown in the output example indicates whether ADMIN OPTION was specified in the GRANT statement that granted the privilege. Chapter 4, and Chapter 5, describe the ADMIN OPTION. 3.9.3 Establishing Classes of Users Setting up roles is not difficult, but we highly recommend that you plan and document your roles carefully . As part of the system design, you must establish the various classes of users. As part of the audit plan, there should be a check to ensure that the permissions granted to roles have not changed. The classes of users identified will lead to the roles which are really required for the application. The classes can be expressed in standard English like "financial clerk," "accounts payable manager," "approving official," and so forth. The actual role names created in the database have to conform to the database naming restrictions regarding length and must contain no embedded spaces. So, as we've shown, "financial clerk" could be implemented as FINCLERK, FIN_CLERK, FINANCE_CLRK, or something like that. After the classes and, hence, the role names have been established, you can create a matrix showing the roles and tables. Where the role and table rows and columns intersect, the privileges would typically be listed as S,I,U,D (for SELECT, INSERT, UPDATE, and DELETE) or a combination of these. Other terms in common use are CREATE (instead of INSERT) and READ (instead of SELECT). The options would then be R,C,U,D or, as most designers order the privileges, C,R,U,D. When this set of abbreviations is used, the chart is usually called a CRUD matrix . 3.9.4 Oracle-Supplied Roles Oracle supplies several default roles with the installation of the database. These roles provide the same system privileges of earlier pre-role based versions of Oracle. The major default roles and a summary of privileges are shown in Table 3.1. The privileges associated with these roles are discussed in Chapter 5; that chapter also describes several additional roles that are used less frequently. Table 3.1. Oracle Default Roles | Role Name | Type of Privileges | | CONNECT | Allows login and ability to create tables, views, synonyms, and database links. | | RESOURCE | Adds cluster, procedure, and trigger privileges. | | DBA | Complete authority to manage database and users. Can create users. | | SYSOPER | Ability to start up and shut down the database. | | SYSDBA | All privileges available to the DBA role with the ability to create, start up, shut down, and recover a database. | We recommend that you do not use the CONNECT and RESOURCE roles for application users. These roles convey privileges that are not required by most of the users functioning within the bounds of an application. They also do not convey any indication of the purpose of the privilege by their names. We recommend that you create a standard logon role. This role should have only the CREATE SESSION system privileges. Grant the role to every user when the user account is created. Granting the role will allow the user to connect to the database and will make it much easier to disable an account by revoking the logon role but leaving the other privileges in place. Do not use roles to assign privileges that will be required by a stored program. Because a role cannot own objects, programs do not recognize a role as a valid mechanism for granting privileges. This is usually not a problem if the schema owner also owns all of the stored programs.  |
