Re: [Info-Ingres] User Creation File
- From: "Jean-Luc Hampton" <Jean-Luc.Hampton@xxxxxxxxxx>
- Date: Tue, 9 Jun 2009 09:41:46 -0400
Hi Steph,
You will find the script to save and recreate the users within the
migration guide "mg.pdf".
But this may be not enough for recreating a contingency db on a remote
server.
Don't forget database privileges, locations and extended locations on
the other installation if required.
Record Database Information
For each database, you will need to know information such as whether the
database was journaled, where the database resides, and in what order
the data locations were configured. To record database information :
1. Run infodb against each database. Issue the following command:
infodb dbname >infodb.out
Save the output for later.
2. Record whether the database is public or private.
To find out, use the catalogdb command. Select Databases, and then enter
the database name. The screen that appears has an Access field that
indicates whether the database is public or private.
Record Database Privileges
To record database privileges
1. As the installation owner, change directories to the unload directory
for iidbdb created in Step 1 of the upgrade procedure.
2. Run the following SQL to save user database privileges:
sql iidbdb \script dbprivs.out select * from iidbprivileges where
database_name <> '' order by database_name,grantee_name \go \script
\quit
The file dbprivs.out is created for future reference.
Save Users, Groups, and Roles
To save users, groups,and roles
1. As the installation owner, change directory to the iidbdb unload
directory created in Step 1 of the upgrade procedure.
2. Run the following SQL to save users, groups, and roles:
sql iidbdb copy iiusergroup ( groupid=c0comma,groupmem=c0nl ) into
'groups.out' \go copy iirole( roleid=c0nl ) into 'roles.out' \go create
table role_tmp as select role_name,grantee_name from iirolegrant where
admin_option <> 'Y' \go copy role_tmp( role_name = c0comma, grantee_name
= c0nl ) into 'rolegrants.out'; drop role_tmp; \go \quit
3. Run accessdb, and select Users, then SqlScript.
A file called users.sql is written that will recreate all users, as they
are currently defined.
At this junction, you should have all you need to restore your
installation somewhere else on the new/remote/failover installation
Run ingstart to start Ingres. Obvious ? OK, fine.
Recreate Users, Groups, and Roles:
To recreate users, groups, and roles:
1. As the installation owner, change directory to your iidbdb unloaddb
directory where you stored the files from the step Save Users, Groups,
and Roles of this upgrade procedure
2. Run this SQL to recreate users and groups:
sql '-u$ingres' iidbdb copy iiusergroup(groupid=c0comma,groupmem=c0nl)
from 'groups.out' \go commit \go \read users.sql commit \go \quit
Windows ? (Beurrrk... J): Omit the quotes from the sql command line.
The file users.sql may try to recreate some users that already exist in
the installation, such as the installation owner and root user.
This will cause "E_US18B6 The user 'name' already exists" errors. You
can ignore these errors.
3. If your original 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 original installation, so
you must recreate them by hand. After you recreate each role, issue the
following SQL statement:
grant rolename to user; commit
The most common user here is public. You can use the file rolegrants.out
to determine what role grants are needed.
Recreate Locations:
To recreate locations:
1. Refer to each infodb output saved in the step Record Database
Information of this upgrade procedure.
2. Create any location that is not a default installation location
(ii_database, ii_checkpoint, ii_journal, or ii_dump). For more
information about creating locations, see the Database Administrator
Guide.
Extend the Database:
To extend each database:
1. Refer to the infodb output saved in the step Record Database
Information of this procedure.
2. If the database was extended to data locations other than the default
location, run accessdb as the installation owner and extend the
newly-created databases to the same locations. The locations will
already exist; it is only necessary to extend the databases to use them.
If you prefer a non-interactive command line utility, you can use the
extenddb utility instead of accessdb.
Recreate Database Privileges:
To recreate database privileges:
1. As the installation owner, change to the iidbdb unloaddb directory.
2. Refer to the file dbprivs.out created in the step Record Database
Privileges.
Each row in the dbprivs.out file describes one or more database
privileges given to the user grantee-name.
A Y or N in a privilege column indicates the specific privilege. (A U in
a column means "Unchanged.")
3. Start an iidbdb Terminal Monitor session:
sql iidbdb
4. For each row, issue the statement:
grant privilege on database database-name to grantee-name;commit
If the privilege column is N, grant noprivilege instead of privilege.
5. When finished, use \quit to exit the iidbdb session.
The structure of the iidbpriv catalog did not change between OpenIngres
1.x and Ingres 2.6, so it is possible to copy the original contents of
the catalog directly. However, we do not recommend this because the
catalog may change in future releases.
If you have defined many privileges, or recreated many users, groups, or
roles, you should run sysmod on the iidbdb, which will accelerate query
processing. Issue the sysmod command, as follows:
sysmod iidbdb
Recreate Locations:
To recreate locations:
1. Refer to each infodb output saved in the step Record Database
Information of this upgrade procedure.
2. Create any location that is not a default installation location
(ii_database, ii_checkpoint, ii_journal, or ii_dump). For more
information about creating locations, see the Database Administrator
Guide.
Recreate the Database:
Before creating each database, refer to the infodb output saved in the
step Record Database Information of this upgrade procedure. Look at the
location names for ROOT, JOURNAL, CHECKPOINT, and DUMP. If these are not
ii_database, ii_journal, ii_checkpoint, or ii_dump, you must specify the
location to createdb with the -d, -j, -c, or -b flags, respectively.
Also, refer to the database access information recorded in that step. If
the database access was "private," you must use the -p flag for
createdb.
If all the database locations are the default, and the database is
public, you can omit the flags on the createdb command line.
Recreate each user database, omitting the front-end catalogs. (The
front-end catalogs will be created as part of the reload.) Use the
following command:
createdb dbname flags -f nofeclients
Note: For an Ingres Star database, run createdb/star for the DDB. Do not
run createdb for the CDB.
Extend the Database:
To extend each database:
1. Refer to the infodb output saved in the step Record Database
Information of this upgrade procedure.
2. If the database was extended to data locations other than the default
location, run accessdb as the installation owner and extend the
newly-created databases to the same locations. The locations will
already exist; it is only necessary to extend the databases to use them.
If you prefer a non-interactive command line utility, you can use the
extenddb utility instead of accessdb.
Recreate Database Privileges
To recreate database privileges:
1. As the installation owner, change to the iidbdb unloaddb directory.
2. Refer to the file dbprivs.out created in the step Record Database
Privileges.
Each row in the dbprivs.out file describes one or more database
privileges given to the user grantee-name. A Y or N in a privilege
column indicates the specific privilege. (A U in a column means
"Unchanged.")
3. Start an iidbdb Terminal Monitor session:
sql iidbdb
4. For each row, issue the statement:
grant privilege on database database-name to grantee-name;commit
If the privilege column is N, grant noprivilege instead of privilege.
5. When finished, use \quit to exit the iidbdb session.
The structure of the iidbpriv catalog did not change between OpenIngres
1.x and Ingres 2.6, so it is possible to copy the original contents of
the catalog directly. However, we do not recommend this because the
catalog may change in future releases.
If you have defined many privileges, or recreated many users, groups, or
roles, you should run sysmod on the iidbdb, which will accelerate query
processing. Issue the sysmod command, as follows:
sysmod iidbdb
Upgrading
And as bonus and between us only because these accessdb options are
absolutely unsupported !
(But not forbidden J)
accessdb -I<file_name>
(Big I like India in the international alphabet)
anything you will do using accessdb till exiting will be recorded into
this <file_name> output file.
When you need to replay the same sequence:
accessdb -Z<file_name>
Magic, isn't it ?
Enjoy !
Cheers,
Oncle JL
From: info-ingres-bounces@xxxxxxxxxxxxxxxxxxxxxxxxx
[mailto:info-ingres-bounces@xxxxxxxxxxxxxxxxxxxxxxxxx] On Behalf Of
McCulloch, Stephen
Sent: mardi 9 juin 2009 10:48
To: 'Ingres and related product discussion forum'
Subject: [Info-Ingres] User Creation File
Hi All
Has anyone written a user creation script on Unix Solaris that emulates
the user SQLscript function in ACCESSDB.
I want to be able to run this script on a regular basis and transfer the
output file to a contingency db on a remote server.
Thanks
________________________________
************************************************************************
*********************
The information contained in this e-mail is confidential and is intended
only for the named recipient(s). If you are not the intended recipient,
you must not copy, distribute or take any action or reliance on it. If
you have received this e-mail in error, please notify the sender. Any
unauthorised disclosure of the information contained in this e-mail is
strictly prohibited.
The views and opinions expressed in this e-mail are the senders own and
do not necessarily represent the views and opinions of Falkirk Council.
************************************************************************
*********************
- Prev by Date: [Info-Ingres] createdb failed
- Next by Date: Re: [Info-Ingres] createdb failed
- Previous by thread: [Info-Ingres] User Creation File
- Next by thread: [Info-Ingres] createdb failed
- Index(es):
Relevant Pages
|
