Oracle7 Server SQL Reference

CREATE USER

Purpose

To create a database user, or an account through which you can log in to the database, and establish the means by which Oracle7 permits access by the user. You can optionally assign the following properties to the user:

default tablespace

temporary tablespace

quotas for allocating space in tablespaces

profile containing resource limits

Prerequisites

You must have CREATE USER system privilege.

If you are using Trusted Oracle7 in DBMS MAC mode, you must meet additional prerequisites to perform the optional assignments of this statement:

To assign a default or temporary tablespace, your DBMS label must dominate the tablespace's creation label.

To assign a profile, your DBMS label must dominate the profile's creation label.

Syntax

user Keywords and Parameters

is the name of the user to be created. This name can only contain characters from your database character set and must follow the rules described in the section "Object Naming on Rule" . It is recommended that the user contain at least one single-byte character regardless of whether the database character set also contains multi-byte characters.

IDENTIFIED

indicates how Oracle7 permits user access:

BY password The user must specify this password to logon. Password must follow the rules described in the section "Object Naming Rules" and can only contain single-byte characters from your database character set regardless of whether this character set also contains multi-byte characters.

EXTERNALLY Oracle7 verifies that the operating system username matches the database username specified in a database connection.

DEFAULT TABLESPACE

identifies the default tablespace for objects that the user creates. If you omit this clause, objects default to the SYSTEM tablespace.

TEMPORARY TABLESPACE

identifies the tablespace for the user's temporary segments. If you omit this clause, temporary segments default to the SYSTEM tablespace.

QUOTA

allows the user to allocate space in the tablespace and optionally establishes a quota of integer bytes. This quota is the maximum space in the tablespace the user can allocate. You can also use the K or M to specify the quota in kilobytes or megabytes.

Note that a CREATE USER command can have multiple QUOTA clauses for multiple tablespaces.

UNLIMITED allows the user to allocate space in the tablespace without bound.

PROFILE

reassigns the profile named profile to the user. The profile limits the amount of database resources the user can use. If you omit this clause, Oracle7 assigns the DEFAULT profile to the user.

Usage Notes

If you create a new user in Trusted Oracle7, the user's creation label is your DBMS label.

Verifying Users Through Your Operating System

Using CREATE USER ... INDENTIFIED EXTERNALLY allows a database administrator to create a database user that can only be accessed from a specific operating system account. During a database connection, Oracle7 verifies that the operating system username matches the specified database username (prefixed by the value of the initialization parameter OS_AUTHENT_PREFIX). Effectively, you are relying on the login authentication of the operating system to ensure that a specific operating system user has access to a specific database user. Thus, the effective security of such database accounts is dependent entirely on the strength of the operating security mechanisms. For more information, see the Oracle7 Server Administrator's Guide.

Oracle Corporation strongly recommends that you do not use IDENTIFIED EXTERNALLY with operating systems that have inherently weak login security.

Establishing Tablespace Quotas for Users

To create an object or a temporary segment, the user must allocate space in some tablespace. To allow the user to allocate space, use the QUOTA clause . A CREATE USER statement can have multiple QUOTA clauses , each for a different tablespace. Other clauses can appear only once.

Note that you need not have a quota on a tablespace to establish a quota for another user on that tablespace.

Granting Privileges to a User

For a user to perform any database operation, the user's privilege domain must contain a privilege that authorizes that operation. A user's privilege domain contains all privileges granted to the user and all privileges in the privilege domains of the user's enabled roles. When you create a user with the CREATE USER command, the user's privilege domain is empty.

Note: To logon to Oracle7, a user must have CREATE SESSION system privilege. After creating a user, you should grant the user this privilege.

Example I

You can create the user SIDNEY by issuing the following statement:

CREATE USER sidney 
	IDENTIFIED BY carton 
	DEFAULT TABLESPACE cases_ts 
	QUOTA 10M ON cases_ts 
	QUOTA 5M ON temp_ts 
	QUOTA 5M ON system 
	PROFILE engineer

The user SIDNEY has the following characteristics:

the password CARTON

default tablespace CASES_TS, with a quota of 10 megabytes

temporary tablespace TEMP_TS, with a quota of 5 megabytes

access to the tablespace SYSTEM, with a quota of 5 megabytes

limits on database resources defined by the profile ENGINEER

Example II

To create a user accessible only by the operating system account GEORGE, prefix GEORGE by the value of the initialization parameter OS_AUTHENT_PREFIX . For example, if this value is "OPS$", you can create the user OPS$GEORGE with the following statement:

CREATE USER ops$george

	IDENTIFIED EXTERNALLY

	DEFAULT TABLESPACE accs_ts

	TEMPORARY TABLESPACE temp_ts

	QUOTA UNLIMITED ON accs_ts

 	QUOTA UNLIMITED ON temp_ts

The user OPS$GEORGE has the following additional characteristics:

default tablespace ACCS_TS

default temporary tablespace TEMP_TS

unlimited space on the tablespaces ACCS_TS and TEMP_TS

limits on database resources defined by the DEFAULT profile