CREATE_USER Procedure

This procedure creates a new account record in the Oracle APEX user accounts table.

Use this procedure to programmatically create user accounts for applications that utilize the APEX Accounts authentication scheme. To execute this procedure within the context of an APEX application, the current user must be an APEX workspace administrator and the application must permit modification of the workspace repository.

When creating workspace developer or workspace administrator users, you must also ensure that the user can authenticate to the development environment authentication scheme. The CREATE_USER procedure only creates the APEX repository user. For example, if using DB accounts authentication, you must also run CREATE USER nnn IDENTIFIED BY yyy.

Syntax

APEX_UTIL.CREATE_USER (
    p_user_id                       IN NUMBER   DEFAULT NULL,
    p_user_name                     IN VARCHAR2,
    p_first_name                    IN VARCHAR2 DEFAULT NULL,
    p_last_name                     IN VARCHAR2 DEFAULT NULL,
    p_description                   IN VARCHAR2 DEFAULT NULL,
    p_email_address                 IN VARCHAR2 DEFAULT NULL,
    p_web_password                  IN VARCHAR2,
    p_web_password_format           IN VARCHAR2 DEFAULT 'CLEAR_TEXT',
    p_group_ids                     IN VARCHAR2 DEFAULT NULL,
    p_developer_privs               IN VARCHAR2 DEFAULT NULL,
    p_default_schema                IN VARCHAR2 DEFAULT NULL,
    p_allow_access_to_schemas       IN VARCHAR2 DEFAULT NULL,
    p_account_expiry                IN DATE     DEFAULT TRUNC(SYSDATE),
    p_account_locked                IN VARCHAR2 DEFAULT 'N',
    p_failed_access_attempts        IN NUMBER   DEFAULT 0,
    p_change_password_on_first_use  IN VARCHAR2 DEFAULT 'Y',
    p_first_password_use_occurred   IN VARCHAR2 DEFAULT 'N',
    p_attribute_01                  IN VARCHAR2 DEFAULT NULL,
    p_attribute_02                  IN VARCHAR2 DEFAULT NULL,
    p_attribute_03                  IN VARCHAR2 DEFAULT NULL,
    p_attribute_04                  IN VARCHAR2 DEFAULT NULL,
    p_attribute_05                  IN VARCHAR2 DEFAULT NULL,
    p_attribute_06                  IN VARCHAR2 DEFAULT NULL,
    p_attribute_07                  IN VARCHAR2 DEFAULT NULL,
    p_attribute_08                  IN VARCHAR2 DEFAULT NULL,
    p_attribute_09                  IN VARCHAR2 DEFAULT NULL,
    p_attribute_10                  IN VARCHAR2 DEFAULT NULL,
    p_allow_app_building_yn         IN VARCHAR2 DEFAULT NULL,
    p_allow_sql_workshop_yn         IN VARCHAR2 DEFAULT NULL,
    p_allow_websheet_dev_yn         IN VARCHAR2 DEFAULT NULL,
    p_allow_team_development_yn     IN VARCHAR2 DEFAULT NULL );

Parameters

Table 48-13 CREATE_USER Parameters

Parameter	Description
`p_user_id`	Numeric primary key of user account.
`p_user_name`	Alphanumeric name used for login.
`p_first_name`	Informational.
`p_last_name`	Informational.
`p_description`	Informational.
`p_email_address`	Email address.
`p_web_password`	Clear text password.
`p_web_password_format`	If the value your passing for the `p_web_password` parameter is in clear text format then use `CLEAR_TEXT`, otherwise use `HEX_ENCODED_DIGEST_V2`.
`p_group_ids`	Colon separated list of numeric group IDs.
`p_developer_privs`	Colon separated list of developer privileges. If `p_developer_privs` is not null, the user is given access to Team Development. If `p_developer_privs` contains `ADMIN`, the user is given App Builder and SQL Workshop access. If `p_developer_privs` does not contain ADMIN but contains `EDIT`, the user is given App Builder access. If `p_developer_privs` does not contain ADMIN but contains `SQL`, the user is given SQL Workshop access. The following are acceptable values for this parameter: `NULL` - To create an end user (a user who can only authenticate to developed applications). `CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL` - To create a user with developer privileges with access to App Builder and SQL Workshop. `ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL` - To create a user with full workspace administrator and developer privileges with access to App Builder, SQL Workshop and Team Development. Note: Currently this parameter is named inconsistently between the `CREATE_USER`, `EDIT_USER`, and `FETCH_USER` APIs, although they all relate to the `DEVELOPER_ROLE` field stored in the named user account record. `CREATE_USER` uses `p_developer_privs`; `EDIT_USER` uses `p_developer_roles`; and `FETCH_USER` uses `p_developer_role`.
`p_default_schema`	A database schema assigned to the user's workspace, used by default for browsing.
`p_allow_access_to_schemas`	Colon-separated list of schemas assigned to the user's workspace to which the user is restricted (leave NULL for all).
`p_account_expiry`	The date the password was last updated, which defaults to today's date on creation.
`p_account_locked`	`Y` or `N` indicating if account is locked or unlocked.
`p_failed_access_attempts`	Number of consecutive login failures that have occurred, defaults to `0` on creation.
`p_change_password_on_first_use`	`Y` or `N` to indicate whether password must be changed on first use, defaults to `Y` on creation.
`p_first_password_use_occurred`	`Y` or `N` to indicate whether login has occurred since password change, defaults to `N` on creation.
`p_attribute_01` … `p_attribute_10`	Arbitrary text accessible with an API.
`p_allow_app_building_yn`	`Y` or `N` to indicate whether access to App Builder is enabled.
`p_allow_sql_workshop_yn`	`Y` or `N` to indicate whether access to SQL Workshop is enabled..
`p_allow_websheet_dev_yn`	`Y` or `N` to indicate whether access to Websheet development is enabled.
`p_allow_team_development_yn`	`Y` or `N` to indicate whether access to Team Development is enabled.

Example 1

The following example creates an End User called NEWUSER1 with a password of secret99. End Users can only authenticate to developed applications.

BEGIN
    APEX_UTIL.CREATE_USER(
        p_user_name    => 'NEWUSER1',
        p_web_password => 'secret99');
END;

Example 2

The following example creates a Workspace Administrator called NEWUSER2 where the user NEWUSER2:

has full workspace administration and developer privilege (p_developer_privs parameter set to ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL)
has access to 2 schemas, both their browsing default MY_SCHEMA (p_default_schema parameter set to MY_SCHEMA) and also MY_SCHEMA2 (p_allow_access_to_schemas parameter set to MY_SCHEMA2)
does not have to change their password when they first login (p_change_password_on_first_use parameter set to N)
and has their phone number stored in the first additional attribute (p_attribute_01 parameter set to 123 456 7890).

BEGIN
    APEX_UTIL.CREATE_USER(
        p_user_name                     => 'NEWUSER2',
        p_first_name                    => 'FRANK',
        p_last_name                     => 'SMITH',
        p_description                   => 'Description...',
        p_email_address                 => 'frank@smith.com',
        p_web_password                  => 'password',
        p_developer_privs               => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
        p_default_schema                => 'MY_SCHEMA',
        p_allow_access_to_schemas       => 'MY_SCHEMA2',
        p_change_password_on_first_use  => 'N',
        p_attribute_01                  => '123 456 7890');
END;