CREATE_USER Procedure

This procedure creates a new account record in the Application Express user account table. To execute this procedure, the current user must have administrative privileges.

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 35-11 CREATE_USER Procedure 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`	Date 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 is allowed to App Builder.
`p_allow_sql_workshop_yn`	'Y' or 'N' to indicate whether access is allowed to SQL Workshop.
`p_allow_websheet_dev_yn`	'Y' or 'N' to indicate whether access is allowed to Websheet development.
`p_allow_team_development_yn`	'Y' or 'N' to indicate whether access is allowed to Team Development.

Example 1

The following simple example creates an 'End User' called 'NEWUSER1' with a password of 'secret99'. Note an 'End User' 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').
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;