Re: migrating from 8.1.7 to 9i how to keep same user_id

fitzjarrell_at_cox.net wrote:
> BGT wrote:
> > That won't do anything to guarantee that the USER_ID comes out the
> > same, only the username and password.
>
> Correct, and basing application code upon a dbms generated USER_ID
> value is folly, in my opinion. It appears you're due to start
> rewriting code, and possibly your schema.
>
>
> David Fitzjarrell

After verifying my initial thoughts I don't understand how moving the orapwd file between databases preserves your USER_ID. The password file doesn't list the internal Oracle USER_ID values, so why does moving it preserve them? It can't. Also, the orapw file only containts entries for adminsitrative users -- those with SYSOPR or SYSDBA granted to them. You don't intend to tell us your application users all have either SYSOPER or SYSDBA grants, do you? That would be a serious breach of security for your database and data.

It appears you've been lucky with respect to USER_ID values between databases, as the orapw flie has nothing to do with them, and nothing to do with regular, non-administrative application users. The only way to even remotely hope you'll have the same USER_ID for a given USERNAME is to create the users in the same order in the new database as they were created in the existing database. This also presumes the same starting user list after the database has been created (SYS, SYSTEM, DBSNMP, CTXSYS, OUTLN, etc.) exists, providing the same 'point of entry' into the USER_ID list for your application user accounts.

Moving the orapw file doesn't preserve USER_ID values. The orapw file ONLY contains entries for administrative accounts, none of which should be your application users. All it possibly could preserve is the passwords for your administrative users. And, only if the ORACLE_SID for the new database is the same as that for the old database.

David Fitzjarrell Received on Sat Jun 18 2005 - 07:03:32 CDT