Authentication Methods

{{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 docid\ kuzemuw8ipvp0dzq 5dea to manage settings such as minimum password length and password expiration policies fully qualified user name (fqun) to connect to the database, the user must provide a fqun for database users, the fqun has this form \<user name>@\<database> for example, the fqun of the user alice , a member of the database example database alice\@example database how to connect using password authentication the fqun should be set in the connection string along with the password of the user for example, this code is the command alice would use to connect to example database using the ocient jdbc driver 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 https //www oauth com/oauth2 servers/server side apps/authorization code/ when connecting to the database, users are directed (using 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 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 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 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 — \<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 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 connect to jdbc\ ocient //\<host> \<port>/example database;handshake=sso;user=access token;password=\<access token data>; configuring 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 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 n/a 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 n/a the client identifier as registered with the openid provider default group yes string n/a the group users are assigned if no group is specified in the group claim mappings property for the openid provider 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 blocked groups no list of strings (e g , value \[, ] \[] specifies provider groups that are restricted from connecting to the database blocked roles no list of strings (e g , value \[, ] \[] specifies provider roles that are restricted from connecting to the database after all parameters have been retrieved, a database administrator can execute a ddl sql statement using this syntax 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 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 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 cluster and node management docid\ csequa9yqcqaaaexspyue 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 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 database, tables, views, and indexes docid\ a4jvhkvg31tapexr9zpcq sql statement to integrate the sso protocol into a database for connectivity pools, use the cluster and node management docid\ csequa9yqcqaaaexspyue sql statement 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 connect to jdbc\ ocient //localhost 4050/system;handshake=sso;user=id token;password=\<access token> 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/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 define 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" 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 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 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 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 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 docid\ e36p0yk3fcwdnuiimxkn2 sql reference docid\ eaohxmozywvukdfizfpel system catalog