11.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, enforce a second authentication factor, associate the end user 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 ]
  [ AND FACTOR factor AS external_name ]
  [ 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.
factor	The second authentication factor for a local end user through an external authenticator. Valid values are duo_push for Cisco Duo and oma_push for Oracle Mobile Authenticator.
external_name	The identifier of the end user in the external authenticator system, such as the user’s registered email address. Required when a factor is specified.
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.
• For direct database logon, configure a password and, optionally, a second authentication factor. When you specify a second factor, the database establishes the session only after the database validates the password and the end user approves the configured factor.
• Before specifying a factor, configure the required MFA provider, SMTP settings, server wallet secrets, SQLNET.INBOUND_CONNECT_TIMEOUT value, and SMTP credential object. See Set Up Multifactor Authentication for Local End Users.
• The external_name value identifies the end user in the external authenticator system. Each local end user must have a unique external_name value.
• 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 11-1 Create end user with password

Create a local end user 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');

Example 11-2 Create end user with a second authentication factor

Create a local end user Emma with Duo Push as the second factor:

CREATE END USER emma
  IDENTIFIED BY <password>
  AND FACTOR 'duo_push' AS '[e-mail]';

Create a local end user Emma with Oracle Mobile Authenticator as the second factor:

CREATE END USER emma
  IDENTIFIED BY <password>
  AND FACTOR 'oma_push' AS '[e-mail]';