Recreate Users, Groups, and Roles
Note: This step is required only for a clean-installation upgrade.
If your 6.4 installation has only a few Ingres users defined, you should use the accessdb utility or the CREATE USER SQL statement to recreate those users in the Ingres installation. As a guide, use the file users.out or refer to the 6.4 installation.
If you have many users, the following procedure recreates them in mass.
As the installation owner, change directory to your iidbdb unloaddb directory where you stored the files from the step Save Users, Groups, and Roles.
Run this SQL:
sql '-u$ingres' iidbdb
copy iiuser(name=c0comma,status=c0comma,default_group=c0nl)
from 'users.out'
\go
update iiuser
set default_priv = status, user_priv = status,
flags_mask = case when default_group <> ' ' then 28 else 24 end
where user_priv = 0 and flags_mask = 0;
\go
copy iiusergroup(groupid=c0comma,groupmem=c0nl)
from 'groups.out'
\go
commit
\go
\quit
Windows: Omit the quotes from the sql command line.
Ingres has new user privileges that do not exist in 6.4. If you recreate users using the above bulk load procedure, you should review the added users with accessdb to make sure that all user privileges are set the way you want them. In particular, review the definitions for any 6.4 “superusers.”
Ingres handles the “update system catalog” privilege differently than did 6.4. You must explicitly grant this privilege to the Ingres user after you recreate it, with a grant statement, as follows:
grant update_syscat on current installation to user-name
If your 6.4 installation had roles defined, recreate them with the ADD ROLE SQL statement. Use the file roles.out as a guide. Roles cannot be reliably bulk-loaded from the 6.4 installation, so you must recreate them by hand. After you recreate each role, issue the following SQL statement:
grant rolename to public; commit
This allows the role to be used in the same manner as in 6.4.