1. Forum
  2. Usenet
  3. COMP.DATABASES.INGRES

  • Changing Database Ownership

    From Rich Ford@21:1/5 to All on Thu Jan 6 08:31:55 2022
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Roy Hann@21:1/5 to Rich Ford on Thu Jan 6 18:12:27 2022
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Karl Schendel@21:1/5 to Ingres and related product discussi on Thu Jan 6 13:41:36 2022
    On Jan 6, 2022, at 1:12 PM, Roy Hann <specially@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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)