Authentication Methods - Ocient Documentation

offers two authentication methods to access the database: Password authentication and Single Sign On (SSO) 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.

Password-Based Authentication

Users created using DCL are required to set a password that the system uses for authentication when connecting to the database. Administrators can use Database Password Security Settings to manage settings such as minimum password length and password expiration policies.

Fully Qualified User Name (FQUN)

To connect to the database, you must provide an FQUN. For database users, the FQUN has this form.

Text

<user_name>@<database>

For example, the FQUN of the user alice, a member of the database example_database.

Text

alice@example_database

How To Connect using Password Authentication

Set the FQUN in the connection string along with the password of the user. For example, this code is the command alice uses to connect to example_database using the Ocient JDBC driver.

Text

connect to jdbc:ocient://<host>:<port>/example_database;user=alice@example_database;password=****;

The following Ocient driver properties must be set when connecting using password authentication:

user — alice@example_database
password — “

Single Sign On (SSO)

The Ocient System allows administrators to add a Single Sign-On integration, allowing users to authenticate using 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 who authenticate with the Password Authentication flow.

Supported Protocols: The Ocient System supports the following SSO protocols:

Protocol	Description
OpenID Connect	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 the 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.

OpenID Connect

There are two flows that allow users to connect to the database using an external OpenID Connect Provider.

Authorization Code Flow

This is the most commonly used authentication method and makes use of the OAuth 2.0 Authorization Code Grant. When you connect to the database, the Ocient driver directs you to the authorization endpoint of the OpenID Provider, where you log in with your SSO credentials. After the Provider authenticates you, it redirects you 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, this code is the command any user would use to connect to the example_database database using the Ocient JDBC driver.

Text

connect to jdbc:ocient://<host>:<port>/example_database;handshake=SSO;user=;password=;

These 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.

SSO Token Flow

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 these claims:

iss — The issuer must match the issuer contained in the discovery document of the OpenID Provider.
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 system uses the value of the email claim to identify the user in the database audit trails.Ocient supports these signing algorithms for ID Tokens:

RSA 256
RSA 384
RSA 512

Connect using the SSO Token Flow These 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, this format is the JDBC command to connect to the example_database database using an ID Token.

Text

connect to jdbc:ocient://<host>:<port>/example_database;handshake=SSO;user=id_token;password=<id_token_data>;

This format is the JDBC command to connect to example_database database using an Access Token.

Text

connect to jdbc:ocient://<host>:<port>/example_database;handshake=SSO;user=access_token;password=<access_token_data>;

Configure the OpenID Provider

Before users can authenticate using SSO, you must register the database 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 System 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 this Redirect URI.

Application	Redirect URI
JDBC Driver	`http://localhost:7050/ocient/oauth2/v1/callback`
OpenAPI or UI	`https://<advertised_ip>/v1/callback`

For the OpenAPI or UI application, the redirect URI must use https format.The connectivity pool defines the OpenAPI or WebUI advertized_ip setting. For more information, see CONNECTIVITY POOL.

Record the token issuer and client_id of the newly created application. You need these values to configure the Ocient System.

SSO Parameters

These SSO parameters are configurable by using DDL statements.

