[Info-ingres] Installation Password vs DBMS Authentication

[Steve]

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