Example: Granting Privileges and Roles to a User Account

Suppose you are creating or modifying a user account named Nick. Because Nick is a database application developer, you want to grant him the APPDEV role, which enables him to create database objects in his own schema. (You created the APPDEV role in "Example: Creating a Role".) Because you want Nick to be able to create tables and views in other schemas besides his own, you want to grant him the CREATE ANY TABLE and CREATE ANY VIEW system privileges. In addition, because he is developing a human resources application, you want him to be able to view the tables in the hr sample schema and use them as examples. You therefore want to grant him the SELECT object privilege on those tables. Finally, you want Nick to be able to log in to Database Control so that he can use the graphical user interface to create and manage his database objects. You therefore want to grant him the SELECT_CATALOG_ROLE role. The following table summarizes the privileges and roles to grant to Nick.

The following example assumes that you are in the process of creating the user account for Nick or editing the account. Either you have accessed the Create User page and have entered all required fields on the General subpage (see "Example: Creating a User Account"), or you have accessed the Edit User page for Nick (see "Example: Modifying a User Account"). The example also assumes that you have not yet granted any privileges or roles to Nick.

To grant privileges and roles to the user Nick: 

1. Toward the top of the Create User or Edit User page, click Roles to display the Roles subpage.

   The Roles subpage shows that the CONNECT role is assigned to Nick. Database Control automatically assigns this role to all users that you create. (The selected Default check box indicates that the CONNECT role is a default role for Nick, which means that it is automatically enabled whenever Nick logs in.)

2. Click Edit List.

   The Modify Roles page appears.

3. In the Available Roles list, locate the APPDEV role, and double-click it to add it to the Selected Roles list. Do the same with the SELECT_CATALOG_ROLE role and then click OK.

   The Create User or Edit User page returns, showing that the CONNECT, APPDEV, and SELECT_CATALOG_ROLE roles are granted to Nick.

   Note: Double-clicking a role is a shortcut. You can also select the role 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.

4. Toward the top of the page, click System Privileges to select the System Privileges subpage.

5. Click Edit List.

   The Modify System Privileges page appears.

6. In the Available System Privileges list, scroll to locate the CREATE ANY TABLE and CREATE ANY VIEW privileges, double-click each to add them to the Selected System Privileges list, and then click OK.

   The Create User or Edit User page returns, showing the newly added system privileges.

   Note: To revoke a role, double-click it in the Selected Roles list on the Modify Roles page. To revoke a system privilege, double-click it in the Selected System Privileges list on the Modify System Privileges page.

7. Toward the top of the page, click Object Privileges to select the Object Privileges subpage.

8. In the Select Object Type list, select Table and then click Add.

   The Add Table Object Privileges page appears.

9. Click the flashlight icon next to the Select Table Objects list.

   The Select Table Objects dialog box appears.

10. In the Schema list, select HR, and then click Go.

    All tables in the hr schema are displayed.

11. Click Select All, and then click the Select button.

    The Select Table Objects dialog box closes, and the names of all tables in the hr schema appear in the Select Table Objects field on the Add Table Object Privileges page.

12. In the Available Privileges list, double-click the SELECT privilege to move it to the Selected Privileges list, and then click OK.

    The Create User or Edit User page returns, showing that the SELECT object privilege for all hr tables is granted to user Nick.

    Note: To revoke an object privilege, select it on the Create User or Edit User page (Object Privileges subpage), and then click Delete.

13. Do one of the following to save the role and privilege grants:

    • If you are creating a user account, then click OK to save the new user account.

    • If you are modifying a user account, then click Apply to save the changes for the user account.