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. This can be configured using one of the following supported external authenticators: Cisco Duo or Oracle Mobile Authenticator. To enable them, set the value to `duo_push` or `oma_push`, respectively.
`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: 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: 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 'emma.baker@supremo.com';

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 'emma.baker@supremo.com';