[Info-ingres] Changing Database Ownership

[Karl Schendel]

> On Jan 6, 2022, at 1:12 PM, Roy Hann <specially at processed.almost.meat> wrote:
> 
> Rich Ford wrote:
> 
>> I need to change some databases ownership for DBAs who have left the company.
>> 
>> Part of the instructions state to "login as the CURRENT DBA of 
> 
> ...
> Changing the name of the owner of the database now is probably
> infeasible. The only supported way I can think of to do it would be to
> unload, destroy, recreate, and reload the database. 
> 
> This is not ideal, but starting from where you are, I'd probably just
> first make sure no one can ever log in using the current owners'
> ID--delete their password or whatever it takes. Then to access the
> databases from now on I would either use the -u flag to impersonate
> the owner when I connect, or, once connected, use the SET SESSION
> AUTHORIZATION command to impersonate them. 


In addition to what Roy said, if all you really need is a way to be able to
do database admin-type things, you can log in as a security user (eg
user ingres), connect to iidbdb with -uoriginal_dba_user, and

GRANT DB_ADMIN ON DATABASE foo TO new_dba_user;

and now new_dba_user can do everything that a security user
can do, for that specific database.  The inverse is:

REVOKE DB_ADMIN ON DATABASE foo FROM user


> 
> Of course if you'd like some encouragement to get into unsupported
> hackery, I or someone else here can probably whisper evil advice
> if you insist. It's not my database.   >:-)
> 

I used to have a little C program that made the necessary update to the
binary db config file as well as the iidbdb changes needed to change the
owner of a database.  It would have to be reworked to match the specific
version of Ingres involved, and it would take a lot of motivation for me
to even look at the old version I have.

Karl