Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
CREATE TYPE to
DROP ROLLBACK SEGMENT, 4 of 26
Use the CREATE
USER
statement 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. |
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.
create_user::=
create_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.
The IDENTIFIED
clause lets you indicate how Oracle authenticates the user.
The BY
password clause lets you 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 Oracle9i Database Administrator's Guide.
See Also:
Oracle9i Database Administrator's Guide to for a detailed description and explanation of how to use password management and protection |
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 to 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:
strongly recommends that you do not use |
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:
CN=
username,other_attributes', where other_attributes is the rest of the user's distinguished name (DN) in the directory.
See Also:
Specify the default tablespace for objects that the user creates. If you omit this clause, objects default to the SYSTEM
tablespace.
Restriction: You cannot specify an undo tablespace as the default tablespace.
See Also:
CREATE TABLESPACE for more information on tablespaces in general and undo tablespaces in particular |
Specify the tablespace for the user's temporary segments. If you omit this clause, temporary segments default to the SYSTEM
tablespace.
Restrictions:
Use the QUOTA
clause to allow the user to allocate up to integer bytes of space in the tablespace. 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
lets the user allocate space in the tablespace without bound.
Specify the the profile you want to reassign 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.
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.
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.
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 demo QUOTA 10M ON demo TEMPORARY TABLESPACE temp QUOTA 5M ON system PROFILE app_user PASSWORD EXPIRE;
The user sidney
has the following characteristics:
welcome
demo
, with a quota of 10 megabytes
temp
SYSTEM
, with a quota of 5 megabytes
app_user
sidney
can log in to the database
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 tbs_1 QUOTA 5M ON tbs_1 PROFILE app_user;
The user app_user1
has the following additional characteristics:
tbs_1
tbs_1
tbs_1
and unlimited quota on the temporary tablespace of the database
app_user
profile
To create another user accessible only by the operating system account app_user2
, prefix app_user2
by the value of the initialization parameter OS_AUTHENT_PREFIX
. For example, if this value is "ops$
", you can create the user ops$app_user
2 with the following statement:
CREATE USER ops$app_user2 IDENTIFIED EXTERNALLY DEFAULT TABLESPACE tbs_1 QUOTA 5M ON tbs_1 PROFILE app_user;
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 tbs_1 QUOTA 5M on tbs_1;
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|