CREATE USER

CREATE USER

Syntax

create_user ::=

password_parameters ::=

Prerequisites

Only the SYS user and users to whom the CREATE USER system privilege has been granted can create users.

Description

This statement is used to create a database user and specify the user's name, password, and tablespace access privileges.

user_name

This is used to specify the name of the user to create. The user name must be unique in the database. Refer to "Rules for Object Names" in Chapter 2 for more information on specifying names.

IDENTIFIED BY password

Altibase uses password authentication. The maximum length of a user password can be 16 to 22 bytes, depending on the operating system. An error is raised if you try to create a user with password that exceeds this limit. Altibase automatically converts lowercase passwords to uppercase by default. However, you can create a case sensitive password by setting the value of CASE_SENSITIVE_PASSWORD to 1, and then enclosing the password in quotation marks, when creating a user with the CREATE USER statement

User passwords must comply with Rules for Object Names in Chapter 2.

TEMPORARY TABLESPACE Clause

This clause is used to specify the default temporary tablespace for the user, and will be used to store intermediate results when the user performs operations on tables.

If no tablespace is specified, the system temporary tablespace¹ will be used as the temporary tablespace for the user.

A temporary tablespace is normally used to store intermediate results when the user performs operations on disk-based tables.

In the case where all of the tables being accessed by a query are memory tables, all query operations would take place in memory space, and thus no temporary tablespace would be used unless coerced using a query hint.

Only one temporary tablespace can be assigned to a user.

DEFAULT TABLESPACE Clause

This is used to specify the default tablespace in which to store user-created objects. If this clause is omitted, the default tablespace for the user is the system memory default tablespace.

Only one default tablespace can be specified for a user.

ACCESS Clause

This is used to specify whether or not the user has access to the specified tablespace. If this clause takes the form ACCESS tablespace_name ON, the user is permitted to access the specified tablespace, whereas if it takes the form ACCESS tablespace_name OFF, the user is not authorized to access the specified tablespace.

Users are also able to access tablespaces if the ALTER TABLESPACE system privilege has been granted to them.

ENABLE/ DISABLE

Specifies whether to enable or disable the user's TCP connection. Only the SYS user can execute this clause.

password_parameters

FAILED_LOGIN_ATTEMPTS

If the number of times login fails equals the number set for this value, the account is locked and login is impossible until it is unlocked.

If PASSWORD_LOCK_TIME is set, the account is automatically unlocked after the specified time elapses

PASSWORD_LOCK_TIME

This specifies the date(unit: days) required to elapse for a locked account to become unlocked. For example, if the number 5 is set for this value and an account is locked, the account is unlocked after 5 days and login is possible.

PASSWORD_LIFE_TIME

This specifies the period of validity (unit: days) of the account password. PASSWORD_LIFE_TIME is applied in compliance with the last time the password was modified.

PASSWORD_GRACE_TIME

This specifies the grace period (unit: days) during which the password can be modified after the expiry date. After expiration, the password must be modified by logging in the account within the given grace period. After grace period expiration, the password must be modified by logging in as SYS user.

PASSWORD_REUSE_TIME

This specifies the period of time (unit: days) needed to elapse for the reuse of identical passwords. Thus, identical passwords can be reused after the period of time specified for this option elapses.

PASSWORD_REUSE_MAX

This specifies the number of times passwords can be altered for the reuse of identical passwords. Thus, identical passwords can be reused after they have been altered for the number of times specified for this option.

Note: If either PASSWORD_REUSE_MAX or PASSWORD_REUSE_TIME is specified exclusively, the password cannot be reused.

PASSWORD_VERIFY_FUNCTION

Here the user can register user-defined CALLBACK functions to verify passwords. User-defined CALLBACK functions must return 'TRUE'.

CALLBACK functions for verifying passwords must have the following input parameters and return types:

CREATE OR REPLACE FUNCTION pwd_verify_function (
  username varchar(20),
  password varchar(20))
  RETURN varchar(100)
AS
result        varchar(100);
...
BEGIN
    ...
    result := 'TRUE';
    RETURN result;
END;

Restrictions

A single user can use multiple data tablespaces. However, a single user can use only one temporary tablespace.

It is not possible for a user to expressly access the system undo tablespace or create tables, indexes, or other objects therein. Additionally, because there is one, and only one, system undo tablespace in the system, users cannot delete the system undo tablespace or create other system undo tablespaces.

Examples

<Query> Create a user whose name is uare1 and password is rose1.

iSQL> CREATE USER uare1 IDENTIFIED BY rose1;
Create success.

<Query> Create a user named uare4 with the password rose4. Specify user_data as the default tablespace and temp_data as the temporary tablespace for the user, and grant the user privileges to access the SYS_TBS_MEMORY tablespace.

iSQL> CREATE USER uare4
        IDENTIFIED BY rose4
        DEFAULT TABLESPACE user_data
        TEMPORARY TABLESPACE temp_data
        ACCESS SYS_TBS_MEMORY ON;
Create success.

<Query> Create user rose2; the account is to be locked if login fails 5 times and unlocked after 5 days.

iSQL> CREATE USER rose2 IDENTIFIED BY rose2 
  LIMIT (FAILED_LOGIN_ATTEMPTS 5, PASSWORD_LOCK_TIME 5);

<Query> Create user rose3; the password is to expire after 5 days and a grace period of 5 days is to follow.

iSQL> CREATE USER rose3 IDENTIFIED BY rose3
  LIMIT (PASSWORD_LIFE_TIME 5, PASSWORD_GRACE_TIME 5);

<Query> Create user rose4; the password is to be available for reuse after being altered 3 times, and 10 days have elapsed.

iSQL> CREATE USER rose4 IDENTIFIED BY rose4
  LIMIT (PASSWORD_REUSE_MAX 3, PASSWORD_REUSE_TIME 10);

---

1. The system temporary tablespace is used for the temporary storage of data that are generated while a query is being executed. It is not logged, and thus the data stored therein cannot be recovered in the event of a media error. ↩