Database Reference
In-Depth Information
Roles not included in the SET ROLE command become disabled. For
example, let's say you have three roles enabled by default (VIEWMUSIC,
UPDATEMUSIC, and DELETEMUSIC) and one role (INSERTMUSIC)
disabled by default. If the command SET ROLE INSERTMUSIC is exe-
cuted, you will enable the INSERTMUSIC role and disable the VIEW-
MUSIC, UPDATEMUSIC, and DELETEMUSIC roles. Oracle Database
10
g
provides some predefined roles you can use if you wish. There are many
predefined roles. Some of them are listed as follows:
CONNECT
. System privileges needed to log on and work as a data-
base developer. Privileges include CREATE TABLE, CREATE
VIEW, CREATE SESSION, CREATE CLUSTER, and so on. Each
operating system has a slightly different group of privileges, but gen-
erally, you have all you need to do basic database work.
RESOURCE
. System privileges needed for other database develop-
ment, such as creating types. Privileges include CREATE TYPE and
CREATE PROCEDURE. Like the CONNECT role, the exact priv-
ileges vary from system to system.
SELECT_CATALOG_ROLE
. Allows access to data dictionary
metadata and performance views, the catalog.
Use these to help you get started in administering your database. Oracle
recommends, however, that you study the underlying privileges and create
your own roles for most tasks. The CONNECT and RESOURCE roles
may not be created automatically in future releases of Oracle.
23.3.4
Dropping Roles
This final section on roles involves removing roles. Whenever you remove a
role, it is revoked from all users who currently have the role. Syntax for the
DROP ROLE command is shown in Figure 23.17.
Roles are an excellent way to consolidate privileges needed for running
applications.
Figure 23.17
Dropping a Role
Also Revokes the
Role from Users.
