Post by Peter Gale-----Original Message-----
Sent: 02 March 2005 10:10
Subject: Re: [Info-ingres] How to retrieve Ingres Users information
Post by Frank FanHi Peter and Hans,
Unfortunately, we ran ingres 6.4 in old server and
ran ingres 2.6
Post by Frank Fanin new server . Ingres 6.4 don't has SQLscript option .
Could I just find the table which hold user
information and load
Post by Frank Fanit into new server? It seems like iiuser hold the information.
I tried the following steps.
At one time there was a file shipped with 6.4 systems called
us_18622.doc
that described how to do what you need. (It's been a long
time since I did
it, so I don't recall the steps; all I have is a note of the
document name.)
I found the attached doc US38622, which describes the 6.4 'users' file
and how to transfer user info. If you're going from 6.4 -> 2.6 then
there are extra columns in the 2.6 iiuser table so rather than try to
update it directly I would decode the status flag using the info in the
doc and use it to create a CREATE USER statement. It should be fairly
straightforward to create a script to do this automatically.
Alternatively you could follow the r3 Migration Guide, chapter 5 - step
11 to unload, step 28 to reload.
(http://opensource.ca.com/projects/ingres/documents/product/ingresr3/A00
1931E.pdf)
HTH
Paul
*
________________________________________________________________________
____ *
* ID: 71
* PRODUCT: INGSRV
* RELEASE: 6.4/00
* DESC: The "Using the 'Users' File" section of your
Database Admini
* SYSTEMS AFFECTED: ANY
* SOLUTION TEXT:
****************************************************
* INGRES EXPERT NOTE *
****************************************************
The "Using the 'Users' File" section of your Database Administrator's
Guide,
chapter "Authorizing User Access". How INGRES uses the USERS file.
Equivalent
to Release 6 Technical Note #1 or Release 5 Note #7.
Using the "Users" File
======================
With the "users" file, you can authorize a large number of
new users. This is typically done at installation time. This
section tells you how to build a new "users" file.
o User Validation
o Adding a Large Number of New Users
o Building a "Users" File
o Copying an Existing "Users" File
o Restoring the "Users" File
User Validation
---------------
INGRES keeps track of valid INGRES users in two places:
o An INGRES system catalog
o The "users" file
This file is located below II_FILES
(default location is in the directory:)
UNIX: $II_SYSTEM/ingres/file.
VMS: II_SYSTEM:[ingres.files]
The "users" file is created during the INGRES install
procedure, when INGRES is installed for the first time. It
provides the initial entries for one of the INGRES system
catalogs that is created when the master database ('iidbdb')
is created. That system catalog contains the logins of users
authorized to use INGRES. Each time a user tries to use
INGRES, his or her login is compared against the entries in
the catalog.
Because INGRES uses both the system catalog and the "users"
file, they must remain consistent with each other.
Adding a Large Number of New Users
----------------------------------
A "users" file can be used to add a large number of new
users at one time. You will probably want to do this at
installation time. This method can also be used subsequently
to add multiple users to an existing installation.
"Users" File at Installation
- - - - - - - - - - - - - -
To add a large number of new users all at once when INGRES
is installed:
1. Prepare the "users" file.
o If you are generating a new "users" file, see the
section "Building a 'Users' File" for details.
o You can alternatively use an existing "users"
file. See the section "Copying an Existing 'Users'
File."
2. Have your System Administrator run 'iibuild' using this
file instead of the version of the "users" file that
'iibuild' has.
For details on running 'iibuild' and installing INGRES,
please refer to your "INGRES Installation and
Operations Guide".
"Users" File After Installation
- - - - - - - - - - - - - - - -
The "users" file can be used to add a large number of users
after INGRES is installed. In addition to changing the
"users" file, you must copy the changes into the system
catalog.
1. Edit the "users" file to add the new users. The file
should be located in the $II_SYSTEM/ingres/files
directory.
2. Enter the 'iidbdb' as the 'ingres' user:
$ sql +U -u'$ingres' iidbdb
Notice
Since the 'iidbdb' database is exclusively locked at
this point (because of the "+U" flag) no new users can
log in until you exit.
3. Issue a 'copy' command to copy in the "users" file from
where it is located:
copy table iiuser (name = char(0)'!',
gid = char(0)'!',
mid = char(0)'!', status = char(0)nl)
from '$II_SYSTEM/ingres/files/users';
Note
You can ignore the "duplicate key" warning messages
that may be issued during the copy. These are caused by
rows in the "users" file that are for users already
defined to INGRES.
4. Exit from the terminal monitor.
Building a "Users" File
-----------------------
Only the 'ingres' superuser has permission to edit the
"users" file.
To build the "users" file, add an entry for each new user.
The entry consists of a line containing four fields, each of
which is separated with an exclamation point ('!'). The
format for each field is as follows:
o The username in lowercase in the first field
o A zero ('0') in the second field (currently not used)
o A zero ('0') in the third field (currently not used)
o A fourth field, the status code field, which contains a
number representing the permissions you can assign to
the user
This field can contain a single number representing a
single permission or it can contain a sum, representing
any two, three, or all four permissions. The codes for
each permission are shown in the following table:
Permission Codes
Status Code: Permission:
100000 Superuser permission
20 Ability to set trace flags
4 Ability to update system catalogs
1 Ability to create databases
0 No privileges
The following table provides examples of status codes
representing more than one permission.
Multiple Permission Code Examples
Status Code: Permission:
100001 Superuser and the ability to
create databases
100025 All privileges
24 Set trace flags and update system
catalog
An example of an entry for user "SAMIAM" with the
superuser, update system catalogs and create databases
permissions is: "samiam!0!0!100005".
Copying an Existing "Users" File
--------------------------------
If you are creating a new installation, you can add new
users by using a copy of the "users" file of another INGRES
installation with the same INGRES release level as yours.
If you edit the "users" file, do so with the names of new
users, their accounts, and permissions BEFORE the master
database has been created, during the 'iibuild' procedure.
Then, when the master database is created, the INGRES system
catalogs will be current and consistent with the "users"
file.
If you edit the "users" file after the master database is
created, the listing in the system catalog is not updated.
Thus, this is not a way to add new users. To add users with
a "users" file after installation, see the section "'Users'
File After Installation."
Restoring the "Users" File
--------------------------
If your installation's "users" file is destroyed, you can
recover it. You must create a temporary "users" file in the
$II_SYSTEM/ingres/files directory with the following lines
in it:
$ingres!0!0!100025
ingres!0!0!100025
root!0!0!100025
These lines comprise the template with which a new INGRES
installation begins.
Then use the following procedure to run 'accessdb' from the
INGRES account:
1. Start 'accessdb' by issuing the following command at
the operating system prompt:
$ accessdb
2. Select 'User' from the 'accessdb' main menu.
3. At the following prompt, specify the 'root' account by
typing 'root':
User name: root
4. Select 'Save' from the menu to save the changes you've
just entered. You do not actually need to make a change
to this screen. The "users" file will be updated with
information from the appropriate system catalog and
contain an entry for every INGRES user account that
exists in the system catalog.
5. Select 'Quit' to leave 'accessdb'.
Releases Affected: ALL.ALL 6.4/00 -
*
________________________________________________________________________
____ *
* ANY VERSION: 0 EFFECTIVE: 12/02/1991 ACTION: A
*** NO ZAPS FOR THIS VERSION ***