Previous
Previous
 
Next
Next


EDIT_USER Procedure

This procedure enables a user account record to be altered. To execute this procedure, the current user must have administrative privileges in the workspace.

Syntax

APEX_UTIL.EDIT_USER (
    p_user_id                      IN                   NUMBER,
    p_user_name                    IN                   VARCHAR2,
    p_first_name                   IN                   VARCHAR2    DEFAULT NULL,
    p_last_name                    IN                   VARCHAR2    DEFAULT NULL,
    p_web_password                 IN                   VARCHAR2    DEFAULT NULL,
    p_new_password                 IN                   VARCHAR2    DEFAULT NULL,
    p_email_address                IN                   VARCHAR2    DEFAULT NULL,
    p_start_date                   IN                   VARCHAR2    DEFAULT NULL,
    p_end_date                     IN                   VARCHAR2    DEFAULT NULL,
    p_employee_id                  IN                   VARCHAR2    DEFAULT NULL,
    p_allow_access_to_schemas      IN                   VARCHAR2    DEFAULT NULL,
    p_person_type                  IN                   VARCHAR2    DEFAULT NULL,
    p_default_schema               IN                   VARCHAR2    DEFAULT NULL,
    p_group_ids                    IN                   VARCHAR2    DEFAULT NULL,
    p_developer_roles              IN                   VARCHAR2    DEFAULT NULL,
    p_description                  IN                   VARCHAR2    DEFAULT NULL,
    p_account_expiry               IN                   DATE        DEFAULT NULL,
    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');

Parameters

Table: EDIT_USER Parameters describes the parameters available in the EDIT_USER procedure.

EDIT_USER Parameters

Parameter Description

p_user_id

Numeric primary key of the user account

p_user_name

Alphanumeric name used for login.

See Also: "SET_USERNAME Procedure"

p_first_name

Informational.

See Also: "SET_FIRST_NAME Procedure"

p_last_name

Informational.

See Also: "SET_LAST_NAME Procedure"

p_web_password

Clear text password. If using this procedure to update the password for the user, values for both p_web_password and p_new_password must not be null and must be identical.

p_new_password

Clear text new password. If using this procedure to update the password for the user, values for both p_web_password and p_new_password must not be null and must be identical.

p_email_address

Informational.

See Also: "SET_EMAIL Procedure"

p_start_date

Unused

p_end_date

Unused

p_employee_id

Unused

p_allow_access_to_schemas

A list of schemas assigned to the user's workspace to which the user is restricted

p_person_type

Unused

p_default_schema

A database schema assigned to the user's workspace, used by default for browsing

p_group_ids

Colon-separated list of numeric group IDs

p_developer_roles

Colon-separated list of developer privileges. The following are acceptable values for this parameter:

· null - To update the user to be an end user (a user who can only authenticate to developed applications)

· CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL - To update the user to have developer privilege

· ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL - To update the user to have 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.

See Also: "GET_USER_ROLES Function"

p_description

Informational

p_account_expiry

Date password was last updated.

See Also: "EXPIRE_END_USER_ACCOUNT Procedure", "EXPIRE_WORKSPACE_ACCOUNT Procedure", "UNEXPIRE_END_USER_ACCOUNT Procedure", "UNEXPIRE_WORKSPACE_ACCOUNT Procedure"

p_account_locked

'Y' or 'N' indicating if account is locked or unlocked.

See Also: "LOCK_ACCOUNT Procedure", "UNLOCK_ACCOUNT Procedure"

p_failed_access_attempts

Number of consecutive login failures that have occurred.

p_change_password_on_first_use

'Y' or 'N' to indicate whether password must be changed on first use.

See Also: "CHANGE_PASSWORD_ON_FIRST_USE Function"

p_first_password_use_occurred

'Y' or 'N' to indicate whether login has occurred since password change.

See Also: "PASSWORD_FIRST_USE_OCCURRED Function"


Example

The following example shows how to use the EDIT_USER procedure to update a user account. This example shows how you can use the EDIT_USER procedure to change the user 'FRANK' from a user with just developer privilege to a user with workspace administrator and developer privilege. Firstly, the FETCH_USER procedure is called to assign account details for the user 'FRANK' to local variables. These variables are then used in the call to EDIT_USER to preserve the details of the account, with the exception of the value for the p_developer_roles parameter, which is set to 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL'.

DECLARE
    l_user_id                       NUMBER;
    l_workspace                     VARCHAR2(255);
    l_user_name                     VARCHAR2(100);
    l_first_name                    VARCHAR2(255);
    l_last_name                     VARCHAR2(255);
    l_web_password                  VARCHAR2(255);
    l_email_address                 VARCHAR2(240);
    l_start_date                    DATE;
    l_end_date                      DATE;
    l_employee_id                   NUMBER(15,0);
    l_allow_access_to_schemas       VARCHAR2(4000);
    l_person_type                   VARCHAR2(1);
    l_default_schema                VARCHAR2(30);
    l_groups                        VARCHAR2(1000);
    l_developer_role                VARCHAR2(60);
    l_description                   VARCHAR2(240);
    l_account_expiry                DATE;
    l_account_locked                VARCHAR2(1);
    l_failed_access_attempts        NUMBER;
    l_change_password_on_first_use  VARCHAR2(1);
    l_first_password_use_occurred   VARCHAR2(1);
BEGIN
    l_user_id := APEX_UTIL.GET_USER_ID('FRANK');

APEX_UTIL.FETCH_USER(
    p_user_id                       => l_user_id,
    p_workspace                     => l_workspace,
    p_user_name                     => l_user_name,
    p_first_name                    => l_first_name,
    p_last_name                     => l_last_name,
    p_web_password                  => l_web_password,
    p_email_address                 => l_email_address,
    p_start_date                    => l_start_date,
    p_end_date                      => l_end_date,
    p_employee_id                   => l_employee_id,
    p_allow_access_to_schemas       => l_allow_access_to_schemas,
    p_person_type                   => l_person_type,
    p_default_schema                => l_default_schema,
    p_groups                        => l_groups,
    p_developer_role                => l_developer_role,
    p_description                   => l_description,
    p_account_expiry                => l_account_expiry,
    p_account_locked                => l_account_locked,
    p_failed_access_attempts        => l_failed_access_attempts,
    p_change_password_on_first_use  => l_change_password_on_first_use,
    p_first_password_use_occurred   => l_first_password_use_occurred);
APEX_UTIL.EDIT_USER (
    p_user_id                       => l_user_id,
    p_user_name                     => l_user_name,
    p_first_name                    => l_first_name,
    p_last_name                     => l_last_name,
    p_web_password                  => l_web_password,
    p_new_password                  => l_web_password,
    p_email_address                 => l_email_address,
    p_start_date                    => l_start_date,
    p_end_date                      => l_end_date,
    p_employee_id                   => l_employee_id,
    p_allow_access_to_schemas       => l_allow_access_to_schemas,
    p_person_type                   => l_person_type,
    p_default_schema                => l_default_schema,
    p_group_ids                     => l_groups,
    p_developer_roles               => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
    p_description                   => l_description,
    p_account_expiry                => l_account_expiry,
    p_account_locked                => l_account_locked,
    p_failed_access_attempts        => l_failed_access_attempts,
    p_change_password_on_first_use  => l_change_password_on_first_use,
    p_first_password_use_occurred   => l_first_password_use_occurred);
END;