Example: Creating a Role

You can use Oracle Enterprise Manager Database Express (EM Express) to create a role called APPDEV for application developers in a pluggable database (PDB). 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.

Note:

If you create an APPDEV role for application developers at your company, you should follow the principle of least privilege, in which you grant to your application developers only the privileges needed to perform their job function, and no more. Therefore, the set of privileges that you grant to the APPDEV role for your company may be different than the system privileges that are granted to the APPDEV role in Table 7-2.

To create the APPDEV role:

  1. In EM Express, go to the Roles page, as described in "Viewing Roles".

  2. Click Create Role.

    The Create Role wizard appears, with the New Role page showing.

  3. In the Role Name field, enter APPDEV.

  4. Click the right arrow.

    The Privilege page appears.

    Description of the illustration exp_create_role_privs.gif follows
    Description of the illustration exp_create_role_privs.gif

    The available system privileges and roles table on the left shows the available system privileges that can be assigned to the role. Roles are identified by a check mark in the Is Role column. The selected system privileges and roles table on the right shows the system privileges and roles that are currently selected for the role. Select one or more system privileges or roles in either table, and then click the appropriate arrow button to move those privileges to the other table.

    Move the CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER, CREATE SEQUENCE, and CREATE SYNONYM system privileges to the selected system privileges and roles table for the APPDEV role that is being created.

    In the selected system privileges and roles table, enable the WITH ADMIN option for a system privilege or role if you want users who will be granted the APPDEV role you are defining to be able to grant the system privilege or role in the selected system privileges and roles table to other users.

  5. Click OK.

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

Video:

Oracle Database 12c: Creating a Role Using Oracle Enterprise Manager Database Express