Roles

This section is of no use to Oracle6 DBAs (sorry!). Roles make the database administrator’s security job much easier, but you have to have Oracle7 or 8 to get this benefit. Let’s hope that the remaining version 6 DBAs can convert their applications to version 8 soon so that they can take advantage of this and other new features.

Roles are an incredibly simple and powerful concept for the DBA. When databases were small and accessed by a limited number of individuals, it was acceptable to grant a specific access to each table a user required. However, as databases grew and the number of users grew with them, this became a nightmare of maintenance. Operating systems have used the concepts of groups for years to give file access to individuals based on their job descriptions. In fact, many business organizations determine what access individuals have to a particular data set by their job descriptions (payroll clerk, accounts receivable manager, and so forth) The Oracle role accommodates this real-world privilege scheme by allowing the DBA to assign privileges to users based on a series of roles the DBA can define to the database.

The concept of roles is relatively straight forward. The real trick for the DBA is to map out a scheme of roles that enables users to get their jobs done and is maintainable. This topic is covered later in this chapter. The first step in the process is to create the roles you need with this SQL command:

create role role-name;

For a role to be useful, it needs privileges to be granted to it and the role granted to users. After the user is created, you grant privileges to the role using the grant command discussed in the last section. You then grant access to users to the role by using that same grant command (where the role name is the privilege.) For those of you familiar with object-oriented concepts, the users inherit the privileges of the role when they are assigned to the role. This is a dynamic relationship. If you assign users to roles and later change the privileges assigned to the roles, the users acquire those new privileges when they next connect to Oracle. The following shows the format of a role grant:

Grant role to user;

Perhaps a concrete example would be beneficial here. Let’s create a role called developer for software developers. In this world, the developers are allowed to create and modify packages, views, and tables. They need the ability to connect to the system, but do not need any other special privileges. Let’s also add a user with ID bsmith to the system and this group. The following example shows a sample SQL*Plus session the DBA would run to set up this plan:

SQL> create user bsmith identified by brad;



User created.



SQL> create role developer;



Role created

SQL> grant create procedure,create table,create view,create session to 

developer;



Grant succeeded.



SQL> grant developer to bsmith;



Grant succeeded.



SQL>

With the basics of roles out of the way, there are a few other topics a DBA should understand to get the most out of roles. The first is the concept of default roles. When you grant a role, it is a default role, one users have active whenever they connect to the Oracle instance. You can establish certain user’s roles as default roles. The nondefault roles require an overt act on the part of the user to obtain his or her privileges. To establish a specific list of default roles, you issue this command:

alter user userID default role list-of-roles;

Here is an example:

alter user bsmith default role manager,developer;

If users want to access roles other than their default roles, they have to issue a command of the format

set role list-of-roles;

This is a somewhat tricky command. When you provide the list of roles, it does not add to the roles you already have in place. Instead, it becomes the list of roles that are enabled. Any roles that are not in this list become disabled, including default roles. The user can still access privileges from this role by issuing another set role command.

If you think of a role as a bucket for privileges, it is important to understand what that bucket can contain. First, it can contain most object privileges. It can also contain system privileges. Finally, it can contain other roles. I normally do not recommend having a hierarchy of roles. It may make it easier to grant the role privileges by building this hierarchy, but you can lose track of the individual privileges granted and accidentally grant an inappropriate privilege to a certain user. However, there are DBAs who are quite comfortable with using a role hierarchy, first building fundamental roles and then building higher-level roles that are made up of the fundamental roles. In the end, you have to choose whichever method works best for you.

It is sometimes useful to apply passwords to various roles. Users will not be challenged for this password if this is one of their default roles. However, if you set it up so that this is not a default role, they will be challenged for a password when they attempt to access that role. This is a way to implement some password security for special functions even when using automatic login accounts (OPS$), which are explained later when you explore user administration. The following is an example of a role that is created with a password:

create role developer identified by birdseye;

If you can create something, you can also remove what you have created. Perhaps there have been major changes to the application and a role is being split into several roles, each with privileges in different areas of the application. You want to create the new roles and then drop the old role that had all the privileges. The command to perform this is the drop role command:

SQL> drop role developer;



Role dropped.



SQL>

Remember that you can still grant privileges to individual users when you are using roles. For example, if there is a common privilege set for all finance department managers with the exception of Susan, who can also define the list of valid budget categories, you can assign all the basic privileges to the finance_manager role and assign all finance department managers including Susan to this role. You can then perform separate grants to Susan to perform her special job functions. I always prefer to keep things simple, but if you have a special need, remember that this capability is available.