• Installation Password vs DBMS Authentication

    From Steve@21:1/5 to All on Sun Aug 29 00:53:11 2021
    Hi folks

    Is using an Installation Password considered less secure, versus say DBMS authentication, when connecting to a remote Ingres installation?

    To give some context, I am thinking in terms of a cloud environment where Ingres installations can be spun up and down willy nilly. By spun up, I mean where Ingres is installed and started on a new server instance with the press of a button (well, that’
    s the theory).

    I thought DBMS authentication maybe more secure for connecting to a remote instance - presumably each user is prompted for their password when connecting. However, this would require the newly created Ingres installation to have the user information in
    order to authenticate the user.

    Also, this ignores the fact that the cloud provider will have their own layer of security.

    Any thoughts (apart from don’t over think it)?

    Thanks
    Steve

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Paul White@21:1/5 to Steve on Mon Aug 30 09:16:38 2021
    To: info-ingres@lists.planetingres.org

    This is a multi-part message in MIME format.
    Hi Steve,

    I think the installation password is designed to be used in a protected
    network environment where you are in control of the enduser names.  The authentication matches the client OS user with a DBMS user and optional password.

    Most of my sites use Server and Database users with hard coded vnodes
    and DSNs.   At one site, we have a development effort to migrate towards
    a combination of installation password, app/role passwords and some user passwords.   We have been experimenting with 2FA and temporary passwords
    to act like a token.  It seems reasonably secure.

    * OpenROAD challenges AppUser + password,
    * Sends a message to the security service to allow a match on Device,
    Active Directory User/Group, Application, AppUser, password.
    * If matched ok, the service:
    * - refreshes the the database user: expiry date and temporary password.
    * - sends an SMS with 4-6 digit pin to nominated mobile number.
    * - responds to OpenROAD with a one time token
    * The user enters the pin which combines with the token to be used as
    the database password
    * OpenROAD connects to the database.
    * Application logic uses role/password to allow access to various tables
    * 2FA function wraps some secure functions like financial authorisations

    This is all internally developed with a little bit of C for the security service and client end DLL.  We might dabble with Okta integration which
    is already in use at the site.   I am also considering an architecture written in OpenROAD entirely and using DB events to sent the
    authorisation messages.

    Paul



    On 29/08/2021 5:53 pm, Steve wrote:
    Hi folks

    Is using an Installation Password considered less secure, versus say DBMS authentication, when connecting to a remote Ingres installation?

    To give some context, I am thinking in terms of a cloud environment where Ingres installations can be spun up and down willy nilly. By spun up, I mean where Ingres is installed and started on a new server instance with the press of a button (well, that
    s the theory).

    I thought DBMS authentication maybe more secure for connecting to a remote instance - presumably each user is prompted for their password when connecting. However, this would require the newly created Ingres installation to have the user information in
    order to authenticate the user.

    Also, this ignores the fact that the cloud provider will have their own layer of security.

    Any thoughts (apart from don’t over think it)?

    Thanks
    Steve
    _______________________________________________
    Info-ingres mailing list
    Info-ingres@lists.planetingres.org https://lists.planetingres.org/mailman/listinfo/info-ingres
    --
    Paul White
    Shift Seven Solutions
    *m: 0414681799*
    p: 0754482137
    e: paul.white@shift7solutions.com.au
    w: https://www.shift7solutions.com.au
    International: +61414681799
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    </head>
    <body>
    <p>Hi Steve,</p>
    <p>I think the installation password is designed to be used in a
    protected network environment where you are in control of the
    enduser names.  The authentication matches the client OS user with
    a DBMS user and optional password.<br>
    </p>
    <p>Most of my sites use Server and Database users with hard coded
    vnodes and DSNs.   At one site, we have a development effort to
    migrate towards a combination of installation password, app/role
    passwords and some user passwords.   We have been experimenting
    with 2FA and temporary passwords to act like a token.  It seems
    reasonably secure.<br>
    </p>
    <ul>
    <li>OpenROAD challenges AppUser + password, <br>
    </li>
    <li>Sends a message to the security service to allow a match on
    Device, Active Directory User/Group, Application, AppUser,
    password.</li>
    <li>If matched ok, the service:</li>
    <li>- refreshes the the database user: expiry date and temporary
    password.<br>
    </li>
    <li>- sends an SMS with 4-6 digit pin to nominated mobile number.
    <br>
    </li>
    <li>- responds to OpenROAD with a one time token<br>
    </li>
    <li>The user enters the pin which combines with the token to be
    used as the database password</li>
    <li>OpenROAD connects to the database.</li>
    <li>Application logic uses role/password to allow access to
    various tables</li>
    <li>2FA function wraps some secure functions like financial
    authorisations</li>
    </ul>
    <p>This is all internally developed with a little bit of C for the
    security service and client end DLL.  We might dabble with Okta
    integration which is already in use at the site.   I am also
    considering an architecture written in OpenROAD entirely and using
    DB events to sent the authorisation messages.<br>
    </p>
    <p>Paul<br>
    </p>
    <p><br>
    </p>
    <p><br>
    </p>
    <div class="moz-cite-prefix">On 29/08/2021 5:53 pm, Steve wrote:<br>
    </div>
    <blockquote type="cite"
    cite="mid:9e7e851b-ac1e-4c16-b07c-fe29debb0e97n@googlegroups.com">
    <pre class="moz-quote-pre" wrap="">Hi folks

    Is using an Installation Password considered less secure, versus say DBMS authentication, when connecting to a remote Ingres installation?

    To give some context, I am thinking in terms of a cloud environment where Ingres installations can be spun up and down willy nilly. By spun up, I mean where Ingres is installed and started on a new server instance with the press of a button (well, that’
    s the theory).

    I thought DBMS authentication maybe more secure for connecting to a remote instance - presumably each user is prompted for their password when connecting. However, this would require the newly created Ingres installation to have the user information in
    order to authenticate the user.

    Also, this ignores the fact that the cloud provider will have their own layer of security.

    Any thoughts (apart from don’t over think it)?

    Thanks
    Steve
    _______________________________________________
    Info-ingres mailing list
    <a class="moz-txt-link-abbreviated" href="mailto:Info-ingres@lists.planetingres.org">Info-ingres@lists.planetingres.org</a>
    <a class="moz-txt-link-freetext" href="https://lists.planetingres.org/mailman/listinfo/info-ingres">https://lists.planetingres.org/mailman/listinfo/info-ingres</a>
    </pre>
    </blockquote>
    <div class="moz-signature">-- <br>
    Paul White<br>
    Shift Seven Solutions<br>
    <b>m: 0414681799</b><br>
    p: 0754482137<br>
    e: <a class="moz-txt-link-abbreviated" href="mailto:paul.white@shift7solutions.com.au">paul.white@shift7solutions.com.au</a><br>
    w: <a class="moz-txt-link-freetext" href="https://www.shift7solutions.com.au">https://www.shift7solutions.com.au</a><br>
    International: +61414681799<br>
    </div>
    </body>
    </html>

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Steve@21:1/5 to Paul White on Wed Sep 8 19:12:53 2021
    On Monday, August 30, 2021 at 9:18:09 AM UTC+10, Paul White wrote:
    Hi Steve,
    I think the installation password is designed to be used in a protected network environment where you are in control of the enduser names. The authentication matches the client OS user with a DBMS user and optional password.
    Most of my sites use Server and Database users with hard coded vnodes and DSNs. At one site, we have a development effort to migrate towards a combination of installation password, app/role passwords and some user passwords. We have been experimenting
    with 2FA and temporary passwords to act like a token. It seems reasonably secure.
    OpenROAD challenges AppUser + password,
    Sends a message to the security service to allow a match on Device, Active Directory User/Group, Application, AppUser, password.
    If matched ok, the service:
    - refreshes the the database user: expiry date and temporary password.
    - sends an SMS with 4-6 digit pin to nominated mobile number.
    - responds to OpenROAD with a one time token
    The user enters the pin which combines with the token to be used as the database password
    OpenROAD connects to the database.
    Application logic uses role/password to allow access to various tables
    2FA function wraps some secure functions like financial authorisations
    This is all internally developed with a little bit of C for the security service and client end DLL. We might dabble with Okta integration which is already in use at the site. I am also considering an architecture written in OpenROAD entirely and using
    DB events to sent the authorisation messages.
    Paul

    Sounds impressive Paul.

    I turned on DBMS authentication in our development environment and the existing vnodes stopped working.

    Am I right in thinking that upon turning on DBMS authentication any existing vnodes will stop working, if the users specified in those vnodes don’t have a DBMS password, as Ingres will authenticate the users, rather than the OS? Makes sense to me,
    since DBMS authentication was turned on.

    Steve

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Karl Schendel@21:1/5 to Steve on Wed Sep 8 22:38:16 2021
    On Sep 8, 2021, at 10:12 PM, Steve <s.anderson.au@gmail.com> wrote:


    I turned on DBMS authentication in our development environment and the existing vnodes stopped working.

    Right, because with DBMS auth ON, the DBMS is the sole authenticator of Ingres users. The vnode
    username and password is passed to the DBMS, so if you don't have the right username and
    password defined in the iidbdb user table to match what's in the vnode, it will fail authentication.

    Karl

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