CREATE USER

The CREATE USER statement creates a user in the TimesTen database.

Required Privilege

ADMIN

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL Syntax

CREATE USER user IDENTIFIED BY {password | "password"}
  [PROFILE profile] [ACCOUNT {LOCK|UNLOCK}] [PASSWORD EXPIRE]

or

CREATE USER user IDENTIFIED EXTERNALLY
  [PROFILE profile] [ACCOUNT {LOCK|UNLOCK}]

Parameters

Parameter	Description
`user`	Name of the user.
`IDENTIFIED BY {password` `\| "password"}`	Identification clause for an internal user. You must supply a password for an internal user. The password you can specify is dependent on the profile assigned to the user. Specifically, the value of the `PASSWORD_COMPLEXITY_CHECKER` password parameter determines the complexity of the password. If the value is `TT_VERIFY_FUNCTION`, `TT_STRONG_VERIFY_FUNCTION`, or `TT_STIG_VERIFY_FUNCTION`, the password must meet specific password verification requirements. For example, if the value is `TT_VERIFY_FUNCTION`, the password cannot contain the name of the database. See About Password Complexity Checker Verification for details.
`IDENTIFIED EXTERNALLY`	Identifies an external user (the operating system user). To perform database operations as an external user, the external user name must match the user name authenticated by the operating system or network. A password is not required by TimesTen as the user has been authenticated by the operating system at login time.
`PROFILE` `profile`	Use the `PROFILE` clause to specify the name of the profile (designated by `profile`) that you want to assign to the user. The profile sets the limits for the password parameters for the user. See CREATE PROFILE for information on these password parameters. If you omit the `PROFILE` clause, TimesTen assigns the `DEFAULT` profile to the user. If you create an external user (denoted by specifying the `EXTERNALLY` keyword), you can specify a `PROFILE` clause, but the password parameters have no effect on external users. Additionally, if you do not specify the `PROFILE` clause for an external user, TimesTen assigns the `DEFAULT` profile to the user (but the password parameters have no effect).
`ACCOUNT` `[LOCK\|UNLOCK]`	Specify `ACCOUNT` `LOCK` to lock the user's account and disable connections to the database. Specify `ACCOUNT` `UNLOCK` to unlock the user's account and enable connections to the database. The default is `ACCOUNT` `UNLOCK`.
`PASSWORD` `EXPIRE`	Specify `PASSWORD` `EXPIRE` if you want the user's password to expire. This setting forces a user with `ADMIN` privileges to change the password before the user can connect to the database. In order to change the expired password, a user with `ADMIN` privileges must use the `ALTER` `USER` statement with the `IDENTIFIED` `BY` clause to change the password. Once the password is changed, the user can log in to the database with the new password. Note that even if the newly created user is granted `ADMIN` privileges, that newly created user cannot login to the database and therefore cannot initially change the password. See ALTER USER for information. This clause is not valid for an externally identified user (as denoted by the `IDENTIFIED` `EXTERNALLY` clause).

Description

