Connect to the application database with SSO

There are several solutions for automatically connecting to the database server, after starting an application program with a Single sign-on (SSO) delegation.

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.

Note: Because Kerberos SSO support is deprecated by the Genero Application Server, this type of SSO mechanism is not covered in this topic. However, database engines like IBM^® Informix^® IDS support Kerberos SSO with the Generic Security Services CSM (GSSCM) feature.

User handling depends on the type of Web Application

Regarding application users, we can distinguish the following type of web applications:

Typical public web applications, for an undefined number of end users, who can register themselves to the application.
Typical enterprise web applications, for a defined number of known end users, with strong data access control, managed by application administrators in an enterprise directory.

The SSO solution implemented will depend 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.

Most application programs then connect to a database server, to store and query application data. Connecting to a database server requires the application to identify and authenticate the end user as a database user.

Note: A database user is typically created by a CREATE USER SQL statement.

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 Reference Guide.

Note: With IBM Informix IDS servers, database users are traditionally authenticated at the OS level; there must be an OS login from group "informix", created for each DB user. However, IDS 11.7 introduces the concept of internal users, to integrate with external authentication mechanisms or to define pure database users based on logins and passwords.

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 a password be 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 distinguished name (LDAP DN)

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 with requiring the database admministrators to create database users. End users enter an application login and password, that will be checked and stored by the application in a dedicated table of the database.

In this solution, use an SSO technology where end users can create credentials from an open identity provider (IdP), as with the GAS OpenID implementation. Since anyone can freely register with the web application, there is no application administrator task regarding the creation of an application user.

To access the application data, a set of predefined database users must be exist in the database, with a fixed name and password, that are hidden to the end users. Each predefined database user will be 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.

Application users are managed and controlled at the application level, and stored in a database table, or in an external resource file (with passwords encrypted).

Important: Because application programs will implicitly connect to the database with predefined database users, no security holes can exist in programs that would allow an end user to connect as a database user to attack the database, for example by using SQL injection.

Once the web application is allowed by the GAS SSO mechanism to start and needs to connect to the database, the program must get application user information (login name, password and type of user), from the GAS SSO procedure. For example, when using OpenID, the web application must get the end user login, password and user type from OpenID attributes through the corresponding environment variables:

LET user_name = FGL_GETENV("OPENID_user_name")
LET user_pswd = FGL_GETENV("OPENID_user_pswd")
LET user_type = FGL_GETENV("OPENID_user_type")

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

In order to connect to the database server, the predefined database credentials must be found according to the user type got from the SSO attributes. For example, the program can get the database user name and password from a encrypted configuration file:

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

Once connected to the database, the application program can issue SQL queries as the predefined database user.

Note: Because physical/end users are mapped to predefined/anonymous database users, db server auditing services will not be able to trace end user activity. If needed, this feature must be implemented at the application level.

At this level, the application user must be validated with simple SQL queries. The application user definition can be stored in an application table, where the password should be encrypted:

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
   -- application user does not exist: ask for registration, or deny access
   ...
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 an 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.