Prior to starting the infamous Banner® 'gostage' upgrade, you are required to change all table owner passwords back to 'u_pick_it' or modify login.sql. Both are rather laborious processes and requires you to know what these passwords are.
A better way to do this takes advantage of an Oracle trick with the ALTER USER SQL.
Oracle stores passwords in encrypted form in the database. These can be seen when you select from the DBA_USERS dictionary table.
SQL> select username , password from dba_users;
USERNAME PASSWORD
------------------------------ ------------------------------
SYS F5F610AB7C9A65C6
SYSTEM DA487CA15C1583AF
OUTLN A16FDADE19B70AF8
DBSNMP E066D214D5421CCC
GENLPRD 828B7AC8DD79A633
SAISPRD EAF5305E85E740AC
ADISPRD E56111519F4263C1
HRISDAT 917286A8D3337AE6
MICRPRD 9080B4431019968A
EWQSUSR 928815A725D769C5
FAISDAT 59412FCC6D052B36
What Oracle does not say too often, is that you can set a user password to the encrypted value regardless of whether you know the password of not.
The is done using the ALTER USER IDENTIFIED BY VALUES SQL.
SQL> alter user SYSTEM identified by values 'F5F610AB7C9A65C6';
This construct is extremely powerful because it gives the DBA the ability changes a user account password to something else, then CHANGE IT BACK to the original value without knowing what it is.
To utilize this for a Banner® upgrade... we can do the following.
1. Prior to an upgrade we'll want to save all users' original passwords.
We can do this by running the following script, savbanpw.sql.
REM start savbanpw.sql
set head off
set verify off
set echo off
set time off
set timing off
set pagesize 2000
spool resetbanpw.sql
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='UPGRADE1';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='BWPMGR';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='BWLMGR';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='BWFMGR';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='BWAMGR';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='BWSMGR';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='BWGMGR';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='BANIMGR';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='WTAILOR';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='BANINST1';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='BANSECR';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='BPISMGR';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='TRANMGR';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='GENERAL';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='SATURN';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='TAISMGR';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='ALUMNI';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='FIMSARC';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='FIMSMGR';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='PAYROLL';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='POSNCTL';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='FAISMGR';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='CIMSMGR';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='UIMSMGR';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='MICROFA';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='DCRSMGR';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='LIMSMGR';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='XRISMGR';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='LIMSARC';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='SYSTEM';
select 'alter user ',username,' identified by values ', ''''||password||'''',';' from dba_users
where username='SYS';
spool off
Running this script will generate another sql file called resetbanpw.sql which will hold all your owners's original passwords.
2. Now change all your passwords to 'u_pick_it' using the following, chgbanpw.sql.
ReM start chgbanpw.sql
alter user UPGRADE1 identified by u_pick_it;
alter user SYS identified by change_on_install;
alter user SYSTEM identified by manager;
alter user BANIMGR identified by u_pick_it;
alter user WTAILOR identified by u_pick_it;
alter user BANINST1 identified by u_pick_it;
alter user BANSECR identified by u_pick_it;
alter user BWGMGR identified by u_pick_it;
alter user BWAMGR identified by u_pick_it;
alter user BWSMGR identified by u_pick_it;
alter user BWFMGR identified by u_pick_it;
alter user BWLMGR identified by u_pick_it;
alter user BWPMGR identified by u_pick_it;
alter user BPISMGR identified by u_pick_it;
alter user TRANMGR identified by u_pick_it;
alter user GENERAL identified by u_pick_it;
alter user SATURN identified by u_pick_it;
alter user TAISMGR identified by u_pick_it;
alter user ALUMNI identified by u_pick_it;
alter user FIMSARC identified by u_pick_it;
alter user FIMSMGR identified by u_pick_it;
alter user PAYROLL identified by u_pick_it;
alter user POSNCTL identified by u_pick_it;
alter user FAISMGR identified by u_pick_it;
alter user CIMSMGR identified by u_pick_it;
alter user UIMSMGR identified by u_pick_it;
alter user MICROFA identified by u_pick_it;
alter user DCRSMGR identified by u_pick_it;
alter user LIMSMGR identified by u_pick_it;
alter user XRISMGR identified by u_pick_it;
alter user LIMSARC identified by u_pick_it;
3. Now run the upgrade.
4. When the upgrade is done, to change all passwords back, run resetbanpw.sql to restore all the users' original passwords!
