CREATE END USER

Purpose

Use CREATE END USER to create an end user locally in the database.

Prerequisites

You must have the CREATE END USER system privilege to create end users.

You can query the DBA_END_USERS data dictionary view to see existing local end users.

Syntax


Description of create_end_user.eps follows
Description of the illustration create_end_user.eps

Semantics

  • IF NOT EXISTS: If you specify IF NOT EXISTS and the end user already exists, no error is raised. If the end user does not exist, then it will be created.

    If you do not specify IF NOT EXISTS and the end user already exists, an error is raised. If the end user does not exist, then it will be created.

  • end_user: The name of the end user to be created.

  • password: The password for the end user (for direct database login).

  • profile: The name of the profile. The profile limits the amount of database resources the end user can use. If this clause is omitted, then the DEFAULT profile is assigned to the end user.

  • PASSWORD EXPIRE: Expire the password of the end user. This setting forces the end user or administrator to change the password before the end user can log in.

  • ACCOUNT LOCK: Lock the account of the end user and disable access.

  • ACCOUNT UNLOCK: Unlock the account of the end user and enable access.

  • schema: The database schema to be associated with the end user. This is optional. If it is not specified, the end user will not have any database schema for name resolution.

  • START TIME: The time from which the end user account becomes effective, in TIMESTAMP WITH TIME ZONE format. This is optional.

  • END TIME: The date on which the end user account becomes ineffective, in TIMESTAMP WITH TIME ZONE format. This is optional.

If TO_TIMESTAMP is used to specify a start date or end date, the database will use the session time zone as the time zone of the specified time. Then it will be shifted to UTC and stored in the Deep Sec dictionary table. To specify a start date or end date with a different time zone, TO_TIMESTAMP_TZ should be used.

If no password is specified, the authentication type of the end user is ‘NONE’. PASSWORD EXPIRE cannot be specified for an end user with no authentication type.

The following SQL statement creates an end user emma, who can directly login to the database with the specified password, starting from 2025-03-01 19:30:00 UTC time. 


CREATE END USER emma 
  IDENTIFIED BY password 
  START TIME TO_TIMESTAMP_TZ('2025-03-01 19:30:00 +00:00',
                          'YYYY-MM-DD HH24:MI:SS TZH:TZM');