Bug No. 428379 Filed 05-DEC-1996 Updated 27-SEP-1999 Product Oracle Server - Enterprise Edition V7 Product Version 7.3 Platform Intel Windows NT Platform Version 4 RDBMS Version 7.3 Affects Platforms Generic Priority Severe Loss of Service Status Q/A To Development Base Bug N/A Fixed in Product Version 8.0.6 Problem statement: FULL DB IMPORT W/ REMOTE_LOGIN_PASSWORDFILE EXCLUSIVE CHANGES INTERNAL PASSWORD -------------------------------------------------------------------------------- Hdr: 428379 7.3 RDBMS 7.3 SECURITY PRODID-5 PORTID-912 ORA-1031 Abstract: FULL DB IMPORT W/ REMOTE_LOGIN_PASSWORDFILE EXCLUSIVE CHANGES INTERNAL PASSWORD *** MFOWLER 12/05/96 08:42 am *** If password gets changed, you can delete %ORACLE_HOME%\database\pwd.ora, run Instance Manager, double click on sid, and enter/confirm password. *** MFOWLER 12/05/96 08:45 am *** Full import with remote_login_passwordfile=shared, will not effect internal password *** MFOWLER 03/18/97 12:57 pm *** (CHG: Pri->2) *** MFOWLER 03/18/97 12:57 pm *** Customer keeps loosing his internal password when he imports full database. He even makes the password file read only, and the time stamp is not changed, but he will no longer be able to connect internal. This happens often and creates a problem for the customer. *** DCOLELLO 03/25/97 11:28 am *** (CHG: G/P->G Asg->RDBMSREP) *** JNATH 03/25/97 01:10 pm *** (CHG: Asg->AMSRIVAS) *** JNATH 03/25/97 01:11 pm *** (CHG: Asg->AMSRIVAS) *** AMSRIVAS 05/06/97 05:44 am *** (CHG: Sta->30) *** AMSRIVAS 05/06/97 05:44 am *** can you please provide me with a reproducible test case for this . thanks *** AMSRIVAS 05/13/97 02:52 am *** (CHG: Sta->33) *** AMSRIVAS 05/13/97 02:52 am *** please reset back to 11 once the requested info is available. Thanks, *** MSVIHLIK 07/15/97 05:20 am *** Customer of mine is running into absolutely same problem. Full database import has to be followed by resetting internal password and creating new password file. Can you update on this? *** SOWENS 09/08/97 07:40 am *** I have a customer with a case simular and a reproducable one on OS2 it is contained in bug 489944. *** MFOWLER 09/08/97 08:15 am *** (CHG: Sta->11) *** MFOWLER 09/08/97 08:15 am *** Reproducible testcase: Fresh install with starter database. Perfrom a full export of the database. Change remote_login_passwordfile to exclusive in initorcl.ora restart the database import the dump file. (full database import) password for internal should not work. *** AMSRIVAS 11/07/97 02:11 am *** (CHG: Sta->30) *** AMSRIVAS 11/07/97 02:11 am *** I am not able to reproduce this, I guess I might be missing something: this is what I tried, the steps are in sequence: (1) fresh start a database (2) create a password file using the following command: orapwd file=/vobs/oracle/dbs/orapwexptest password=test (3) Do a full export (4) add the following parameter in the init.ora file: REMOTE_LOGIN_PASSWORDFILE= EXCLUSIVE (5) shutdown the DB (6) restart it. (7) Do a full import. password for internal is still valid. is there someting I am missing here ? please provide a readme file on wrvms to recreate this . thanks. *** KINOUE 11/20/97 08:33 pm *** (CHG: Sta->11) The procedure above seems perfect to reproduce this bug. Did you try "connect internal" from remote client? Are you sure you're not in dba group? . Also, can you do "imp show=y full=y |& grep "ALTER USER"? There's probably a line which looks like, "ALTER USER "SYS" IDENTIFIED BY VALUES 'D4C5016086B2DC6A' TEMPORARY TABLESPA" . If this statement is run, password for internal is also changed by spec. Related bug: 575598, 476428. *** KINOUE 11/21/97 12:35 am *** Now I know the real problem. Full import does change internal's password to sys's. This is probably by specification. The real problem is that change is not done correctly. I found that internal's password is changed to D4C5016086B2DC6A. (look above) I tested 7.3.4 on Intel NT and SPARC Solaris. This, of course, does not happen if I do "alter user sys identified by blah" manually in SQL*Plus. *** AMSRIVAS 11/21/97 01:05 am *** (CHG: Sta->30) *** AMSRIVAS 11/21/97 01:05 am *** I am lost with this one :) would appriciate if you could provide me with a script to reproduce this. also when i tested this i was in the dba group and i did do a connect internal. thanks. *** KINOUE 11/24/97 11:51 pm *** test script is wrvms.us:bug$/bug428379/bug428379.sh. TCP listener is required for this script to run. Just do "% source bug428379.sh". *** KINOUE 11/24/97 11:51 pm *** (CHG: Sta->11) *** AMSRIVAS 01/20/98 12:24 am *** (CHG: Sta->32) *** AMSRIVAS 01/20/98 12:24 am *** now that I understand this better. I think this si not a bug but a result of a restriction on part of exp/imp. In a Full Database Mode export a Privileged users can export and import all database objects except those owned by SYS: we do not export objects owned by SYS as they are default for database creation. hence after a full export/import we end up having the default password for SYS . *** KINOUE 01/20/98 03:32 am *** (CHG: Sta->11) I tested on Solaris and NT and never once ended up in 'CHANGE_ON_INSTALL'. Is that what you mean? *** AMSRIVAS 01/22/98 05:08 am *** (CHG: Sta->32) what i mean is that export dose not export objects that belong to user SYS. this is a restriction on part of exp/imp. so at the end of a full import we end up with the default password. *** KINOUE 01/25/98 03:56 pm *** . Apparently, SYS's password is exported with full=y and imported back again . *** KINOUE 01/25/98 04:07 pm *** (CHG: Sta->11) when importing full=y. *** AMSRIVAS 02/01/98 09:24 pm *** (CHG: Asg->PMOTHKUR) *** AMSRIVAS 02/01/98 10:27 pm *** the utility ORAPWD is setting the password only for user INTERNAL and not for SYS also. export is picking up the correct password for user SYS if changed from svrmgrl. thanks. *** WBORNICK 05/05/98 01:24 pm *** I have a customer (Tar 9215344.7) that ran into this issue last night. He will set Remote_login_passwordfile=Exclusive and then run the Import as a workaround. Any update on this bug ? *** PTRENT 05/28/98 11:09 am *** I have another customer that ran into this same issue on a NetWare server using remote_login_passwordfile=shared to startup the database is a workaround but this should be resolved. In my customers case the import came into a 8.0.3 rdbms. The source database was 7.1.4. *** PMOTHKUR 07/09/98 06:36 pm *** (CHG: Sta->30) *** PMOTHKUR 07/09/98 06:36 pm *** finally, got a chance to work on this one. i have tried the script on 7336 with the server on the same machine. i am able to connect using svrmgrl using any of the three passwords successfully orapwd, change_on_install,D4C5016086B2DC6A and REMOTE_LOGIN_PASSWORDFILE is not set. i have followed your script as given in bug428379.sh *** KINOUE 07/09/98 10:40 pm *** Please read the whole text carefully. Set REMOTE_LOGIN_PASSWORDFILE=exclusive. and try with the account who doesn't belong to DBA group. *** KINOUE 07/09/98 11:26 pm *** (CHG: Sta->11) *** PMOTHKUR 07/10/98 10:23 am *** (CHG: Sta->30) *** PMOTHKUR 07/10/98 10:23 am *** what do you mean by account who doesn't belong to DBA group? your script uses internal and sys users. thanks. *** KINOUE 07/12/98 07:30 pm *** (CHG: Sta->11) I mean UNIX OS user account. People usually use 'oracle' user account who belongs to 'dba' group to start up the instance. This works without password. *** PMOTHKUR 07/13/98 01:11 pm *** (CHG: Sta->30) *** PMOTHKUR 07/13/98 01:11 pm *** why do you need an account who doesn't belong to dba group? How is this relevant to this problem? we are using internal and sys users. thanks. *** KINOUE 07/14/98 06:13 pm *** (CHG: Sta->11) Since 'dba' group users can logon as internal without password, you need to create UNIX OS user account who's not in dba group to reproduce this problem. *** KINOUE 08/06/98 06:40 pm *** This bug reproduces without using imp. I tested below on 8.0.5 Solaris. SVRMGR> alter user sys identified by sys; SVRMGR> select username,password from dba_users where username='SYS'; USERNAME PASSWORD ------------------------------ ------------------------------ SYS-4DE42795E66117AE SVRMGR> alter user sys identified by blah; SVRMGR> connect sys/blah Connected. SVRMGR> connect internal/blah Connected. SVRMGR> alter user sys identified by values '4DE42795E66117AE'; -- reverting pw SVRMGR> connect sys/sys Connected. SVRMGR> connect internal/sys ORA-1031: insufficient privileges SVRMGR> connect internal/4DE42795E66117AE Connected. *** PTRENT 10/27/98 06:16 am *** Any update on this bug? This is still affecting customers. *** PMOTHKUR 10/30/98 12:49 pm *** discovered the problem. we are encrypting the password which is already encrypted again for INTERNAL. I'll be checking in the fix soon. *** PMOTHKUR 11/16/98 10:05 am *** fix is in review. *** PMOTHKUR 11/19/98 10:14 am *** (CHG: Sta->80) *** PMOTHKUR 11/19/98 10:14 am *** (CHG: Fixed->8.0.6) *** PMOTHKUR 11/19/98 10:14 am *** fix checked into 8.0.6 transaction: pmothkur_bug-428379 files: dac/kzu.c/main/st_rdbms_big_dev/19 dac/kzsr.c/main/st_rdbms_big_dev/17 security/if/kzsr.h/main/st_rdbms_big_dev/4 *** PMOTHKUR 11/19/98 10:16 am *** ]]When you execute 'alter sys identified by values '98kajf89558', connections ]] to internal fails. *** PMOTHKUR 05/05/99 01:54 pm *** forward merged to 8.1.6 files: dac/kzu.c/main/59 dac/kzsr.c/main/23 security/if/kzsr.h/main/10 *** PMOTHKUR 09/27/99 03:28 pm *** PLEASE NOTE: after the 'alter sys identified by values ...' execution, the customer has to change the sys password again so that the sys password and the internal password will be in sync. in the above testcase, the customer has to change the sys password again after the import.