next »

[Gary C.]

Hello

For us, we deliver our core system through GDC. The user is presented with a traditional username/password form that is used to authenticate them against a database table.

I am interested to hear how others have approached user authentication in their applications, specifically, I am wondering if a form of SSO can be implemented given that the user will have already authenticated themselves against Active Directory.

Thanks

[Sebastien F.]

Hello,

What database engine(s) do you use?

Each DB vendor supports its own DB user authentication solutions, you should first look at what they provide,
Then see how to interface with what Genero offers.

Seb

[Gary C.]

Hi
We use MySQL and I should have explained that the connection to the DB uses a generic system user, not a real user.

When the menu app is launched a DB connection is made using the system user, the user then authenticates themselves against a table held in the database.

Gary

[Sebastien F.]

Gary,

Your describe a typical technique where a single DB user is defined to connect to the DB server, and then application users as managed "by hand" in your own DB table.
This solution has the advantage of being simple and portable, but prevents to use DB features based on DB user definitions.
I assume that you want to define a DB user for each real end user, in order to use DB server features like SQL privileges, auditing...
This is a good thing, but keep in mind that each DB engine has its own specifics when is comes to user management.
So if you plan to support several other DB engines with your application, you better keep your current solution.
You will also have more DB administration to do, for example, give privileges to DB users to access the tables created by the owner of the tables, select the DB schema after connection.

From a Genero process point of view, fglrun acts as a C program using the MySQL C client library.
The MySQL ODI drivers uses the following C API functions to connect to the server:
- mysql_options(cn->dbcHandle, MYSQL_READ_DEFAULT_FILE, <filename>) -- If FGLPROFILE entry dbi.database.dbname.mys.config is defined
- mysql_options(cn->dbcHandle, MYSQL_READ_DEFAULT_GROUP, "client") -- to read options from the [client] section of your config file
- mysql_real_connect( ... ) -- using user name / password ...

I suggest that you have a look at the MySQL guide:
- https://dev.mysql.com/doc/refman/8.0/en/security.html
- https://dev.mysql.com/doc/refman/8.0/en/authentication-plugins.html

Then we should talk about the options.
The solution can be different depending on your architecture:
For example, if fglrun programs run on the same machine as the MySQL server, and it's a Linux/UNIX, maybe you could use the auth_socket authentication plugin.

Useful BDL doc links:
- https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/t_fgl_odiagmys_004.html
- https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_Connections_008.html
- https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_Connections_018.html

Seb

[Sebastien F.]

Maybe using the Windows Authentication Plugin:

https://www.mysql.com/products/enterprise/security.html
https://dev.mysql.com/doc/refman/8.0/en/windows-pluggable-authentication.html

With Proxy Users:

https://dev.mysql.com/doc/refman/8.0/en/proxy-users.html

Seb

[Gary C.]

Hello
Thanks for the replies.

I don't want to define DB users and wish to continue with the current method.

What I am trying to do is remove the username/password element and adopt a form of single sign on by somehow leveraging the user's AD credentials.

I was just wondering if anyone has done anything similar. For example, I know I can get the environment variables USERDOMAIN/USERNAME and use those, i.e. if a record for that user exits in the DB table then use that but that seems too simplistic.

[Sebastien F.]

MySQL Windows Authentication Plugin + Proxy Users should do the job, assuming you have MySQL Enterprise Edition...

Each Windows Active Directory user can be authenticated and connect without password to MySQL.
You can map these AD users to 1, 2 or 3 different "proxied users", which will be the actual DB users.
Then grant different classes of privileges / roles for each of these DB users (app admin, app user, guest)

To identify the application user and verify that it exists in your own application user table:
The Windows/AD user name is provided by the USER() SQL function.
(The DB user is returned by the CURRENT_USER() SQL function)

But you need the Enterprise Edition:

https://dev.mysql.com/doc/refman/8.0/en/windows-pluggable-authentication.html

Seb

[Reuben B.]

Seb,

I don't think this is a database question.

What I think Gary is asking is for user experience on things like ...

1. if using direct connection, The Kerberos Authentication method on page 5 of the GDC short-cut http://4js.com/online_documentation/fjs-gdc-manual-html/#gdc-topics/t_gdc_applications_shortcuts_creating_direct.html

2. If using GAS (whether GBC or GDC via http)  the use of SSO when using Genero Application Server http://4js.com/online_documentation/fjs-gas-manual-html/#gas-topics/c_gas_sso_howto.html

My initial thoughts are to look at what you have that is allowing "user will have already authenticated themselves against Active Directory" and seeing what Open ID or SAML options it has, and be prepared to move from GDC-Direct to GDC via GAS using DELEGATE in your .xcf to do the authentication using what the users have already signed in on.

Reuben

[Sebastien F.]

Reuben,

You are right, I have discussed with Gary about MySQL user authentication options, but there is no plan to create DB users for each real end user.
MySQL DB connection is achieved with a single DB user.
As discussed with Gary, that could be enhanced, but it's not the main request.

What they are looking for is a way to pass the USERNAME and USERDOMAIN (in the GDC/Windows workstation context) to the application, in order to authenticate the user with their own application user table.

Using a getEnv front call in the app code to get USERNAME/USERDOMAIN in GDC context is not an option:
The end user can easily impersonate another user by setting these env vars and then run the GDC.

With GDC direct mode shortcuts, we have this @USR special placeholder to pass the current user name in the command to be executed on the server...
But for now they are using the GAS and http connection, not sure an equivalent exists and we are missing the domain name.

I told already to Gary that we have SSO with the GAS and that this could be an option.

I will suggest Gary to contact his support center to find a solution.

Seb

[Gary C.]

Hello

Seb, thanks for your time on the call last night - much appreciated.

I think you have both pointed me in the right direction so I will do some further reading/research and reach out to suppor if needs be.

Thanks again.
Gary

[Sebastien F.]

After internal discussing, we can suggest you this solution:

https://4js.com/files/documents/support/HowTo-SSO-ADFS-Genero.pdf

Seb