> ## Documentation Index > Fetch the complete documentation index at: https://docs.ocient.com/llms.txt > Use this file to discover all available pages before exploring further. # Authentication Methods export const Ocient = "Ocient®"; {Ocient} 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](/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 Text theme={null} @ ``` For example, the FQUN of the user `alice`, a member of the database `example_database`. ```Text Text theme={null} 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 Text theme={null} connect to jdbc:ocient://:/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](https://openid.net/) | 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](https://www.oauth.com/oauth2-servers/server-side-apps/authorization-code/). 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. Sequence of authentication 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 Text theme={null} connect to jdbc:ocient://:/example_database;handshake=SSO;user=;password=; ``` These Ocient driver properties must be set when connecting using the Authorization Code flow: * handshake — `SSO` * user — `` * password — `` 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](https://openid.net/specs/openid-connect-core-1_0.html#IDToken) or an [Access Token](https://datatracker.ietf.org/doc/html/rfc6749#section-1.4) 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 — `` 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 Text theme={null} connect to jdbc:ocient://:/example_database;handshake=SSO;user=id_token;password=; ``` This format is the JDBC command to connect to `example_database` database using an Access Token. ```Text Text theme={null} connect to jdbc:ocient://:/example_database;handshake=SSO;user=access_token;password=; ``` ### 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: 1. Create an application for the Ocient System in the Provider. Select the "Native Application" application type. 2. Enable the Authorization Code, Refresh Token, and Device Authorization grant types. 3. Grant permission to the appropriate users to use the newly created application. 4. Enter this Redirect URI. | **Application** | **Redirect URI** | | --------------- | ------------------------------------------------- | | JDBC Driver | `http://localhost:7050/ocient/oauth2/v1/callback` | | OpenAPI or UI | `https:///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](/cluster-and-node-management#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](https://openid.net/specs/openid-connect-core-1_0.html#Terminology) 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](https://openid.net/specs/openid-connect-core-1_0.html#Terminology) as registered with the OpenID Provider. | | `client_secret` | Yes | string | None | The [client secret](https://openid.net/specs/openid-connect-core-1_0.html#IDTokenValidation) 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 SQL theme={null} ALTER DATABASE database { SET | ALTER } SSO INTEGRATION sso_protocol [, ...] ::= -- literal or string property_name = value | -- list property_name = [ value [, ...] ] | -- map property_name = { key = value [, ...] } # map ``` ```sql SQL theme={null} 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 SQL theme={null} 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](/cluster-and-node-management#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 SQL theme={null} 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](/databases#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](/cluster-and-node-management#alter-connectivity_pool-set-sso-integration) SQL statement. ```sql SQL theme={null} ALTER DATABASE example_database SET SSO INTEGRATION sso_test; ``` **Connect to your system.** Use a JDBC connection string that includes the access token value ``. This string connects to the local host at port number `4050`, uses the SSO protocol, and with the username `id_token`. ```shell Shell theme={null} CONNECT TO jdbc:ocient://localhost:4050/system;handshake=SSO;user=id_token;password= ``` ### 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](/other-functions-and-expressions#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](/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 SQL theme={null} 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 SQL theme={null} 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 SQL theme={null} 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 SQL theme={null} 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. ```none Text theme={null} 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`. ```none Text theme={null} 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](/other-functions-and-expressions#current_user) function. ```sql SQL theme={null} SELECT CURRENT_USER(); ``` Check the current groups using the CURRENT\_GROUPS function. ```sql SQL theme={null} SELECT CURRENT_GROUPS(); ``` Check the current database using the [CURRENT\_DATABASE](/other-functions-and-expressions#current_database) function. ```sql SQL theme={null} 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](/other-functions-and-expressions#current_session_id) function. ```sql SQL theme={null} 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](/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](/commands-supported-by-the-ocient-jdbc-cli-program#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](/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 SQL theme={null} 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 SQL theme={null} ALTER DATABASE example_database ALTER SSO INTEGRATION oidc blocked_groups = [ [, ...] ], blocked_roles = [ [, ...] ]; ``` 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 SQL theme={null} 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 `::::` 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 SQL theme={null} 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. ## Related Links [Data Definition Language (DDL) Statement Reference](/data-definition-language-ddl-statement-reference) [SQL Reference](/sql-reference) [System Catalog](/system-catalog)