Roles

Database roles are a way of indirectly assigning privileges to users, which helps in the overall management aspects of security. In the database, there are many privileges that allow the users to do many things. The privileges can be granted directly to the users or granted to database roles that, in turn, are granted to the database users. By doing the latter, you can simplify security administration. Figure 7-1 shows how privilege maintenance can be simplified.

Figure 7-1: Database roles simplify privilege maintenance tasks.

If you want to add or remove a privilege from a group of users, you can issue a single statement adding or removing the privilege from the role. This adds or removes the privilege from every user that has been granted that role. If you directly grant privileges to users and you have 100 users, you would have to issue 100 separate statements to add or remove a privilege for those users.

Role Hierarchies

Utilizing roles, the task of granting or revoking privileges to multiple users can be accomplished with a single statement. I am all for efficiency; however, a tricky part to managing privileges via roles is that roles can be granted to other roles. This nesting adds wonderful flexibility in capturing real-world security policies. Unfortunately, the flexibility can also lead to complexity and confusion when trying to unravel what privileges are granted to what or whom.

The previous scripts will help you in determining specific privileges a user has regardless of how the privileges were obtained. You should nonetheless consider the management aspect when creating the role structure in your database; limiting the number of nested roles will help simplify the complexity of your privilege structures and make overall security management easier.

To help in this endeavor, the following package will display all roles for a user as well as how the user received that role. This role hierarchy depiction is helpful when combined with the obj_privs.sql script, previously shown, in determining how a privilege granted to a role finally ended in a user’s set of available privileges (or privilege domain). First, to illustrate the complexity associated with nested role grants, several roles are created and granted to other roles with one role finally being directly granted to the user SCOTT:

sec_mgr@KNOX10g> CREATE ROLE a; Role created. sec_mgr@KNOX10g> CREATE ROLE b; Role created. sec_mgr@KNOX10g> CREATE ROLE c; Role created. sec_mgr@KNOX10g> CREATE ROLE d; Role created. sec_mgr@KNOX10g> CREATE ROLE e; Role created. sec_mgr@KNOX10g> GRANT a TO b; Grant succeeded. sec_mgr@KNOX10g> GRANT b TO c; Grant succeeded. sec_mgr@KNOX10g> GRANT c TO d; Grant succeeded. sec_mgr@KNOX10g> GRANT e TO c; Grant succeeded. sec_mgr@KNOX10g> -- Granting D to SCOTT will give SCOTT all roles sec_mgr@KNOX10g> GRANT d TO scott; Grant succeeded.

The following package uses recursion to display the roles granted for a user in the hierarchy they were granted:

sec_mgr@KNOX10g> CREATE OR REPLACE PACKAGE show_role_hierarchy   2  AS   3    PROCEDURE display (p_username IN VARCHAR2);   4  END;   5  / Package created. sec_mgr@KNOX10g> CREATE OR REPLACE PACKAGE BODY show_role_hierarchy   2  AS   3  --------------------------------------------------------   4    FUNCTION convert_level (p_level IN NUMBER)   5      RETURN VARCHAR2   6    AS   7      l_str  VARCHAR2 (32767);   8    BEGIN   9      FOR i IN 1 .. p_level  10      LOOP  11        l_str := l_str || '..';  12      END LOOP;  13  14      RETURN l_str;  15    END;  16  --------------------------------------------------------  17    PROCEDURE recursive_role_getter (  18      p_role   IN  VARCHAR2,  19      p_level  IN  NUMBER)  20    AS  21    BEGIN  22      FOR irec IN (SELECT   granted_role  23                       FROM dba_role_privs  24                      WHERE grantee = UPPER (p_role)  25                   ORDER BY 1)  26      LOOP  27        DBMS_OUTPUT.put_line (   'Indirect Role: '  28                              || convert_level (p_level)  29                              || irec.granted_role  30                              || ' via '  31                              || p_role);  32        recursive_role_getter (irec.granted_role,  33                               p_level + 1);  -- recurse  34      END LOOP;  35    EXCEPTION  36      WHEN OTHERS  37      THEN  38        NULL;  39    END;  40  --------------------------------------------------------  41    PROCEDURE display (p_username IN VARCHAR2)  42    AS  43    BEGIN  44      FOR rec IN (SELECT   granted_role  45                      FROM dba_role_privs  46                     WHERE grantee = UPPER (p_username)  47                  ORDER BY 1)  48      LOOP  49        DBMS_OUTPUT.put_line (   'Direct Role:   '  50                              || rec.granted_role);  51        recursive_role_getter (rec.granted_role, 1);  52      END LOOP;  53    END;  54  --------------------------------------------------------  55  END;  56  / Package body created.

Tracing the Privilege

By combining the previous procedure with the USER_OBJECT_PRIVS view, you can identify precisely how a user received a privilege. To illustrate this, a table is created and select privileges are granted on the table to role A.

sec_mgr@KNOX10g> -- Create a table and grant privileges to role A sec_mgr@KNOX10g> CREATE TABLE obj_of_interest AS SELECT * FROM DUAL; Table created. sec_mgr@KNOX10g> GRANT SELECT ON obj_of_interest TO a; Grant succeeded.

You can use the obj_privs.sql script or log on as SCOTT and query the USER_OBJ_PRIVS view to determine what privileges SCOTT has on the table as well as how he received the privilege:

sec_mgr@KNOX10g> -- See if SCOTT has access to table OBJ_OF_INTEREST. sec_mgr@KNOX10g> -- This query can be run as SCOTT or you can   sec_mgr@KNOX10g> -- execute the obj_privs.sql script   sec_mgr@KNOX10g> conn scott/tiger   Connected. scott@KNOX10g> COL privilege format a20   scott@KNOX10g> COL object format a20   scott@KNOX10g> COL grantee format a20   scott@KNOX10g> SELECT privilege, object, grantee     2    FROM user_object_privs       3   WHERE owner = 'SEC_MGR' AND OBJECT = 'OBJ_OF_INTEREST'; PRIVILEGE            OBJECT               GRANTEE   -------------------- -------------------- --------------------   SELECT               OBJ_OF_INTEREST      A 

