Skip Headers
Oracle® Database 2 Day DBA
11g Release 1 (11.1)

Part Number B28301-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Administering Roles

Roles are named groups of related system and object privileges. You create roles and then assign them to users and to other roles.

This section contains the following topics:

See Also:

Viewing Roles

You view roles on the Roles page of Oracle Enterprise Manager Database Control (Database Control).

To view roles:

  1. Go to the Database Home page, logging in with a user account that has privileges to manage roles. An example of such a user account is SYSTEM.

    See "Accessing the Database Home Page".

  2. At the top of the page, click Server to view the Server subpage.

  3. In the Security section, click Roles.

    The Roles page appears.

    Description of roles.gif follows
    Description of the illustration roles.gif

  4. If you want to view the details of a particular role, then in the Select column, select the name of the role you want to view, and then click View.

    If you do not see the role that you want to view, it may be on another page. In this case, do one of the following:

    • Just above the list of roles, click Next to view the next page. Continue clicking Next until you see the desired role.

    • Use the Search area of the page to search for the desired role. In the Object Name field, enter the first few letters of the role, and then click Go.

      You can then select the role and click View.

    The View Role page appears. In this page, you can see all the privileges and roles granted to the selected role.

Example: Creating a Role

Suppose you want to create a role called APPDEV for application developers. Because application developers must be able to create, modify, and delete the schema objects that their applications use, you want the APPDEV role to include the system privileges shown in Table 7-2.

Table 7-2 System Privileges Granted to the APPDEV Role

Privilege Description

CREATE TABLE

Enables a user to create, modify, and delete tables in his schema.

CREATE VIEW

Enables a user to create, modify, and delete views in his schema.

CREATE PROCEDURE

Enables a user to create, modify, and delete procedures in his schema.

CREATE TRIGGER

Enables a user to create, modify, and delete triggers in his schema.

CREATE SEQUENCE

Enables a user to create, modify, and delete sequences in his schema.

CREATE SYNONYM

Enables a user to create, modify, and delete synonyms in his schema.


To create the APPDEV role:

  1. Go to the Roles page, as described in "Viewing Roles".

  2. Click Create.

    The Create Role page appears.

  3. In the Name field, enter APPDEV.

  4. Click System Privileges to go to the System Privileges subpage.

    Description of create_role.gif follows
    Description of the illustration create_role.gif

    The table of system privileges for this role contains no rows yet.

  5. Click Edit List.

    The Modify System Privileges page appears.

  6. In the Available System Privileges list, double-click privileges to add them to the Selected System Privileges list. The privileges to add are listed in table Table 7-2.

    Description of modify_system_privs.gif follows
    Description of the illustration modify_system_privs.gif

    Note:

    Double-clicking a privilege is a shortcut. You can also select a privilege and then click the Move button. To select multiple privileges, hold down the Shift key while selecting a range of privileges, or press the Ctrl key and select individual privileges, then click Move after you have selected the privileges.
  7. Click OK.

    The System Privileges subpage returns, showing the system privileges that you selected. At this point, you could click Roles to assign other roles to the APPDEV role, or click Object Privileges to assign object privileges to the APPDEV role.

  8. Click OK to return to the Roles page.

    The APPDEV role now appears in the table of database roles.

Example: Modifying a Role

Suppose your applications make use of Oracle Streams Advanced Queuing, and you determine that developers need to be granted the roles AQ_ADMINISTRATOR_ROLE and AQ_USER_ROLE to develop and test their applications. You must edit the APPDEV role to grant it these two Advanced Queuing roles.

To modify the APPDEV role:

  1. Go to the Roles page, as described in "Viewing Roles".

  2. In the Select column, click APPDEV role, and then click Edit.

    The Edit Role page appears.

  3. Click Roles to navigate to the Roles subpage.

  4. Click Edit List.

    The Modify Roles page appears.

  5. In the Available Roles list, double-click the roles AQ_ADMINISTRATOR_ROLE and AQ_USER_ROLE to add them to the Selected Roles list.

  6. Click OK.

    The Roles subpage returns, showing that the roles that you selected were granted to the APPDEV role.

  7. Click Apply to save your changes.

    An update message appears, indicating that the role APPDEV was modified successfully.

Deleting a Role

You must use caution when deleting a role, because Database Control deletes a role even if that role is currently granted to one or more users. Before deleting a role, you may want to determine if the role has any grantees. Dropping (deleting) a role automatically removes the privileges associated with that role from all users that had been granted the role.

To determine if a role has any grantees:

  1. Go to the Roles page as described in "Viewing Roles".

  2. In the Select column, click the desired role.

    If you do not see the desired role, it may be on another page. In this case, do one of the following:

    • Just above the list of roles, click Next to view the next page. Continue clicking Next until you see the desired role.

    • Use the Search area of the page to search for the desired role. In the Object Name field, enter the first few letters of the role, and then click Go.

    You can then select the role.

  3. In the Actions list, select Show Grantees, and then click Go.

    A report appears, listing the users that are granted the selected role.

  4. Click Cancel to return to the Roles page.

To delete a role:

  1. If you are not already there, go to the Roles page, as described in "Viewing Roles".

  2. In the Select column, click the desired role, and then click Delete.

    A confirmation page appears.

  3. Click Yes.

    A confirmation message indicates that the role has been deleted successfully.