CREATE_USER Procedure

This procedure creates a new account record in the Application Express user accounts table. Use this procedure to programmatically create user accounts for applications that utilize Application Express Accounts authentication scheme. To execute this procedure within the context of an Application Express application, the current user must be Application Express workspace administrator and the application must permit modification of the workspace repository.

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 44-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;