Using Roles Wisely

Standard roles are very powerful. There are a few important things that can affect your design with respect to roles, privileges, and the subsequent assignments to users.

Too Many Roles

The ability to create and assign roles to other roles repeatedly, coupled with the ability to grant privileges to both users and roles in a redundant way, can make absolute security management confusing and difficult. An almost endless hierarchy of roles can be created. The confusion is not from Oracle for allowing roles to be granted to roles or redundant privileges, but rather from administrators’ behavior in actually doing this.

Tip 

Keep in mind that every role enabled for a user takes up a small percentage of memory. Recall that the MAX_ENABLED_ROLES init.ora parameter restricts the number of roles for this very reason. Take this into consideration when creating and assigning roles.

Naming

Naming roles may seem trivial, but there are (at least) two things to consider. First, role names have to be unique not only from other roles, but also from other usernames. This may seem obvious, but often people develop applications forgetting that the role name is global. If two applications require different privileges granted through roles, but both use applications depending on the same poorly chosen role name—such as “Developer”—then it may not be possible or desirable to install both applications on the same database.

Second, there are often semantics associated with the name. A role named “Administrator” implies powerful privileges. Unfortunately, the name is too vague. If one were conducting a security audit, the role name could be either helpful or a hindrance depending on how carefully the name is chosen. A user with this role is an Administrator of what?

A solution to both of these problems simply requires a bit of thought at the design stage. For example, inserting a prefix for the specific application name in front of the role name may prove to be descriptive as well as helpful in ensuring global uniqueness. You may wish to create the APP_X_DEVELOPER and APP_Y_ADMINISTRATOR roles instead of the plain Developer and Administrator roles given previously.

Dependencies

Roles are not contained in schemas. If user X creates role A, then dropping user X still leaves the role A and all system privileges associated with the role. This is probably desired behavior, but as in the case with synonyms, it’s important to understand that a DROP USER X CASCADE doesn’t delete the roles or all the subsequent privileges given to the roles.

Finally, it’s important to have an understanding of the enabling and disabling behavior as it relates to roles granted to roles. Any roles that are default enabled for a user will also default enable all other roles granted to it; this includes not only the standard roles, but also the password-protected roles and the secure application roles. Consider the following example in which a password-protected role is created. The DBA role is granted to this protected role. Enabling the protected role bypasses the password-protection feature and also default enables the DBA role and all roles granted to it.

sec_mgr@KNOX10g> -- Create a user for role example sec_mgr@KNOX10g> CREATE USER foo IDENTIFIED BY bar; User created. sec_mgr@KNOX10g> GRANT CREATE SESSION TO foo; Grant succeeded. sec_mgr@KNOX10g> -- Create a password protected role. sec_mgr@KNOX10g> CREATE ROLE app_1 IDENTIFIED BY secretpassword; Role created. sec_mgr@KNOX10g> -- Grant DBA role to protected role. sec_mgr@KNOX10g> -- Normally this would also protect the DBA role. sec_mgr@KNOX10g> GRANT DBA TO app_1; Grant succeeded. sec_mgr@KNOX10g> -- Make protected role an eligible role for user. sec_mgr@KNOX10g> GRANT app_1 TO foo; Grant succeeded. sec_mgr@KNOX10g> -- CRITICAL MISTAKE: forgot to disable role app_1 sec_mgr@KNOX10g> CONN foo/bar Connected. foo@KNOX10g> -- DBA role and all roles granted to DBA are enabled foo@KNOX10g> SELECT role FROM session_roles; ROLE ------------------------------ APP_1 DBA SELECT_CATALOG_ROLE HS_ADMIN_ROLE EXECUTE_CATALOG_ROLE DELETE_CATALOG_ROLE EXP_FULL_DATABASE IMP_FULL_DATABASE GATHER_SYSTEM_STATISTICS SCHEDULER_ADMIN WM_ADMIN_ROLE JAVA_ADMIN JAVA_DEPLOY XDBADMIN OLAP_DBA 15 rows selected.