Previous
Previous
 
Next
Next


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

Parameters

Table: CREATE_USER Procedure Parameters describes the parameters available in the CREATE_USER procedure.

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. 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 privilege.

ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL - To create a user with full workspace administrator and developer privilege.

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 will default 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


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':

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;