Example: Granting Privileges and Roles to a User Account

You can use Oracle Enterprise Manager Database Express (EM Express) to grant privileges and roles to a user account. For example, suppose you are creating or modifying a user account named NICK for an application developer named Nick. Because Nick is a database application developer, you will grant NICK the APPDEV role, which enables him to create database objects in his own schema (you created the APPDEV role in "Example: Creating a Role"). You also want him to be able to connect to the database, so you will grant him the CREATE SESSION system privilege. 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 that is provided with Oracle Database, so you will grant him the SELECT object privilege for all the tables in the hr sample schema. The sample schemas that are provided by Oracle Database include fictitious data that is intended to be used for example and demonstration purposes, so granting NICK access to the hr sample schema provided by Oracle Database does not grant him access to any sensitive data. The following table summarizes the privileges and roles that will be granted to NICK.

Grant Type Privilege or Role Name

Role

APPDEV

System privilege

CREATE SESSION

Object privilege

SELECT on all tables in the hr sample schema provided with Oracle Database

The following example assumes that you are in the process of creating the user account for Nick. 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. On the Privilege page in EM Express, find and select the APPDEV role and the CREATE SESSION system privilege in the available system privileges and roles table on the left, and use the right arrow button to move them to the selected system privileges and roles table on the right.

    Description of the illustration exp_create_user_role_privs.gif follows
    Description of the illustration exp_create_user_role_privs.gif

  2. Click OK.

    A confirmation message appears, and user NICK is created.

  3. Go to the View User page for user NICK, as described in "Viewing User Accounts".

  4. Click the Object Privileges subtab.

    The Object Privileges subpage appears.

  5. Click the Grant button.

    The Grant Object Privileges wizard appears, with the Select Schema and Object Type page displayed.

    Description of the illustration exp_grant_obj_priv_sch.gif follows
    Description of the illustration exp_grant_obj_priv_sch.gif

  6. In the Schema list, select HR, and in the Object Type list, select Tables.

    In this example, user NICK is being granted the SELECT object privilege for all the tables in the hr sample schema provided by Oracle Database, which contains fictitious data intended for example and demonstration purposes. He is not being granted access to any sensitive data.

  7. Click the right arrow button.

    The Select Objects page appears.

  8. Move all the tables from the available objects table on the left to the selected objects table on the right to make those tables available to user NICK.

  9. Click the right arrow button.

    The Grant Object Privileges page appears.

  10. Select the SELECT privilege in the Privilege list to grant NICK the SELECT privilege for all of the tables in the hr sample schema provided by Oracle Database.

  11. Click OK to save the new object privilege grants.

    A confirmation message appears.

See Also: