Authentication Methods
offers two authentication methods to access the database: Password based authentication and Single Sign On (SSO) based authentication. You can use these independently or as an organization according to your security and access control needs. This section explains each authentication method and how it can be configured on Ocient.
Users created via DCL are required to set a password that will be used for authentication when connecting to the database.
To connect to the database, the user must provide a FQUN. For database users, the FQUN has the form:
For example, the FQUN of a user "alice", a member of the database "example_database", would be:
The FQUN should be set in the connection string along with the user’s password. For example, the command "alice" would use to connect to "example_database" via the Ocient JDBC driver would be:
The following Ocient driver properties MUST be set when connecting via Password authentication:
- user: alice@example_database
- password: ****
The Ocient System allows administrators to add a Single Sign On integration, allowing users to authenticate via an external Identity Provider (idP). A database, including the "system" database, can have 0 or 1 SSO integrations.
The existence of an SSO integration has no effect on users that authenticate with the Password Authentication flow.
Supported Protocols:
The Ocient System supports the following SSO protocols:
Protocol | Description |
|---|---|
OpenID Connect (OIDC) is a simple JSON-/REST-based identity protocol built on top of the OAuth 2.0 protocol. It enables client applications to rely on authentication performed by an OpenID Connect Provider to verify the identity of a user. Clients can also obtain basic profile information about a user in an interoperable and REST-like manner from OpenID Connect Providers. |
There are two flows which allow users to connect to the database via an external OpenID Connect Provider.
This is the most commonly used authentication method and makes use of the OAuth 2.0 Authorization Code Grant.
When connecting to the database, users are directed (via Ocient driver) to the OpenID Provider’s authorization endpoint where they are able to login with their SSO credentials. After the user has been authenticated, the Provider redirects the user back to the database with a temporary code, which the database exchanges for an ID token, Access token, and optionally a Refresh token.
See this image for a sequence diagram of the flow.
Connect using the Authorization Code Flow
The client handshake method MUST be set to SSO, and the connection username and password set to the empty string.
For example, the command any user would use to "example_database" via the Ocient JDBC driver would be:
The following Ocient driver properties MUST be set when connecting using the Authorization Code flow:
- handshake: SSO
- user: <empty_string>
- password: <empty_string>
The user and password properties are REQUIRED and must be set to the empty string.
Users can provide either an ID Token or an Access Token issued by the OpenID Provider to connect to the database.
The provided ID or Access token MUST contain the following claims:
- iss: The issuer MUST match the issuer contained in the OpenID Provider’s discovery document
- aud: The token audience MUST contain the client_id used when configuring the OpenID Provider in the database
Ocient recommends including the "email" scope in any token provided to Ocient. The value of the email claim will be used to identify the user in the database audit trails.
Ocient supports the following signing algorithms for ID Tokens:
- RSA 256
- RSA 384
- RSA 512.
Connect using the SSO Token Flow
The following Ocient driver properties MUST be set when connecting using an OpenID token:
- handshake: SSO
- user: id_token OR access_token
- password: <token_data>
The handshake MUST be set to SSO, with the username set to the token type, and the password set to the token payload.
For example, the JDBC command to connect to "example_database" using an ID Token would be:
The JDBC command to connect to "example_database" using an Access Token would be:
Before users can authenticate via SSO, the database will need to be registered with the OpenID Provider. This process varies depending on the provider, but typically, the steps for doing so are:
- Create an application for the Ocient Database in the Provider. Select the "Native Application" application type.
- Enable the Authorization Code, Refresh Token, and Device Authorization grant types.
- Grant permission to the appropriate users to use the newly created application.
- Enter the following Redirect URIs:
Record the token issuer and client_id of the newly created application. You need these values to configure the Ocient Database.
These parameters are configurable by using DDL statements.
After all parameters have been retrieved, a database administrator can execute a DDL command using this syntax.
- The user MUST be a database administrator (or have the "Security Administrator" role for the "system" database).
- The user MUST be connected to the database.
- Any String key or value can be placed between double quotations (e.g., default_group OR "default_group").
- String values that contain characters other than ([a-zA-z] | [0-9] | '_') MUST be placed between double quotations (e.g., this.is.a.complex-$tring ⇒ "this.is.a.complex-$tring").
- The NULL value can be used to clear existing configuration of LIST or MAP properties (e.g., user_claim_ids = NULL).
To remove the connection, execute this command:
The OpenID Connect protocol requires TLS for communication between the end user, the database, and the OpenID Provider. A valid TLS key and certificate pair MUST be configured on the SQL nodes, which behave as a server during the Authorization Code Flow.
The database maintains an internal User Access Control model consisting of groups, roles, and service classes. The database relies on group/role membership to determine a user’s privileges or service class. The following integration properties allow administrators to grant SSO users membership to a Database group/role:
- groups_claim_ids and roles_claim_ids define the token claim(s) that specify the user’s Provider-defined groups/roles.
- groups_claim_mappings and roles_claim_mappings defines mappings between a Provider and Database groups/roles.
- additional_scopes should include any request scopes needed for the Provider to include the group/role claims in tokens it issues.
Consider an example where the Provider lists all groups the user belongs to in the token claim "user_groups" when the application requests the "example_scope" scope. In this scenario, the administrator wants to associate the Provider Group "Example Provider Group" with the Database Role "example_database Analyst".
By default, the database grants all users membership to the "Public Role" of the database for the current connection. Unlike other default roles, you can grant additional privileges to the "Public Role".
Additionally, database administrators can revoke access to users with membership to ANY of the specified Provider groups or roles. To revoke access, execute the following statement:
The user MUST have the ALTER privilege on all Ocient defined groups or roles specified by groups_claim_mappings, roles_claim_mappings, blocked_groups, and blocked_roles.
By default, audit trails, including but not limited to system level log messages, identify users connecting using Single Sign-On (SSO) by the email claim of the ID Token. If the email claim is not present in the token, the identifier is iss and sub ID Token claims separated by ::, for example: "https://ocient.okta.com::00u5rslndgXm9Ey7y5d7".
To change the Fully Qualified User Name (FQUN), alter the user_claim_ids SSO integration property.
For example, to change the FQUN to a combination of the first_name and last_name claim values, execute the following command:
After executing this command, users are identified with the <first_name>::<last_name>::<email> format. (For example: John::Smith::[email protected])
Updating the user_claim_ids property has NO EFFECT on users that are already connected to the database.
Properties that do not contain sensitive data can be viewed using the sys.oidc_integrations virtual table. In addition to the configurable properties, a database_id column is included in the schema.
To view the configuration for a specific database (if one exists), execute the following command:
The user MUST be a database administrator to view the OpenID Connect properties.