Roles in Oracle - Managing Users

An ORACLE role is a set of privileges (or the type of access that each user needs depending on his or her status and responsibilities)

Roles :

There are 3 Standard Roles

- CONNECT

- RESOURCE and

- DBA

The CONNECT Role

This has Privileges as follows :

 CREATE SESSION, ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, and CREATE VIEW.

Note: Users do not have the ability to create tables or clusters unless you grant them a quota on a tablespace.

You can also create your own role and grant privileges to that role :

Let s see this..

SQL > Create role APPLICATION_USER;

SQL > Grant CREATE SESSION to APPLICATION_USER;   - - - Grant privileges to Role

SQL > Grant APPLICATION_USER to username;  - - - Grant Role to a user

The RESOURCE Role

The RESOURCE role has the following system privileges:

CREATE CLUSTER, CREATE INDEX, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE.

Users who have the RESOURCE role do not have the ability to create tables, indexes and clusters unless you first grant them a space quota in a tablespace.

Grant RESOURCE role to developers who will be creating PL/SQL objects such as procedures and triggers.

The DBA Role

The DBA role has all system privileges – including unlimited space quotas – and the ability to grant all privileges to other users

SYSTEM is for use by a DBA user

In ORACLE, the DBA is granted the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles, which in turn have privileges necessary for exporting and importing the full Oracle database

Note:

An individual with the DBA role can revoke CONNECT, RESOURCE, DBA, or any other privilege or role from anyone, including another DBA.

How to add password to a role ?

To enable security for a role, use the identified keyword in the alter role command.

alter role APPLICATION_USERS  identified by password123 ;