How to Create Roles in Oracle 12c - dummies

How to Create Roles in Oracle 12c

By Chris Ruel, Michael Wessler

You can group privileges with database roles for ease of management with Oracle 12c. Instead of an object owner individually granting privileges to one or more users with similar job descriptions, the object owner can create a role and grant the role instead.

For example, say you’re a database administrator (DBA) for a major retailer. Every day, new store clerks are hired. The application allows them to do dozens of requirements, including

  • INSERT into the SALES table

  • UPDATE the INVENTORY table

  • DELETE from the ORDERS table

Follow these steps to use a role to grant privileges:

  1. Log in to SQL*Plus as HR.

  2. Type the following:

    <CREATE ROLE sales_clerk;>

    This role is called SALES_CLERK, and you see this:

    Role created.
  3. Grant system and object privileges to the role:

    <GRANT INSERT ON sales TO sales_clerk;>
    <GRANT UPDATE ON inventory TO sales_clerk;>
    <GRANT DELETE ON orders TO sales_clerk;>

    And so on.

  4. Grant the role to the employees:

    <GRANT sales_clerk TO rob, nora, dan;>

    The role is granted to new clerks ROB, NORA, and DAN. You see this:

    Grant Succeeded.

Another nice thing about roles is dynamic privilege management, where adding and removing privileges from a role immediately affects all users who have the role.

All users need special access during a certain time (a few months, for example), to be able to SELECT from the INVENTORY table. Instead of granting it to possibly hundreds of clerks, grant the role and they will automatically have it. It makes managing privileges much easier.

Oracle-supplied roles

Some roles come already created and set up by the database, making it easier to manage certain tasks.

Here are some of the many roles supplied by Oracle when the database is installed:

  • CONNECT includes the privileges needed to connect to the database.

  • RESOURCE includes many of the roles a developer might use to create and manage an application, such as creating and altering many types of objects including tables, view, and sequences.

  • EXP_FULL_DATABASE/IMP_FULL_DATABASE allows the grantee to do logical backups of the database.

  • RECOVERY_CATALOG_OWNER allows grantee to administer Oracle Recovery Manager catalog.

  • SCHEDULER_ADMIN allows the grantee to manage the Oracle job scheduler.

  • DBA gives a user most of the major privileges required to administer a database. These privileges can manage users, security, space, system parameters, and backups.

The SYSDBA role in Oracle 12c

SYSDBA is the top dog of all roles. Anyone with this role can do anything they want in the database. Obviously you want to be careful with some of these. For example, be very particular about whom, if anyone, you give the SYSDBA role.

Those users should be fully trained, qualified Oracle administrators. If they are not, they could irreparably damage your database. Also, if too many people have this role, it destroys the chain of accountability in the database.

Oracle-supplied roles are managed just like the roles you create.