Changing Database Ownership

[Rich Ford]

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

[Roy Hann]

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

First off, this is an example of why I suggest creating special user IDs
for the sole purpose of "owning" databases. It is imprudent and
unnecessary to have real users as database owners. But that ship sailed
long ago...

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. (This is sort of like doing
su or sudo and only a user with Ingres security_administrator
privileges can do it.)

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. >:-)

Roy

[Karl Schendel]

> On Jan 6, 2022, at 1:12 PM, Roy Hann <spec...@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