Using roles in Oracle 10g

Learning objective

After completing this topic, you should be able to recognize how to create a role and assign it to a user.

1. Using roles

Instead of granting privileges to users on an individual basis, Oracle lets you manage privileges by applying them to roles.

A role is the establishment of a group of privileges under a single name, which you can then assign to users or other roles.

With roles, the administration of privileges is easier, and it's also more secure, because it involves less potential human error than assigning privileges individually.

Roles have several characteristics:

• users and privileges
• they can be enabled and disabled
• they may require passwords
• they are not owned

users and privileges

The granting and revoking of privileges to a role is done in the same way as it is to a user. You can also grant and revoke roles to and from a user in the same way as system privileges. Both object and system privileges can be used in a role.

they can be enabled and disabled

Each user can enable or disable a role when they are assigned that role.

they may require passwords

You can use a password to enable a role.

they are not owned

Roles are not owned by a user, nor are they part of the user's schema. They are not owned by anyone.

There are several benefits of using roles:

• easier privilege management
• dynamic privilege management
• selective availability of privileges
• granting through the operating system

easier privilege management

Roles provide easier privilege management because they let you assign a privilege to a role instead of an individual user. That way the privilege only needs to be assigned once. The role can then be assigned to each user.

dynamic privilege management

Roles provide dynamic privilege management when you change the privileges in a role. Such a change automatically updates the privileges of every user assigned to that role.

selective availability of privileges

Roles provide you with the selective availability of privileges option. This allows you to turn privileges on and off temporarily by enabling or disabling a role.

When enabling a role, you can verify that a particular user has been granted that role.

granting through the operating system

Roles can be granted to the users in the database using operating system commands or utilities.

Question

Which of these are advantages of using roles?

Options:

1. Dynamic privilege management
   
2. Easier privilege management
3. Static privilege management
4. Selective availability of privileges

Answer

Roles have the advantage of providing easy and dynamic privilege management, while also allowing you to select the availability of privileges.

Option 1 is correct. You can change the privilege in a role and know that through dynamic privilege management the change will pass to the user immediately and automatically.

Option 2 is correct. Instead of providing each individual user with their own set of privileges, you grant the privileges to a role. That role is then assigned to each user.

Option 3 is incorrect. Roles enable you to modify the privileges in a role and know that the changes will be passed dynamically to users in that role.

Option 4 is correct. You can enable and disable roles, which gives you the opportunity to turn privileges on and off temporarily.

2. Creating roles

Privileges are granted to roles, and the user is then provided with the privileges by being assigned to the role.

For example: in an HR department the HR_CLERK role is first granted the privilege to UPDATE and SELECT the EMPLOYEES table.

The HR_MANAGER role is then granted the privileges DELETE and INSERT over the EMPLOYEES table and the HR_CLERK role.

The HR_MANAGER role is then assigned to the manager, who uses it to select, delete, insert, and update the EMPLOYEES table.

To create a role, you first select Roles in the Security section of the Administration tabbed page in Enterprise Manager.

The Roles page contains a search function and a Results table listing the roles that have already been created.

You now want to create a new role.

You click Create at the top of the Results table.

The Create Role page contains several tabbed sections.

You can grant system and object privileges to the role through the System Privileges and Object Privileges sections.

However, first you must provide a name for the role in the Name field. It is HR_MANAGER in this case.

To create the role you click OK.

When the role has been created successfully, an update message appears at the top of the screen.

Question

You want to create a role that can be used to grant administrators in the HR department rights to update several HR-related tables.

This task requires you to create the role HR_CLERK. Which of these options allows you to complete this task?

Options:

1. You click Roles in the Security section of the Administration tabbed page. You then click Create in the Roles page. In the Create Role page you type HR_CLERK in the Name field and click OK.
2. You click Users in the Security section of the Administration tabbed page. You then click Create in the Roles page. In the Create Role page you type HR_CLERK in the Name field and click OK.

Answer

You click Roles in the Security section of the Administration tabbed page. You then click Create in the Roles page. In the Create Role page you type HR_CLERK in the Name field and click OK.

3. Assigning a role

The fact that a role consists of a set of privileges that can be granted both to users or other roles helps in the administration of database privileges.

You can add the privilege to the role, and then assign the role to any number of users.

The user can then exercise the privilege by enabling the role.

A role contains all the privileges granted to it, including the privileges of other roles granted to it.

Enterprise Manager grants the CONNECT role to new users, by default.

The CONNECT role enables users to connect to the database and create database objects.

Suppose you want to assign Andrew Chung to the HR_MANAGER role.

To assign a user to a role, you first click Users in the Security section of the Administration tabbed page.

In the Users page, you now want to open the user's account so you can assign the role.

You click the Select option beside Andrew Chung and click Edit.

In the Edit User page there are several tabbed links.

You click Roles to assign a role.

In the Roles tabbed page, the default CONNECT role appears in the Role table.

To assign a new role, you click Modify.

The Modify Roles page is made up of two list boxes: Available Roles and Selected Roles.

Available Roles lists all roles in the database.

You now want to select the role to be assigned to the user Andrew Chung.

You select HR_MANAGER in the Available Roles list box and click Move.

The HR_MANAGER role now appears in the Selected Roles list box.

You click OK to complete the selection.

The HR_MANAGER role now appears in the Role table.

Question

You have been asked to give Andrea Horner a role that will allow her to select and update the EMPLOYEES table only.