Name	Required	Value Type	Default Value	Description
`issuer`	Yes	string	None	The complete URL for the OAuth 2.0 and OpenID Connect Authorization Server. This property value is the expected `\"iss\"` claim in access tokens validated by the database.
`client_id`	Yes	string	None	The client identifier as registered with the OpenID Provider.
`client_secret`	Yes	string	None	The client secret as registered with the OpenID Provider. This property is required for some SSO workflows.
`default_group`	Yes	string	None	The group users are assigned if no group is specified in the `group_claim_mappings` property for the OpenID Provider.
`redirect_host`	No	string	Depends on the connector (pyocient or JDBC).	Specifies the host name during SSO redirection.
`redirect_ssl`	No	Boolean	Depends on the connector (pyocient or JDBC).	Set to `true` to enable SSL callback during SSO redirection (i.e., redirect uses `https`). Set to `false` to disable (i.e., redirect uses `http`).
`disabled`	No	Boolean	`false`	Set to `true` to disable the OIDC integration for maintenance temporarily. ⚠️ If you set this property to `true`, all authentication requests using this connection fail.
`enable_id_token_authentication`	No	Boolean	`false`	Set this property to `true` if the identifier token also contains the authorization token. In most circumstances, this option is necessary only for machine-to-machine connections without user interaction, such as a server using a script to connect to an Ocient System.
`user_id_claims`	No	list of strings (e.g., value [, …]	[“email”]	Set the identifier token claims used to identify users in audit trails. If you do not set a value, the system uses the `"email"` claim if it is present, otherwise it uses `["iss", "sub"]`.
`additional_scopes`	No	list of strings (e.g., value [, …]	[]	Specifies additional scopes to request when executing the Authorization Code Flow.
`additional_audiences`	No	list of strings (e.g., value [, …]	[]	Specifies additional audiences to accept when validating tokens. This property is useful for authorization servers without the token exchange capability. Each additional audience is a case-sensitive URL from a provider, similar to the `issuer` value.
`groups_claim_ids`	No	list of strings (e.g., value [, …]	[]	Specifies the token claims that can be used to map the user to a Database group. If you specify the `groups_claim_mappings` property, you must also specify the `groups_claim_ids` property.
`groups_claim_mappings`	No	map of strings (e.g., key = value [, …])	Specifies mappings from the Provider group to the Database group.
`roles_claim_ids`	No	list of strings (e.g., value [, …]	[]	Specifies the token claims that can be used to map the user to a Database role. If you specify the `roles_claim_mappings` property, you must also specify the `roles_claim_ids` property.
`roles_claim_mappings`	No	map of strings (e.g., key = value [, …])	Specifies mappings from the Provider role to the Database role.
`allowed_groups`	No	list of strings (e.g., value [, …]	[]	Specifies a list of external identity provider groups that are permitted to authenticate through this SSO integration. If you specify the `allowed_groups` property, only users who are members of the specified identify provider groups can access the Ocient System. If this property is empty or you do not specify it, then group-based filtering is disabled, and the system allows all authenticated users from the provider unless they are explicitly blocked. Group names must match exactly as they appear in the claims of the identify provider group.
`allowed_roles`	No	list of strings (e.g., value [, …]	[]	Defines a list of external identity provider roles that are authorized to access the Ocient System through this SSO integration. Only users assigned to the specified roles in the external provider can authenticate. If this property is empty or you do not specify it, role-based access control is disabled for this integration. Role names must correspond exactly to the role claims provided by the identity provider in the authentication response.
`blocked_groups`	No	list of strings (e.g., value [, …]	[]	Specifies a list of external identity provider groups that are explicitly denied access through this SSO integration. The system blocks users who are members of any specified group in the external identity provider from authenticating, regardless of other permissions they have. Setting this property takes precedence over the `allowed_groups` property if a user belongs to both an allowed group and a blocked group. Group names must match exactly as they appear in the claims of the identify provider group.
`blocked_roles`	No	list of strings (e.g., value [, …]	[]	Defines a list of external identity provider roles that are explicitly prohibited from accessing the Ocient System through this SSO integration. The system denies access to users assigned to any of the specified roles, overriding any other access permissions. Setting this property takes precedence over the `allowed_roles` property in cases where a user is in both an allowed role and a blocked role. Role names must correspond exactly to the role claims of the identity provider group.
`allow_offline_access`	No	Boolean	`false`	When the `allow_offline_access` property is `true`, Ocient requests offline access from the OpenID Connect identity provider by including `access_type=offline` and `prompt=consent` in the authorization URL.

After all parameters have been retrieved, a database administrator can execute a DDL SQL statement using this syntax.

SQL

ALTER DATABASE database
    { SET | ALTER } SSO INTEGRATION sso_protocol <sso_property> [, ...]

<sso_property> ::=
    -- literal or string
    property_name = value |
    -- list
    property_name = [ value [, ...] ] |
    -- map
    property_name = { key = value [, ...] }   # map

SQL

ALTER DATABASE example_database
    SET SSO INTEGRATION oidc
        issuer = "https://example.com",
        client_id = "xxxxxx",
        default_group = "default",
        additional_scopes = [email, groups, profile, offline_access],
        user_id_claims = [claim1, claim2],
        groups_claim_ids = [claim3],
        groups_claim_mappings = { idp_group1 = ocient_group1, idp_group2 = ocient_group2 };

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 configurations of LIST or MAP properties (e.g., user_claim_ids = NULL).

To remove the connection, execute this SQL statement.

SQL

ALTER DATABASE example_database REMOVE SSO INTEGRATION;

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.

Set Up Machine-To-Machine SSO Integration

You can use DDL SQL statements to add client credentials for SSO connection to individual databases or to a connectivity pool that operates across SQL Nodes. These steps set up SSO client credentials on a database, but can also apply to connectivity pools.

Create an SSO protocol.Use the CREATE SSO INTEGRATION SQL statement to make an SSO integration protocol with your preferred configuration. This configuration must include these properties:

The enable_id_token_authentication SSO property must be set to true.
The additional_audiences SSO property must use the URL from the OIDC provider, similar to the issuer value.

SQL

CREATE SSO INTEGRATION sso_test PROTOCOL oidc
    issuer = "https://accounts.google.com",
    client_id = example_database_app_id,
    default_group = example_database_group,
    enable_id_token_authentication = true,
    additional_audiences = ["https://accounts.google.com"];

Assign the SSO protocol.Use the ALTER DATABASE SET SSO INTEGRATION SQL statement to integrate the SSO protocol into a database. For connectivity pools, use the ALTER CONNECTIVITY_POOL SET SSO INTEGRATION SQL statement.

SQL

ALTER DATABASE example_database
    SET SSO INTEGRATION sso_test;

Connect to your system.Use a JDBC connection string that includes the access token value <access_token>. This string connects to the local host at port number 4050, uses the SSO protocol, and with the username id_token.

Shell

CONNECT TO jdbc:ocient://localhost:4050/system;handshake=SSO;user=id_token;password=<access_token>

Set Up Cross-Database SSO Integration

Set up authentication using SSO integration across databases. Identify the database that has SSO integration using the username@database format, where username is the access or identifier token and database is the database for the SSO integration. Each SSO integration has a client identifier client_id. You must obtain the access token using the same client identifier used in the SSO integration for authentication. When you authenticate using SSO integration as a user, the system assigns you to a group defined in the integration. Such users always have groups qualified with the SSO integration of the database. These groups have fully qualified names such as sso_users@system, where sso_users is the name of the group and system is the name of the database. You can use a fully qualified group name from any database. Without the @database qualification, the referenced group specifies the current database. To grant cross-database privileges to such groups, you must reference them using their fully qualified names. You can find a list of the groups the current user is a member of by using the CURRENT_GROUPS function. To manage privileges and groups, use the GRANT, REVOKE, ALTER GROUP, and DROP GROUP SQL statements. For details about privileges, see Data Control Language (DCL) Statement Reference. Requirements

Configure the SSO integration in the source database.
The SSO user must be a member of a group that has the USE privilege on the target database. If the SSO user is not a member of any groups, the default group for the SSO integration must have the USE privilege on the target database.
The token (either an access or an identifier token) must be valid for the SSO integration in the source database.

Create a Cross-Database SSO Integration These steps guide you through setting up a cross-database SSO integration.

Create a default group sso_users.

SQL

CREATE GROUP sso_users;

Create the SSO integration test_sso in the source database using the CREATE SSO INTEGRATION SQL statement. Specify your issuer and client identifier for the issuer and client_id parameters.

SQL

CREATE SSO INTEGRATION test_sso PROTOCOL OIDC
    enable_id_token_authentication = true,
    issuer = 'https://your-idp.com/oidc',
    client_id = 'your_client_id',
    default_group = sso_users,
    additional_scopes = [profile, email, groups, offline_access],
    protocol_version = 'v1',
    groups_claim_ids = [groups];

Set the SSO integration as the default for the database source_db. This step is optional. To set up SSO integration for the system database, substitute source_db with system.

SQL

ALTER DATABASE source_db SET SSO INTEGRATION test_sso;

Grant cross-database privileges to the target database target_db for the default group sso_users.

SQL

GRANT USE ON DATABASE target_db TO GROUP sso_users;

Connect to the target database target_db at the local host with port number 4050 using the JDBC driver with an access token on the source database access_token@source_db. Specify a password token.

Text

Properties props = new Properties();
props.setProperty("user", "access_token@source_db");
props.setProperty("password", token);
props.setProperty("handshake", "sso");
Connection conn = DriverManager.getConnection("jdbc:ocient://host:4050/target_db", props);

Or, connect using the device flow by using the name of the source database @source_db.

Text

Properties props = new Properties();
props.setProperty("user", "@source_db");
props.setProperty("handshake", "sso");
props.setProperty("ssoOAuthFlow", "deviceGrant");
Connection conn = DriverManager.getConnection("jdbc:ocient://host:4050/target_db", props);

Verify your connection using these queries:Check the current user using the CURRENT_USER function.

SQL

SELECT CURRENT_USER();

Check the current groups using the CURRENT_GROUPS function.

SQL

SELECT CURRENT_GROUPS();

Check the current database using the CURRENT_DATABASE function.

SQL

SELECT CURRENT_DATABASE();

View session information using the sys.sessions system catalog table and the identifier of the current session by using the CURRENT_SESSION_ID function.

SQL

SELECT * FROM sys.sessions WHERE id = CURRENT_SESSION_ID();

You can also retrieve information about the defined groups using the sys.groups system catalog table.

Driver Connection Properties These tables describe the connection properties you can specify. pyocient Parameters This table lists the DNS parameters that apply to SSO integration. For details, see Ocient Python Module: pyocient.

Parameter	Description	Example
`handshake`	Authentication method to use for connection.	`sso`
`ssoOAuthFlow`	Type of OAuth flow.	`deviceGrant` or `authenticationCode`
`identityprovider`	The name of the SSO integration.	`test_sso`

JDBC Driver Properties This table lists the JDBC connection properties that apply to SSO integration. For details, see CONNECT.

Property	Description	Example
`handshake`	Authentication method to use for connection.	`sso`
`ssoOAuthFlow`	Type of OAuth flow.	`deviceGrant` or `authenticationCode`
`identityprovider`	The name of the SSO integration.	`test_sso`

User Access Control and Workload Management with SSO

The database maintains an internal User Access Control model consisting of groups, roles, and service classes. The database relies on group or role membership to determine the privileges of a user or service class. The following integration properties allow administrators to grant SSO users membership to a Database group or role:

The groups_claim_ids and roles_claim_ids properties define the token claims that specify the Provider-defined groups or roles of the user.
The groups_claim_mappings and roles_claim_mappings properties define mappings between a Provider and Database groups or roles.
The additional_scopes property should include any request scopes needed for the Provider to include the group or role claims in tokens it issues.

Because SSO users can map to multiple groups, when choosing which group to assign creator privileges, the system follows this criteria:

Filter by authorization using only groups with CREATE privileges on the relevant schema or database.
When multiple authorized groups exist at different levels, the system uses hierarchy precedence to ensure that privileges are assigned at the most specific scope. The enforced hierarchy is that schema-level group privileges take precedence over database-level group privileges.
If multiple groups match at the same level, the system selects the group name by using alphabetical order.

For example, if an SSO user belongs to the analytics_team group (with CREATE privileges at the database level) and finance_schema_admins group (with CREATE privileges at the schema level), and creates a table in the finance schema, the system assigns creator privileges to finance_schema_admins.For details about privileges, see Data Control Language (DCL) Statement Reference.

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".

SQL

ALTER DATABASE example_database
    ALTER SSO INTEGRATION oidc

        /* Tell the Database to request the "profile", "email" and "offline_access" scopes */
        additional_scopes = [ profile, email, offline_access ],

        /* Tell the Database to look for the "user_groups" token claim to establish role membership */
        roles_claim_ids = [ user_groups ],

        /* Map provider group "Example Provider Group" to database role "example_database Analyst" */
        roles_claim_mappings = { "Example Provider Group" = "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 this statement.

SQL

ALTER DATABASE example_database
    ALTER SSO INTEGRATION oidc
        blocked_groups = [ <provider_group_name> [, ...] ],
        blocked_roles  = [ <provider_role_name>  [, ...] ];

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.

User Claim Identifiers (IDs)

By default, audit trails, including but not limited to system-level log messages, identify users connecting using 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 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 this SQL statement.

SQL

ALTER DATABASE example_database
    ALTER SSO INTEGRATION oidc
        user_claim_ids = [first_name, last_name, email];

After executing this statement, users are identified with the <first_name>::<last_name>::<email> format. (For example: John::Smith::jsmith@example.com)

Updating the user_claim_ids property has no effect on users that are already connected to the database.

System Catalog Tables

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 this SQL statement.

SQL

SELECT *
    FROM sys.databases
    JOIN sys.oidc_integrations
    ON databases.id = oidc_integrations.database_id
    WHERE databases.name = 'example_database';

The user must be a database administrator to view the OpenID Connect properties.

Data Definition Language (DDL) Statement Reference SQL Reference System Catalog

​Password-Based Authentication

​Fully Qualified User Name (FQUN)

​How To Connect using Password Authentication

​Single Sign On (SSO)

​OpenID Connect

​Authorization Code Flow

​SSO Token Flow

​Configure the OpenID Provider

​SSO Parameters

​Set Up Machine-To-Machine SSO Integration

​Set Up Cross-Database SSO Integration

​User Access Control and Workload Management with SSO

​User Claim Identifiers (IDs)

​System Catalog Tables

​Related Links

Password-Based Authentication

Fully Qualified User Name (FQUN)

How To Connect using Password Authentication

Single Sign On (SSO)

OpenID Connect

Authorization Code Flow

SSO Token Flow

Configure the OpenID Provider

SSO Parameters

Set Up Machine-To-Machine SSO Integration

Set Up Cross-Database SSO Integration

User Access Control and Workload Management with SSO

User Claim Identifiers (IDs)

System Catalog Tables

Related Links