This discussion is archived
4 Replies Latest reply: Feb 28, 2013 4:46 PM by Danny*D201 RSS

How to migrate Apex users with existing passwords.

Danny*D201 Journeyer
Currently Being Moderated
Hi Guys,
Our apex env finally getting a upgrade from 3.1.1 to 4.1.1 (I know, it's been overdue for years)
Some of our apps use 'Application Express' authentication, and have few hundreds users in Apex (and users belong to diff user groups).

The issue is, the 4.1.1 env is set up on a brand new server and DB, we want to migrate these users with their existing passwords from the 3.1.1 env.

I tried exporting the workspace, and the users are exported as below,

     begin
     wwv_flow_fnd_user_api.create_fnd_user (
     p_user_id => '10592934818556549584',
     p_user_name => 'TEST',
     p_first_name => 'a',
     p_last_name => 'b',
     p_description => '',
     p_email_address=> 'test@test.com',
     p_web_password => 'E92903DEAD135E6E86BD6B64544D2BD9',
     p_web_password_format => 'HEX_ENCODED_DIGEST_V2',
     p_group_ids => '10592435401495787816:',
     p_developer_privs=> '',
     p_default_schema=> 'TEST',
     p_account_locked=> 'N',
     p_account_expiry=> to_date('201212040000','YYYYMMDDHH24MI'),
     p_failed_access_attempts=> 0,
     p_change_password_on_first_use=> 'Y',
     p_first_password_use_occurred=> 'N',
     p_allow_access_to_schemas => '');
     end;
     /

when I run this in 4.1.1 I had to modify it to the new format as below,
also changed the p_group_ids to new user group but kept the password the same

     begin
     wwv_flow_fnd_user_api.create_fnd_user (
     p_user_id => '',
     p_user_name => 'TEST',
     p_first_name => 'a',
     p_last_name => 'b',
     p_description => '',
     p_email_address=> 'test@test.com',
     p_web_password => 'E92903DEAD135E6E86BD6B64544D2BD9',
     p_web_password_format => 'HEX_ENCODED_DIGEST_V2',
     p_group_ids => '1399416797653068:',
     p_developer_privs=> '',
     p_default_schema=> 'TEST',
     p_account_locked=> 'N',
     p_account_expiry=> to_date('201209041006','YYYYMMDDHH24MI'),
     p_failed_access_attempts=> 0,
     p_change_password_on_first_use=> 'Y',
     p_first_password_use_occurred=> 'N',
p_allow_app_building_yn=> 'N',
p_allow_sql_workshop_yn=> 'N',
p_allow_websheet_dev_yn=> 'N',
p_allow_team_development_yn=> 'N',     
p_allow_access_to_schemas => '');
     end;
     /

the result was that the user is created fine, but the password is not valid.

Anyone knows how to export apex users with existing password to a new server?

Thanks.

Edited by: Danny on 3/12/2012 20:51
  • 1. Re: How to migrate Apex users with existing passwords.
    Prabodh Guru
    Currently Being Moderated
    Hi,
    Not sure why you say
    when I run this in 4.1.1 I had to modify it to the new format as below,
    If you just run the workspace export sql it should create the Workspace, Groups and Users

    The signature of the procedure is below. See the highlighted lines.
    procedure create_fnd_user (
    -- Description:
    -- This procedure allows for programatic and bulk creation of users.
    --
    -- Example:
    -- From sqlplus logged in as the privileged flows user, first
    -- ensure that the security group id is set properly, then create
    -- your users.
    --
    <b> -- begin wwv_flow_security.g_security_group_id := 20; end;</b>
    -- /
    --
    -- begin
    -- for i in 1..10 loop
    -- wwv_flow_fnd_user_api.create_fnd_user(
    -- p_user_name => 'USER_'||i,
    -- p_email_address => 'user_'||i||'@mycompany.com',
    -- p_web_password => 'user_'||i) ;
    -- end loop;
    -- commit;
    -- end;
    -- /
    --
    --
    -- Arguments:
    -- p_user_id numeric primary key of user
    -- p_user_name the username the user uses to login
    -- p_first_name informational only
    -- p_last_name informational only
    <b> -- p_web_password the unencrypted password for the new user</b>
    -- p_group_ids A colon delimited list of group IDs from the table wwv_flow_fnd_user_groups
    -- p_developer_privs A colon delmited list of developer privs, privs include:
    -- ADMIN:BROWSE:CREATE:DATA_LOADER:DB_MONITOR:EDIT:HELP:MONITOR:SQL:USER_MANAGER
    -- p_default_schema A valid oracle schema that is the default schema for use in browsing and
    -- creating flows
    -- p_allow_access_to_schemas A colon delimited list of oracle schemas that the user is allowed to
    -- parse as. If null the user can parse as any schema available to the company.
    -- This does not provide privilege it only resticts privilege, so listing a schema
    -- does not provide the privilege to parse as a schema, it only restricts that user
    -- to that list of schemas.
    -- p_attributes_XX These attributes allow you to store arbitary information about a given user.
    -- They are for use by flow developers who want to extend user information.
    <b> -- p_web_password_format Identifies the format of the web password.
    -- The range of values is CLEAR_TEXT, HEX_ENCODED_DIGEST, DIGEST </b>
    -- p_person_type "E" marks the user as external
    -->

    Note there is no HEX_ENCODED_DIGEST, DIGEST_V2 listed. It may work, but not obvious from the signature.
    Cheers,
  • 2. Re: How to migrate Apex users with existing passwords.
    Danny*D201 Journeyer
    Currently Being Moderated
    Thanks for replying.
    The exported file has p_web_password_format => 'HEX_ENCODED_DIGEST_V2', in it as default.

    I solved the issue by exporting (from 3.1.1) and importing (into 4.1.1) the whole workspace, it created users as well and retained their passwords.
  • 3. Re: How to migrate Apex users with existing passwords.
    846231 Newbie
    Currently Being Moderated
    Hi Danny,

    Kindly let us know how did you do the process? Is your apex 3.1 and 4.1 on the same database?

    Thanks a lot.

    Kinz
  • 4. Re: How to migrate Apex users with existing passwords.
    Danny*D201 Journeyer
    Currently Being Moderated
    Hi Kinz
    no the 3.1 and 4.1 are on different server and DB.
    You can export the workspace and users from Home>Application Builder>Application xxxx>Export / Import>Export > Export Workspace Users

    Just export the workspace & users then import them into 4.1 and check your apex users should be there and passwords are the same as 3.1.
    hope that helps.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points