Database users can be internal or external.
- Internal users are defined for a TimesTen database.
- External users are defined by the operating system. External users cannot be assigned a TimesTen password.
Password requirements:
- Cannot exceed 30 characters.
- Is case-sensitive.
- Must start with a letter. A password cannot start with a digit or a special character unless the password is enclosed in double quotation marks.
- If a special character is used, the password must be contained in double quotation marks. The exceptions are the # and the @ special characters. A password that contains the # or the @ special character does not need to be enclosed in double quotation marks.
- Cannot contain a semi-colon (;) or a double quotation mark (").
When a user is created, the user has the privileges granted to PUBLIC and no additional privileges.
Use the PROFILE clause to assign a profile to a user. If you assign the profile to an internal user, the user cannot exceed the limits specified for the profile. If you do not assign a profile to an internal user, a DEFAULT profile is assigned to that user. See "CREATE PROFILE" for details.
Use the ACCOUNT LOCK or ACCOUNT UNLOCK to lock or unlock the user account.
Use the PASSWORD EXPIRE clause to expire the user's password and force a password change before the user can connect to the database.
You can create a user over a client/sever connection if the connection is encrypted with TLS. See Transport Layer Security for TimesTen Client/Server in the Oracle TimesTen In-Memory Database Security Guide for details.
In TimesTen, user brad is the same as user "brad". In both cases, the name of the user is created as BRAD.
User names are TT_CHAR data type.
This statement is replicated.

Examples

Create Users and Observe Password Verification

This example creates the user_pw1 user and does not assign a profile to the user1_pw user. The user is subject to the limits of the DEFAULT profile. The PASSWORD_COMPLEXITY_CHECKER password parameter is set to NULL for the DEFAULT profile. Therefore, there is no password verification performed on this user's password. The example then alters the DEFAULT profile, changing the value of the PASSWORD_COMPLEXITY_CHECKER to TT_VERIFY_FUNCTION. The user1_p1 user can still connect to the database with the original password. Password verification is performed only on newly created users.

Command> CREATE USER user_pw1 IDENTIFIED BY user1_pw1;

User created.

Query the dba_profiles system view to check the limits of the password parameters for the DEFAULT profile. The PASSWORD_COMPLEXITY_CHECKER password parameter has a value of NULL.

Command> SELECT * FROM dba_profiles WHERE profile = 'DEFAULT';
< DEFAULT, FAILED_LOGIN_ATTEMPTS, PASSWORD, 10 >
< DEFAULT, PASSWORD_LIFE_TIME, PASSWORD, UNLIMITED >
< DEFAULT, PASSWORD_REUSE_TIME, PASSWORD, UNLIMITED >
< DEFAULT, PASSWORD_REUSE_MAX, PASSWORD, UNLIMITED >
< DEFAULT, PASSWORD_COMPLEXITY_CHECKER, PASSWORD, NULL >
< DEFAULT, PASSWORD_LOCK_TIME, PASSWORD, .0034 >
< DEFAULT, PASSWORD_GRACE_TIME, PASSWORD, UNLIMITED >
< DEFAULT, TEMP_SPACE_PER_SESSION_MAX, MEMORY, UNLIMITED >
8 rows found.

Alter the DEFAULT profile, changing the value of the PASSWORD_COMPLEXITY_CHECKER parameter to TT_VERIFY_FUNCTION. Attempt to connect to the database as the user_pw1 user. The connection is successful, as password verification is only performed on newly created passwords.

Command> ALTER PROFILE "DEFAULT" LIMIT 
           PASSWORD_COMPLEXITY_CHECKER TT_VERIFY_FUNCTION;

Profile altered.

Command> connect adding "UID=user_pw1;PWD=user_pw1" as user1;
Connection successful: DSN=access1;UID=user_pw1;
DataStore=/scratch/user1/mydatabase1;DatabaseCharacterSet=AL32UTF8;
ConnectionCharacterSet=AL32UTF8;PermSize=128;
(Default setting AutoCommit=1)

Create the user_pw2 user and specify user_pw2 for the password. The CREATE USER statement fails. Password verification is performed on the password for user_pw2, as the password is a newly created password. Create the user_pw2 user again, specifying a password that meets the requirements of the TT_VERIFY_FUNCTION function. The CREATE USER statement is successful, and the user is created. See TT_VERIFY_FUNCTION for more information on the TT_VERIFY_FUNCTION function.

Command> CREATE USER user_pw2 IDENTIFIED BY user_pw2;
15186: Password complexity check for the specified password failed
15188: TT-20002: Password contains the username
The command failed.

Command> CREATE USER user_pw2 IDENTIFIED BY abc75#n4;

User created.

Create User with TT_STRONG_VERIFY_FUNCTION Password Qequirements

This example illustrates the password verification requirements for the TT_STRONG_VERIFY_FUNCTION function. Create the profile_pw3 profile and specify a value of TT_STRONG_VERIFY_FUNCTION for the PASSWORD_COMPLEXITY_CHECKER password parameter. Create the user_pw3 user and assign this user the profile_pw3 profile. Experiment with different passwords to confirm that the password meets the requirements of the TT_STRONG_VERIFY_FUNCTION function. If the password meets the requirements, the CREATE USER statement is successful and the user is created. See TT_STRONG_VERIFY_FUNCTION.

Command> CREATE PROFILE profile_pw3 LIMIT 
           PASSWORD_COMPLEXITY_CHECKER TT_STRONG_VERIFY_FUNCTION;

Profile created.

Create the user_pw3 user and experiment with various passwords. Recall that special characters must be enclosed in double quotation marks (with the exception of # and @).

Command> CREATE USER user_pw3 IDENTIFIED BY abcABC1# 
           PROFILE profile_pw3;
15186: Password complexity check for the specified password failed
15188: TT-20001: Password length less than 9
The command failed.

Command> CREATE USER user_pw3 IDENTIFIED BY abcABCD1# 
           PROFILE profile_pw3;
15186: Password complexity check for the specified password failed
15188: TT-20001: Password must contain at least 2 digit(s)
The command failed.

Command> CREATE USER user_pw3 IDENTIFIED BY abcABCD11# 
           PROFILE profile_pw3;
15186: Password complexity check for the specified password failed
15188: TT-20001: Password must contain at least 2 special character(s)
The command failed.

Command> CREATE USER user_pw3 IDENTIFIED BY "!abcABCD11#" 
           PROFILE profile_pw3;

User created.

Create a User and Assign a Profile

This example creates the user1 user and assigns the profile1 profile to the user.

Command> CREATE USER user1 IDENTIFIED BY user1 PROFILE profile1;
 
User created.

Create a User and Do Not Assign a Profile

This example creates the user2 user and does not assign a profile. The user2 user is assigned the values of the password parameters in the DEFAULT profile.

Command> CREATE USER user2 identified by user2;
 
User created.

Query the dba_users system view to verify the user2 user is assigned the DEFAULT profile.

Command> SELECT profile FROM dba_users WHERE username='USER2';
< DEFAULT >
1 row found.

Create a User and Lock the User Account

This example creates the user3 user and locks the user3 account. The user3 account must be unlocked by a user with the ADMIN privilege before the user3 user can connect to the database.

Command> CREATE USER user3 IDENTIFIED BY user3 ACCOUNT LOCK;
 
User created.

Grant the CONNECT privilege to user3;

Command> GRANT CONNECT TO user3;

Attempt to connect to the database as user3. The user3 account is locked so the connection fails.

Command> connect adding "UID=user3;PWD=user3" as user3;
15179: the account is locked
The command failed.

As the instance administrator, reconnect to the database and use the ALTER USER statement to unlock the user3 account.

none: Command> use database1
database1: Command> ALTER USER user3 ACCOUNT UNLOCK;
 
User altered.

Attempt to connect to the database a the user3 user. The connection succeeds.

database1: Command> connect adding "UID=user3;PWD=user3" as user3;
Connection successful: DSN=database1;UID=user3;DataStore=/scratch/database1;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;PermSize=128;
(Default setting AutoCommit=1)

Lock the User Account and Enforce a Password Change

This example creates the user4 user. The user4 user is assigned the profile1 profile. The user4 account is locked and the password for user4 must be changed before the user4 user can connect to the database.

Command> CREATE USER user4 identified by user4 PROFILE profile1 
         ACCOUNT LOCK PASSWORD EXPIRE;
 
User created.

Attempt to connect to the database as user4. The user4 account is locked and the password must be changed before the user4 user can connect to the database.

Command>  connect adding "UID=user4;PWD=user4" as user4;
15179: the account is locked
The command failed.

As the instance administrator, reconnect to the database and use the ALTER USER statement to unlock the user4 account.

none: Command> use database1
database1: Command> ALTER USER user4 ACCOUNT UNLOCK;
 
User altered.

Grant the CONNECT privilege to user4. Then change the user4's expired password. (This example changes the password to user4_changed, represented in bold.)

database1: Command> GRANT CONNECT TO user4;
database1: Command> ALTER USER user4 IDENTIFIED BY user4_changed;
 
User altered.

Attempt to connect to the database as the user4 user. The connection succeeds. The account is unlock and the password is changed.

database1: Command> connect adding "UID=user4;PWD=user4_changed" as user4;
Connection successful: DSN=database1;UID=user4;DataStore=/scratch/database1;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;PermSize=128;
(Default setting AutoCommit=1)

Create an External User

This example creates the user5 user as an external user.

Command> CREATE USER user5 IDENTIFIED EXTERNALLY;
 
User created.