CREATE USER

Syntax

Purpose

To create and configure a database user, or an account through which you can log in to the database and establish the means by which Oracle permits access by the user.

Note: You can enable a user to connect to Oracle through a proxy (that is, an application or application server). For syntax and discussion, refer to "ALTER USER".

Prerequisites

You must have CREATE USER system privilege. When you create a user with the CREATE USER statement, the user's privilege domain is empty. To log on to Oracle, a user must have CREATE SESSION system privilege. Therefore, after creating a user, you should grant the user at least the CREATE SESSION privilege. See "GRANT system_privileges_and_roles".

Keywords and Parameters

user	is the name of the user to be created. This name can contain only characters from your database character set and must follow the rules described in the section "Schema Object Naming Rules". Oracle recommends that the user name contain at least one single-byte character regardless of whether the database character set also contains multi-byte characters.
IDENTIFIED	indicates how Oracle authenticates the user. See Also: Oracle8i Application Developer's Guide - Fundamentals and your operating system specific documentation for more information.
	BY password	creates a local user and indicates that the user must specify password to log on. Passwords can contain only single-byte characters from your database character set regardless of whether this character set also contains multibyte characters. Passwords must follow the rules described in the section "Schema Object Naming Rules", unless you are using Oracle's password complexity verification routine. That routine requires a more complex combination of characters than the normal naming rules permit. You implement this routine with the UTLPWDMG.SQL script, which is further described in Oracle8i Administrator's Guide. See Also: Oracle8i Administrator's Guide to for a detailed description and explanation of how to use password management and protection.
	EXTERNALLY	creates an external user and indicates that a user must be authenticated by an external service (such as an operating system or a third-party service). Doing so causes Oracle to rely on the login authentication of the operating system to ensure that a specific operating system user has access to a specific database user.
		WARNING: Oracle strongly recommends that you do not use IDENTIFIED EXTERNALLY with operating systems that have inherently weak login security. For more information, see Oracle8i Administrator's Guide.
	GLOBALLY AS 'external_name'	creates a global user and indicates that a user must be authenticated by the enterprise directory service. The 'external_name' string can take one of two forms:
		The X.509 name at the enterprise directory service that identifies this user. It should be of the form 'CN=username,other_attributes', where other_attributes is the rest of the user's distinguished name (DN) in the directory. A null string (' ') indicating that the enterprise directory service will map authenticated global users to the appropriate database schema with the appropriate roles.
		See Also: Oracle Advanced Security Administrator's Guide for more information on global users.
	Note: You can control the ability of an application server to connect as the specified user and to activate that user's roles using the ALTER USER statement. See Also: "ALTER USER"
DEFAULT TABLESPACE	identifies the default tablespace for objects that the user creates. If you omit this clause, objects default to the SYSTEM tablespace. See Also: "CREATE TABLESPACE" for more information on tablespaces.
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. Use K or M to specify the quota in kilobytes or megabytes. This quota is the maximum space in the tablespace the user can allocate.
	A CREATE USER statement 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 to the user. The profile limits the amount of database resources the user can use. If you omit this clause, Oracle assigns the DEFAULT profile to the user. See Also: "GRANT system_privileges_and_roles" and "CREATE PROFILE".
PASSWORD EXPIRE	causes the user's password to expire. This setting forces the user (or the DBA) to change the password before the user can log in to the database.
ACCOUNT LOCK	locks the user's account and disables access.
ACCOUNT UNLOCK	unlocks the user's account and enables access to the account.

Examples

If you create a new user with PASSWORD EXPIRE, the user's password must be changed before attempting to log in to the database. You can create the user SIDNEY by issuing the following statement:

CREATE USER sidney 
    IDENTIFIED BY welcome 
    DEFAULT TABLESPACE cases_ts 
    QUOTA 10M ON cases_ts 
    TEMPORARY TABLESPACE temp_ts 
    QUOTA 5M ON system 
    PROFILE engineer 
    PASSWORD EXPIRE;

The user SIDNEY has the following characteristics:

• The password WELCOME

• Default tablespace CASES_TS, with a quota of 10 megabytes

• Temporary tablespace TEMP_TS

• Access to the tablespace SYSTEM, with a quota of 5 megabytes

• Limits on database resources defined by the profile ENGINEER

• An expired password, which must be changed before SIDNEY can log in to the database

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; 

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

The following example creates user CINDY as a global user:

CREATE USER cindy 
   IDENTIFIED GLOBALLY AS 'CN=cindy,OU=division1,O=oracle,C=US'
   DEFAULT TABLESPACE legal_ts
   QUOTA 20M ON legal_ts
   PROFILE lawyer;