Wednesday, May 14, 2008

Oracle EBS: Automating Changes of Username

As as typically the case within any organisation that has been around for a while the rules behind generating a user name will change. In our case the change was from initials (ARP01 - Andy Pellew) to a sort of initial/surname combination (APE06 - yes, one day I'll get together with apes 1-5 and we'll take over the world).

Oracle will allow you to update a single users user name as everything is stored against the USER_ID (from the FND_USER table under APPLSYS) in the database.

The package below (XXXX_APPUSERMANAGER) allows you to specify an old user name and a new user name and does the switch for you (after validating that it should let you). The source code for the package is below;

create or replace package XXXX_APPUSERMANAGER is

  procedure RenameUsers(p_ReturnMessage OUT VARCHAR2,
                        p_ReturnCode    OUT NUMBER,
                        p_OldUserName   IN VARCHAR2 default '',
                        p_NewUserName   IN VARCHAR2 default '');


And here is the required code for the package body;

create or replace package body XXXX_APPUSERMANAGER is

  c_NOTFOUND constant number := -998;
  c_BLANK    constant number := -999;

  /* ********** ********** ********** ********** ********** --
  -- PRIVATE Routines (available only to this package)      --
  -- ********** ********** ********** ********** ********** */
  function private_GetUser(p_Username in varchar2) return number as
    cursor c_GetUser is
      select u.user_id
        from applsys.fnd_user u
       where upper(u.user_name) = upper(p_UserName);
    v_UserId applsys.fnd_user.user_id%type;
    open c_GetUser;
    fetch c_GetUser
      into v_UserId;
    close c_GetUser;
    if v_UserId is null then
      if length(p_UserName) = 0 then
        v_UserId := c_BLANK;
        v_UserId := c_NOTFOUND;
      end if;
    end if;
    return v_UserId;
  end private_GetUser;

  function private_emailSubjectTag return varchar2 as
    v_Database v$;
    select distinct name into v_Database from v$database;
    return '[User Admin - ' || nvl(v_Database, 'Null') || ']';
  end private_emailSubjectTag;

  function private_passwordGenerator return varchar2 as
    -- This is not massively secure, but it's secure enough (providing you don't have external users!)
    c_ALPHA constant varchar2(255) := '1DFC84XSW3ERTGBZA52KI9JM6LOPYH70N';
    v_Position number := mod(to_number(to_char(sysdate, 'SSSSS')),
    v_Length   number := mod(to_number(to_char(sysdate, 'SSSSS')), 4) + 6;
    return substr(c_ALPHA || c_ALPHA, v_Position, v_Length);

  /* ********** ********** ********** ********** ********** --
  -- PUBLIC Routines (available globally)                   --
  -- ********** ********** ********** ********** ********** */
  procedure RenameUsers(p_ReturnMessage OUT VARCHAR2,
                        p_ReturnCode    OUT NUMBER,
                        p_OldUserName   IN VARCHAR2 default '',
                        p_NewUserName   IN VARCHAR2 default '') as
    pragma autonomous_transaction;
    v_OldUserId    number;
    v_NewUserId    number;
    v_RowCount     number;
    v_Password     varchar2(255);
    v_EmailAddress applsys.fnd_user.email_address%TYPE;
    v_URL          varchar2(255) := 'http://%HOST%:8000/OA_HTML/AppsLocalLogin.jsp?cancelUrl=/OA_HTML/AppsLocalLogin.jsp&langCode=US&username=' ||
    v_Host         v$instance.HOST_NAME%TYPE;
    procedure LogMessage(p_Text in varchar2) as
      apps.fnd_file.put_line(apps.fnd_file.log, p_Text);
    v_OldUserId := private_GetUser(p_OldUserName);
    v_NewUserId := private_GetUser(p_NewUserName);
    LogMessage('Renaming ' || p_OldUserName || '(' || to_char(v_OldUserId) ||
               ') to ' || p_NewUserName || '(' || to_char(v_NewUserId) || ')');
    -- If the user details are blank raise an error
    if (v_OldUserId = c_BLANK) or (Length(trim(p_NewUserName)) = 0) then
                              'ERROR: You cannot rename from/to a blank username!');
    end if;
    -- If the old user is not found then raise an error
    if v_OldUserId = c_NOTFOUND then
                              'ERROR: The user "' || p_OldUsername ||
                              '" does not exist');
    end if;
    -- If the old user is not found then raise an error
    if upper(p_OldUserName) = upper(p_NewUserName) then
                              'ERROR: Old and new usernames are the same!');
    end if;
    -- Get the email address, if there is no email address raise an error
    select email_address
      into v_EmailAddress
      from applsys.fnd_user u
     where u.user_id = v_OldUserId;
    if v_EmailAddress is null then
                              'ERROR: Unable to update user "' ||
                              upper(p_OldUserName) ||
                              '" because they do not have a valid email address');
    end if;
    -- If the old user *is* found then raise an error
    if v_NewUserId <> c_NOTFOUND then
                              'ERROR: The user "' || p_NewUserName ||
                              '" already exists');
    end if;
    -- Perform the rename
    LogMessage('Updating user record (ID=' || to_char(v_OldUserId) || ')');
    update applsys.fnd_user u
       set u.user_name = upper(p_NewUserName)
     where u.user_id = v_OldUserId;
    v_RowCount := SQL%ROWCOUNT;
    -- If anything other than a single record has been updated raise an error
    if v_RowCount <> 1 then
                              'ERROR: Updating the user record has failed (' ||
                              to_char(v_RowCount) || ')');
    end if;
    -- Reset the users password. This is necessary because the encryption on the account includes
    -- the Username in the key (so when we change the username we make it impossible for the user
    -- to login - hence the resetting of the users password to a known value).
    LogMessage('Initialising Oracle Application Suite (required for Password Change)');
    execute immediate 'alter session set NLS_LANGUAGE = AMERICAN'; -- oddly we need these lines
    execute immediate 'alter session set NLS_TERRITORY = AMERICA';
    apps.fnd_global.apps_initialize(0, 20420, 1); -- switch to SYSADMIN under apps.
    v_Password := private_passwordGenerator; -- this is a pretty poor generator, but is almost certainly secure enough for us.
    -- Get the hostname (necessary to customise the URLs).
    select lower(host_name) into v_Host from v$instance where rownum = 1;
    LogMessage('Changing the users password');
    fnd_user_pkg.UpdateUser(x_user_name                  => upper(p_NewUserName),
                            x_owner                      => 'SEED',
                            x_unencrypted_password       => v_Password,
                            x_password_lifespan_days     => '',
                            x_password_lifespan_accesses => '',
                            x_password_accesses_left     => '');
    -- Notify the user their account name and password have changed
    XXXX_emailmanager.addEmail(p_From    => v_EmailAddress,
                               p_To      => v_EmailAddress,
                               p_Subject => private_emailSubjectTag ||
                                            ' User/Password Change Notification',
                               p_Body    => '

Your username (which was "' ||
                                            upper(p_OldUserName) ||
                                            '") has been changed to "' ||
                                            upper(p_NewUserName) ||
". Your password has also been reset to "' ||
                                            v_Password ||
                                            '" (without the quotes).

Please click the Oracle link below to logon and change your password:
' ||
                                            Replace(v_URL, '%HOST%', v_Host) ||

If you have any problems accessing the system please contact the IT Helpdesk.
    p_ReturnMessage := 'OK';
    p_ReturnCode    := 0;
    when others then
        p_ReturnMessage := '(' || TO_CHAR(SQLCODE) || ') ' || SQLERRM;
        p_ReturnCode    := 1;
        logMessage('999 ERROR: (' || TO_CHAR(SQLCODE) || '): ' || SQLERRM);
        XXXX_emailmanager.addEmail(p_From    => '',
                                   p_To      => '',
                                   p_Subject => private_emailSubjectTag ||
                                                ' Error',
                                   p_Body    => '999 ERROR: (' ||
                                                TO_CHAR(SQLCODE) || '): ' ||
                                                SQLERRM ||
Please raise this issue with the helpdesk.');
  end RenameUsers;


Now the bad news; this won't work (yes, I know that's a bit of a biggie). For copyright reasons several routines are missing from the package, the routine that sends the notification email to the user letting them know that their user name and password have been changed. In the packages these routines are referred to as:
XXXX_emailmanager.addEmail, and

The former allows you to add an e-mail to a queue, the latter processes all emails in the queue (rather than waiting for an automated process to pick the new email up).

These are custom packages that sit on top of two packages from oracle; BASE64ENCODER and EMAILER. Unfortunately the links I have for these packages are no longer working but as this is pretty standard functionality that most people would want to do it shouldn't be too hard to find a replacement (or the packages themselves - try google).

One day it's my goal to write an installation script to get everything installed nicely, but that goal is not going to be met today (and as I'm updating this post 3 years later and I still haven't done it it's looking like "never" is the window of time this work will be dropping into!).