Database ownership change

In article , sue@netcom.com (Sue Miller) writes:
> We have a difficult upgrade situation with our application under
> INGRES 6.3 (UNIX, Pyramid platform).
>
> Problem #1
> The database for the current customer release has
> been created by 'ingres' (don't ask), and we have changed this
> in the current so that an administrative account shall own it.
> How do we upgrade this thing in the field such that we don't have to
> copy out the old database as 'ingres' and create & copy it in as
> the new owner?  We can script this, but still - seems like a lot
> of work.

The only method that Ingres supports is using copydb or unloaddb, and
these have a few minor, though annoying, bugs.  I have worked up a
script (for VMS) to change database ownership directly, without
unloading and reloading.  It works, so it _can_ be done, but it's
completely unsupported and if I messed up my database (which I could
easily doing this kind of thing), I'd be out in the cold.  I've only
done it on databases that I created solely for the purpose of testing
the script.

The script works by updating some system catalogs in the iidbdb, as well
as in the target database itself, but, worst of all, it writes directly
to the aaaaaaaa.cnf configuration file in the ingres/data/dbname
directory.  It also requires that INGRES be shut down, because of
writing to the aaaaaaaa.cnf file.  Every single connection to the
database writes to this file because it keeps a connection count up to
date so it can tell when the database is inconsistent because of some
crash or other.

Theoretically, Ingres should be able to write this tool themselves and
make it work with their system, but for some reason they haven't.
Maybe not enough demand.

> Isn't there some administrative command which we could
> use to change the owner of the db or do some other magic thing
> so that our upgrade procedure (adding tables, changing fields, etc)
> won't fail?

I don't think so--unloaddb is it.

> (don't have the exact error at hand, but GRANT is choking)

Probably: only the DBA can use the GRANT statement.

> Problem #2
> We now have a requirement that all of our customer's UNIX id's
> become INGRES users.  The FM says we must use accessdb to add
> the users.  Again, seems like a lot of work, especially since some
> customer sites have a LOT of user ids which might use the application.

Once again, Ingres offers no simple solution to this problem.  You can
run accessdb with the ING_SET or II_EMBED_SET environment variables set,
and see exactly what accessdb does when you create a new user.  You can
then put these updates into an application of your own devising, and
this really isn't very hard.  However, Ingres can decide to change the
system catalogs any time, so your "add a user" application could
suddenly break when you upgrade an INGRES installation.  Also, you have
to use the "+U" switch with the iidbdb to allow updating the system
catalogs, and this takes an exclusive lock that prevents anyone from
connecting to _any_ database.

The one thing Ingres _does_ offer (at least in VMS) is a function that
let's you change the user name.  This way, you can have lots of users
run a given program, which then changes their user name to an unchanging
name that _is_ known to INGRES.  This fools INGRES at connect time into
thinking the user name is known, even though the _real_ one is not.  You
can find the source code in $II_SYSTEM/ingres/sig/setuser.  You have to
write a shell around this function, and then give the program privileges
(setuid in Unix?).

What they really need (anyone at Ingres listening?) is a command-line
interface for accessdb.  It would let you add or change users, and grant
them access to databases.  It could be complicated, since you wouldn't
use it every day, and you'd probably only use it in scripts for
automatic user creation.  I wouldn't mind seeing the same thing for
froont-end database objects too, something that would let you delete
forms, reports, and abf applications with a single OS command.

> We tried editing $II_SYSTEM/ingres/files/users and restarting INGRES
> but that didn't seem to work (why? it must read that file at some
> point, maybe I have to get rid of iidbdb first? sounds drastic)

At connect time, the server authenticates a user by checking for an
entry in iiuser (and sometimes iidbaccess) in the iidbdb.  The
$II_SYSTEM/ingres/files/users file gets read only (1) when you do a
build (to tell if this is a new installation or an upgrade), and (2) the
first time you create the iidbdb.  It gets written every time you add or
change a user in accessdb, and serves as a backup to the iidbdb that way
(I guess).

> and the idea of modifying system catalogs leaves me a bit cold.

It's the only way.  That's exactly how accessdb works.

> The only ideas we have for getting around the grunt labor of adding
> these ids are fairly gruesome so I think we need some new ideas
>
> Sorry if these are stupid questions...I'd appreciate any thoughts.

Not at all.  We've spent long hours worrying about how to work around
these limitations.

--Mark Jaeger
University of Chicago   phone: (312) 702-0328
Graduate School of Business    fax: (312) 702-0233
1101 East 58th Street, W309  email: cs_mj@gsbvax.uchicago.edu
Chicago, IL  60637-1511, USA         (internet)
Ingres Q & A
To William's Home Page
Email William