10.1 Create End User

Use the CREATE END USER command to create a local end user in the database.

You can configure the local end user with a password for direct database login, associate it with a database schema for name resolution, and assign an account validity period. Query the DBA_END_USERS data dictionary view to review existing local end users.

Required privilege

The CREATE END USER system privilege.

Syntax

CREATE END USER [ IF NOT EXISTS ] end_user
  [ IDENTIFIED BY password ]
  [ PROFILE profile ]
  [ PASSWORD EXPIRE ]
  [ ACCOUNT { LOCK | UNLOCK } ]
  [ SCHEMA schema ]
  [ START TIME timestamp ] [ END TIME timestamp ];

Parameters

Parameter Description

end_user

The name of the end user to be created.

password

The password for the end user, enabling direct database login. The password uses a SHA-512 verifier, and the Oracle client must be compatible with Oracle Database 12c or later. If not specified, the authentication type is set to NONE.

profile

The name of the Oracle profile to assign. Profiles limit database resource usage. If omitted, the DEFAULT profile is assigned.

PASSWORD EXPIRE

A setting that expires the end user's password immediately, forcing the user or administrator to set a new password before the next login.

ACCOUNT LOCK

A setting that locks the account and disables access.

ACCOUNT UNLOCK

A setting that unlocks the account and enables access.

schema

The existing database schema to associate with this end user for name resolution. Optional. If not specified, no schema is associated.

START TIME

The time stamp from which the end-user account becomes effective, in TIMESTAMP WITH TIME ZONE format. Optional.

END TIME

The time stamp on which the end-user account becomes ineffective, in TIMESTAMP WITH TIME ZONE format. Optional.

Usage notes and restrictions

  • IF NOT EXISTS behavior: If the end user does not exist, it is created. If it already exists, an error is raised unless IF NOT EXISTS is specified, in which case the statement is a no-op.
  • When specifying time values with TO_TIMESTAMP, Oracle uses the session time zone. To specify a different time zone, use TO_TIMESTAMP_TZ.

For syntax diagrams and additional details, see CREATE END USER in Oracle AI Database SQL Language Reference.

Example 10-1 Create end user with password

Create a local end user named Emma who can log in with a password, effective from 2025-03-01 19:30:00 UTC:

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');