Oracle7 Server SQL Reference

CREATE ROLE

Purpose

To create a role. A role is a set of privileges that can be granted to users or to other roles.

Prerequisites

You must have CREATE ROLE system privilege.

Syntax

role Keywords and Parameters

is the name of the role to be created. It is recommended that the role contain at least one single-byte character regardless of whether the database character set also contains multi-byte characters. NOT IDENTIFIED

indicates that a user granted the role need not be verified when enabling it.

IDENTIFIED

indicates that a user granted the role must be verified when enabling it with the SET ROLE command:

BY password The user must specify the password to Oracle7 when enabling the role. The password can only contain single-byte characters from your database character set regardless of whether this character set also contains multi-byte characters.

EXTERNALLY The operating system verifies the user enabling to the role. Depending on the operating system, the user may have to specify a password to the operating system when enabling the role.

If you omit both the NOT IDENTIFIED option and the IDENTIFIED clause, the role defaults to NOT IDENTIFIED.

Usage Notes

In Trusted Oracle7, the new role is automatically labeled with your DBMS label.

Using Roles

A role is a set of privileges that can be granted to users or to other roles. You can use roles to administer database privileges. You can add privileges to a role's privilege domain and then grant the role to a user. The user can then enable the role and exercise the privileges in the role's privilege domain. For information on enabling roles, see the ALTER USER command

A role's privilege domain contains all privileges granted to the role and all privileges in the privilege domains of the other roles granted to it. A new role's privilege domain is initially empty. You can add privileges to a role's privilege domain with the GRANT command.

When you create a role, Oracle7 grants you the role with ADMIN OPTION. The ADMIN OPTION allows you to perform the following operations:

grant the role to another user or role

revoke the role from another user or role

alter the role to change the authorization needed to access it

drop the role

Roles Defined by Oracle7

Some roles are defined by SQL scripts provided on your distribution media. The following roles are predefined:

CONNECT

RESOURCE

EXP_FULL_DATABASE

IMP_FULL_DATABASE

The CONNECT, RESOURCE, and DBA roles are provided for compatibility with previous versions of Oracle7. You should not rely on these roles, rather, it is recommended that you to design your own roles for database security. These roles may not be created automatically by future versions of Oracle7.

The EXP_FULL_DATABASE and IMP_FULL_DATABASE roles are provided for convenience in using the Import and Export utilities.

For more information on these roles, see Table 4 - 12.

Oracle7 also creates other roles that authorize you to administer the database. On many operating systems, these roles are called OSOPER and OSDBA. Their names may be different on your operating system.

Example

The following statement creates the role TELLER:

CREATE ROLE teller 
	IDENTIFIED BY cashflow

Users who are subsequently granted the TELLER role must specify the passwords CASHFLOW to enable the role.