1. Forum
  2. USENET
  3. comp.databases.ingres

  • Changing Database Ownership

    From Rich Ford@richford990@gmail.com to comp.databases.ingres on Thu Jan 6 08:31:55 2022
    From Newsgroup: comp.databases.ingres

    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
    --- Synchronet 3.21b-Linux NewsLink 1.2
  • From Roy Hann@specially@processed.almost.meat to comp.databases.ingres on Thu Jan 6 18:12:27 2022
    From Newsgroup: comp.databases.ingres

    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
    --- Synchronet 3.21b-Linux NewsLink 1.2
  • From Karl Schendel@schendel@kbcomputer.com to Ingres and related product discussion forum on Thu Jan 6 13:41:36 2022
    From Newsgroup: comp.databases.ingres


    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
    --- Synchronet 3.21b-Linux NewsLink 1.2