Connect to the application database with SSO

Overview

The goal of a complete SSO solution is to let the end-user enter credentials once in a login form on the front-end, authenticate that user with an GAS/SSO mechanism, then start the application program and connect to the database without having the end user input other credentials for the database server.

Depending on the features of the target database server, you can implement different techniques to connect to the database automatically, without having to provide more credentials.

The goal of this topic is not to provide a complete example. There are different authentication methods available, and your SSO solution must be adapted to the type of database and operating system. Consider learning about database user security within the database engine of your application.

User handling depends on the type of web application

Connecting to the database from the application program

Once the end user is authenticated with one of the SSO mechanisms supported by the GAS, the application is started in the context of the GAS operating system user. For example, when on an Apache server, the application program will execute as the Apache user.

The most common way to authenticate a user in a database connection is to provide the login name and the associated password of the user object existing in the database:

MAIN
   DEFINE uname, upswd VARCHAR(50)
   ...
   CONNECT TO "dbsource" USER uname USING upswd
   ...
END MAIN

For more details about the CONNECT TO instruction, see Database Connections chapter in the Genero Business Development Language User Guide.

Database user creation

In order to use database engine features to control privileges and audit activity, end users must be identified in the database server, as db user objects in the database system. This is typically done with the CREATE USER SQL instruction.

When creating a database user object, you must specify the authentication method.

The basic default authentication method is to specify that a password is provided each time the SQL session is created. For example, to create a database user with password authentication in an Oracle® database:

CREATE USER username IDENTIFIED BY password

It is also possible to define database users with an authentication method based on credentials issued from a trusted part. For example, in Oracle, you can create a database user that will be authenticated with the Oracle Internet Directory:

CREATE USER username IDENTIFIED GLOBALLY AS directory_DN

CREATE USER username IDENTIFIED EXTERNALLY AS ...

ALTER USER application-user GRANT CONNECT THROUGH connection-user

For more details, read database vendor's documentation and the Database Users topics in the SQL adaptation guides of the Genero Business Development Language User Guide.

Connecting to the DB with predefined database users

This technique can be implemented for a web application where the number of end users is unknown, and where users can register themselves to the application without requiring the database administrators to create database users. End users enter an application login and password, that is checked and stored by the application in a dedicated table of the database.

In this solution, an SSO technology is used where end users can create credentials from an open identity provider (IdP), as, for example, with the OpenID Connect SSO implementation. Since anyone can freely register with the web application, there is no application administrator task required to create an application user.

To access application data, a set of predefined database users with a fixed name and password must exist in the database. The database users are hidden from the application or end users. Each predefined database user is assigned to several real physical end users. For example, you can create four types of database users, each with specific application permissions and database privileges:

• An application administrator can manage application users, can read/write all application data.
• A read/write access user can read/write all data of the application.
• A read access user can only read all application data.
• A guest user can only read a limited set of application data.

Once the web application is allowed to start by the GAS SSO mechanism, it needs to connect to the database, and therefore the program must get application user information (login name, password, and type of user), from the GAS SSO procedure.

LET user_name = fgl_getenv("OIDC_user_name")
LET user_pswd = fgl_getenv("OIDC_user_pswd")
LET user_type = fgl_getenv("OIDC_user_type")

The user type identifies the predefined database user that is used to connect to the database, and in turn determines the privileges allowed for the end user.

CALL get_db_login("config_file", user_type) RETURNING db_user, db_pswd
CONNECT TO dbname USER db_user USING db_pswd

LET user_pswd_encrypted = my_encode(user_pswd)
SELECT last_login INTO ts FROM app_users
   WHERE app_users.u_name = user_name
     AND app_users.u_pswd = user_pswd_encrypted
IF SQLCA.SQLCODE == 100 THEN
   SELECT last_login INTO ts FROM app_users
      WHERE app_users.u_name = user_name
   IF SQLCA.SQLCODE == 100 THEN
      -- Application user is not registered
      ...
   ELSE
      -- Password is invalid
      ...
   END IF
END IF

Connecting to the DB with custom SSO implementation

This technique can be implemented for a public web application (where end users can register themselves), or for an enterprise web application (where end users are known and where creation is controlled).

The principle is similar to the Connecting to the DB with predefined database users solution, but instead of using a standard SSO protocol, the GAS SSO delegation feature is used to implement a custom single sign-on procedure.

Application users (SSO login and password) are handled by the delegation program, and associated database user credentials can be stored in a file or light-weight database, which can then be passed through environment variables to the application program. The application program then issues a regular CONNECT TO instruction with USER db_username USING db_password option.