You now see how the role hierarchy can be useful. The privilege was granted to A. How did the user get role A? A direct query on the DBA_ROLE_PRIVS view and using the Oracle Enterprise Manager will only tell you that SCOTT has role D. You know the user must have role A, but it’s not obvious how he got it. Running the role hierarchy display program, you can see the role relationships:

scott@KNOX10g> conn sec_mgr/oracle10g   Connected. sec_mgr@KNOX10g> EXEC show_role_hierarchy.display('scott') Direct Role:   CONNECT   Direct Role:   D Indirect Role: ..C via D Indirect Role: ....B via C Indirect Role: ......A via B Indirect Role: ....E via C   Direct Role:   RESOURCE     PL/SQL procedure successfully completed.

The USER_OBJ_PRIVS view told you SCOTT received the SELECT privilege from role A. This output shows you that A was granted to B, B was granted to C, C granted to D, and finally D was directly granted to SCOTT.

Designing for Definer and Invoker Rights

There exists an often overlooked dependency on roles and PL/SQL named programs. Oracle allows two modes of operation for executing named PL/SQL: definer rights and invoker rights.

Definer rights is the default mode for PL/SQL programs and is the mode that has been in place for years. As such, many people are familiar with how to design and implement effective and secure PL/SQL applications using the definer rights mode. There is a relationship to roles with definer rights in that roles are disabled.

Note 

All database roles are disabled while compiling, and more importantly, while executing named PL/SQL programs created with definer rights.

Not only are roles disabled, but you also can’t make a SET ROLE call to enable them from inside a definer rights program. This isn’t a severe limitation, but knowledge of this is important in designing applications and determining how users and roles will be used by your applications. It’s also helpful in preventing misfiled bugs or technical assistance requests.

The following example shows how one might accidentally fall into this role-based predicament. In the previous section, you established that SCOTT has the SELECT privilege on SEC_MGR.OBJ_OF_INTEREST table by way of a role grant. SCOTT can query the table directly and therefore he can write an anonymous block of PL/SQL that queries the table without problems. But notice when he takes the PL/SQL block that just successfully executed and places it in a procedure, the table access is prohibited:

scott@KNOX10g> -- User can query table. scott@KNOX10g> SELECT *   2    FROM sec_mgr.obj_of_interest; D - X scott@KNOX10g> -- User can query table within scott@KNOX10g> -- an anonymous PL/SQL block. scott@KNOX10g> DECLARE   2    l_dummy  VARCHAR2 (1);   3  BEGIN   4    SELECT dummy   5      INTO l_dummy   6      FROM sec_mgr.obj_of_interest;   7    DBMS_OUTPUT.put_line ('Value: ' || l_dummy);   8  END;   9  / Value: X PL/SQL procedure successfully completed. scott@KNOX10g> -- User cannot create a procedure. scott@KNOX10g> CREATE OR REPLACE PROCEDURE show_obj   2  AS   3    l_dummy  VARCHAR2 (1);   4  BEGIN   5    SELECT dummy   6      INTO l_dummy   7      FROM sec_mgr.obj_of_interest;   8    DBMS_OUTPUT.put_line ('Value: ' || l_dummy);   9  END;  10  / Warning: Procedure created with compilation errors. scott@KNOX10g> show error Errors for PROCEDURE SHOW_OBJ: LINE/COL ERROR -------- -------------------------------------------------- 4/14     PL/SQL: SQL Statement ignored 5/30     PL/SQL: ORA-00942: table or view does not exist

SCOTT has the select privilege by default. However, the role that contains the privilege is disabled during the program’s compilation. The role is also disabled during program execution so using dynamic SQL to get the program to compile will not solve the problem either. Note the script ran as an anonymous PL/SQL block; anonymous blocks and straight SQL queries allow roles to remain enabled.

Invoker rights is the alternate model for program execution in the Oracle database. To use the SELECT privilege granted to the role requires SCOTT to compile the program with invoker rights. The syntax in the previous procedure also will have to be modified because the database roles are only enabled for invoker rights during execution, not during compilation. Therefore, dynamic SQL will have to be used to get the procedure to compile. The following code does this. The procedure is converted from the default definer rights to an invoker rights by adding the AUTHID CURRENT_USER directive as seen in line 2:

scott@KNOX10g> -- Use dynamic SQL and invoker rights scott@KNOX10g> CREATE OR REPLACE PROCEDURE show_obj   2  AUTHID CURRENT_USER   3  AS   4    l_dummy  VARCHAR2 (1);   5  BEGIN   6    EXECUTE IMMEDIATE    'SELECT dummy '   7                      || 'FROM sec_mgr.obj_of_interest'   8                 INTO l_dummy;   9    DBMS_OUTPUT.put_line ('Value: ' || l_dummy);  10  END;  11  / Procedure created. scott@KNOX10g> EXEC show_obj Value: X 

Often the most practical solution for the roles and named PL/SQL dilemma is to simply remove the role dependency and make the direct privilege grants to the user creating the procedure. Granting select privilege on OBJ_OF_INTEREST directly to SCOTT would allow the original definer rights program to compile and execute.

Note 

The point to this is that all privileges needed to compile and execute the PL/SQL have to be directly granted to the user or granted to PUBLIC (although I would discourage grants to PUBLIC). Privileges received via roles aren’t available. Understanding this relationship is critical to ensuring the successful and effective implementation of your PL/SQL programs.