This task requires you to assign the HR_CLERK role to Andrea Horner. Which of these options allows you to complete this task?

Options:

1. You click Roles in the Edit User page. Then you click Create in the Roles tabbed page. You select HR_CLERK in the Select Roles list box and click Move. Finally you click OK.
2. You click Roles in the Edit User page. Then you click Modify in the Roles tabbed page. You select HR_CLERK in the Available Roles list box and click Move. Finally you click OK.

Answer

You click Roles in the Edit User page. Then you click Modify in the Roles tabbed page. You select HR_CLERK in the Available Roles list box and click Move. Finally you click OK.

4. Using predefined and secure roles

There are several roles defined automatically for Oracle databases when you run database creation scripts.

The roles Oracle defines automatically are

• CONNECT
  
• DBA
  
• RESOURCE
  
• SCHEDULER_ADMIN
  
• SELECT_CATALOG_ROLE

CONNECT

The CONNECT role is assigned automatically in Enterprise Manager when a new user is created.

DBA

The DBA role consists of almost all of the Oracle privileges. For this reason, nonadministrators should not be granted the DBA role.

RESOURCE

The RESOURCE role allows a user to create tables, procedures, sequences, triggers, types, clusters, index types, and operators in their own schema.

SCHEDULER_ADMIN

The SCHEDULER_ADMIN role allows a user to create jobs in their own or another user's schema. They can also execute classes and programs across schema lines, and manage the scheduler.

SELECT_CATALOG_ROLE

With the SELECT_CATALOG_ROLE, the user can access data dictionary views and packages, although the SELECT_ANY_DICTIONARY system privilege is more effective for this.

Question

Match the predefined roles to their characteristics.

Options:

1. CONNECT
2. DBA
3. RESOURCE
4. SCHEDULER_ADMIN

Targets:

1. Assigned automatically when a new user is created
2. Allows a user to create tables, procedures, and sequences
3. Allows a user to create jobs in their own or in another user's schema
4. Contains almost all Oracle privileges

Answer

The CONNECT role is assigned automatically when a new user is created, and the RESOURCE role allows a user to create tables, procedures, and sequences. The SCHEDULER_ADMIN role allows a user to create jobs in their own or in another user's schema, and the DBA role contains almost Oracle's privileges.

When a new users are created in Enterprise Manager, they are automatically given the CONNECT role.

As the DBA role provides almost all of Oracle's privileges, you should not grant the role to nonadministrators.

With the RESOURCE role, the user can also create triggers, types, clusters, index types, and operators.

With the SCHEDULER_ADMIN role, the user can also execute classes and programs across schema lines, and manage the scheduler.

When certain functionality is installed in Oracle Database 10g, you are automatically provided with roles that authorize you to administer those functions.

For example, if an XML database is installed, you are provided with the XDBADMIN role, whose privileges allow you to administer the database.

If you need to administer advanced queuing, you are provided with the AQ_ADMINISTRATOR_ROLE.

Similarly, if you need to administer heterogeneous services, you use the HS_ADMIN_ROLE with its associated privileges.

To avoid disabling some needed functionality by mistake, you should not alter the privileges granted to these roles without assistance from Oracle Support.

When a user enables a role, they have the right to exercise the privileges granted to that role.

This level of access may lay the database open to errors created by the user. However, there are ways that you can make the role more secure and control the use of privileges.

The ways of making roles more secure include

• creating nondefault roles
• requiring additional authentication
• using secure application roles

creating nondefault roles

Making a role nondefault forces the user to explicitly enable the role before the privileges granted to it can be exercised. You do this by deselecting the DEFAULT checkbox when granting the role to a user.

requiring additional authentication

When creating a role, you can have it require additional authentication before it can be set. NONE is the default authentication setting for a role. However, you can apply a password, or enforce Global or External authentication.

using secure application roles

Secure application roles can only be enabled when a PL/SQL procedure is executed successfully. With the PL/SQL procedure, you can ensure that a group of permissions is secure by checking items - for example, which program the user is running, the user's network address, or the time of day.

Question

Which of these are ways of making roles more secure?

Options:

1. Using additional authentication
2. Using Internal Authentication
3. Using nondefault roles
4. Using secure application roles

Answer

Additional authentication, nondefault roles, and secure application roles are all ways of making roles more secure.

Option 1 is correct. Providing additional authentication when creating a role forces a user to provide a password or external or global authentication before being able to exercise a privilege.

Option 2 is incorrect. Providing additional authentication techniques is one way of making a role more secure. However, the only authentication methods available in Oracle databases are Password, Global, and External.

Correct. By deselecting the DEFAULT checkbox when the role is granted, you require the user to explicitly enable a role before exercising a privilege.

Option 4 is correct. With secure application roles, the role only be enabled when a PL/SQL procedure is executed successfully.

Summary

A role consists of a group of privileges under a single name. Granting privileges to roles reduces the administration of privileges in Oracle Database 10g. Instead of granting them to individual users, you grant them to roles, which you then assign to each user. Roles have other benefits, including being able to manage privileges dynamically.

When a user is created in Enterprise Manager, they are given the CONNECT role automatically. However, you must assign the user to any additional roles. Roles can also be granted to roles. Besides containing all its own privileges, a role acquires all the privileges of other roles granted to it.

Each Oracle database contains a number of predefined roles that have been provided with privileges to let you make changes immediately. When certain functionality is installed, roles are provided with it that let you administer the function. You can also secure roles by making them nondefault, by adding authentication or by using secure application roles.