Skip Headers

Oracle® Database SQL Reference
10g Release 1 (10.1)

Part Number B10759-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

CREATE USER

Purpose

Use the CREATE USER statement to create and configure a database user, which is an account through which you can log in to the database, and to establish the means by which Oracle Database permits access by the user.

You can enable a user to connect to the database through a proxy application or application server. For syntax and discussion, refer to ALTER USER .

Prerequisites

You must have the 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 Database, a user must have the CREATE SESSION system privilege. Therefore, after creating a user, you should grant the user at least the CREATE SESSION system privilege. Please refer to GRANT for more information.

Syntax


create_user::=
Description of create_user.gif follows
Description of the illustration create_user.gif

Semantics


user

Specify 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 multibyte characters.


Note:

Oracle Corporation recommends that user names and passwords be encoded in ASCII or EBCDIC characters only, depending on your platform. Please refer to Oracle Database Administrator's Guide for more information about this recommendation.


IDENTIFIED Clause

The IDENTIFIED clause lets you indicate how Oracle Database authenticates the user.


BY password

The BY password clause lets you creates a local user and indicates that the user must specify password to log on to the database. Passwords can contain only single-byte characters from your database character set regardless of whether the character set also contains multibyte characters.

Passwords must follow the rules described in the section "Schema Object Naming Rules ", unless you are using the Oracle Database 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 Oracle Database Security Guide.


Note:

Oracle Corporation recommends that user names and passwords be encoded in ASCII or EBCDIC characters only, depending on your platform. Please refer to Oracle Database Administrator's Guide for more information about this recommendation.


See Also:

Oracle Database Administrator's Guide to for a detailed discussion of password management and protection


EXTERNALLY Clause

Specify EXTERNALLY to create an external user. Such a user must be authenticated by an external service, such as an operating system or a third-party service. In this case, Oracle Database relies on the login authentication of the operating system to ensure that a specific operating system user has access to a specific database user.


Caution:

Oracle strongly recommends that you do not use IDENTIFIED EXTERNALLY with operating systems that have inherently weak login security. For more information, see Oracle Database Administrator's Guide.


GLOBALLY Clause

The GLOBALLY clause lets you create a global user. Such a user must be authenticated by the enterprise directory service. The external_name string can take one of two forms:

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.


DEFAULT TABLESPACE Clause

Specify the default tablespace for objects that the user creates. If you omit this clause, then the user's objects are stored in the database default tablespace. If no default tablespace has been specified for the database, then the user's objects are stored in the SYSTEM tablespace.


Restriction on Default Tablespaces

You cannot specify a locally managed tablespace, including an undo tablespace, or a dictionary-managed temporary tablespace as a user's default tablespace.


See Also:



TEMPORARY TABLESPACE Clause

Specify the tablespace or tablespace group for the user's temporary segments. If you omit this clause, then the user's temporary segments are stored in the database default temporary tablespace or, if none has been specified, in the SYSTEM tablespace.

Restrictions on Temporary Tablespace
  • The tablespace must be a temporary tablespace and must have a standard block size.

  • The tablespace cannot be an undo tablespace or a tablespace with automatic segment-space management.


See Also:



QUOTA Clause

Use the QUOTA clause to allow the user to allocate up to integer bytes of space in the tablespace. 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 lets the user allocate space in the tablespace without bound.


See Also:

Oracle Database Security Guide for more information on assigning tablespace quotas


PROFILE Clause

Specify the profile you want to assign to the user. The profile limits the amount of database resources the user can use. If you omit this clause, then Oracle Database assigns the DEFAULT profile to the user.


See Also:

GRANT and CREATE PROFILE


PASSWORD EXPIRE Clause

Specify PASSWORD EXPIRE if you want 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 Clause

Specify ACCOUNT LOCK to lock the user's account and disable access. Specify ACCOUNT UNLOCK to unlock the user's account and enable access to the account.

Examples

All of the following examples use the example tablespace, which exists in the seed database and is accessible to the sample schemas.


Creating a Database User: Example

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

CREATE USER sidney 
    IDENTIFIED BY out_standing1 
    DEFAULT TABLESPACE example 
    QUOTA 10M ON example 
    TEMPORARY TABLESPACE temp
    QUOTA 5M ON system 
    PROFILE app_user 
    PASSWORD EXPIRE;

The user sidney has the following characteristics:


Creating External Database Users: Examples

The following example creates an external user, who must be identified by an external source before accessing the database:

CREATE USER app_user1
   IDENTIFIED EXTERNALLY
   DEFAULT TABLESPACE example
   QUOTA 5M ON example
   PROFILE app_user;

The user app_user1 has the following additional characteristics:

To create another user accessible only by an operating system account, prefix the user name with the value of the initialization parameter OS_AUTHENT_PREFIX. For example, if this value is "ops$", you can create the externally identified user external_user with the following statement:

CREATE USER ops$external_user
   IDENTIFIED EXTERNALLY
   DEFAULT TABLESPACE example
   QUOTA 5M ON example
   PROFILE app_user;     

Creating a Global Database User: Example

The following example creates a global user. When you create a global user, you can specify the X.509 name that identifies this user at the enterprise directory server:

CREATE USER global_user
   IDENTIFIED GLOBALLY AS 'CN=analyst, OU=division1, O=oracle, C=US'
   DEFAULT TABLESPACE example
   QUOTA 5M ON example;