CREATE USER

Purpose

Use this statement to add users to the database.

Prerequisite

You must have the system privilege CREATE USER.

Syntax

create_user::=

Create User

Usage notes

  • The system privilege CREATE SESSION must always be granted for the user to be able to log in.

  • User names are restricted by the same rules as SQL identifiers, except that user names are case insensitive even when delimited by quotation marks. For example, the usernames Test, TEST, and test are synonymous. Periods are not allowed in usernames.

    To learn more, see SQL identifier.

  • Users can authenticate against the database with a password or through Kerberos, OpenID, or LDAP.
  • A user will not be automatically associated with a schema when created.
  • A user can be altered using the ALTER USER command and renamed using the RENAME command.
  • For information about database users, refer to the system tables EXA_DBA_USERS, EXA_ALL_USERS, and EXA_USER_USERS.
  • For details about password security (complexity, expiry, locking accounts), see Database users and roles and Privileges.

Authenticate with password

If the database user should authenticate with a password, use CREATE USER <username> IDENTIFIED BY <password>.

If you use delimited (quoted) identifiers, the password is case sensitive. For more information, see SQL identifier.

Example:
Copy 
CREATE USER user_1 IDENTIFIED BY "h12_Xhz";
GRANT CREATE SESSION TO user_1; 

Authenticate using Kerberos

To enable the user to authenticate with Kerberos, use: CREATE USER <username> IDENTIFIED BY KERBEROS PRINCIPAL <principal>.

The JDBC and ODBC drivers will then authenticate the user through the Kerberos service.

Example:
Copy 
CREATE USER user_2 IDENTIFIED BY KERBEROS PRINCIPAL "<user>@<realm>";
GRANT CREATE SESSION TO user_2; 

For more information about Kerberos authentication, see Kerberos SSO.

Authenticate using LDAP

To enable the user to authenticate with LDAPClosed Lightweight Directory Access Protocol (authentication service), use CREATE USER <username> IDENTIFIED AT LDAP AS '<dn_string>'.The parameter dn_string specifies the distinguished name, which is the username configured on the LDAP server.

The LDAP server must be configured per database within EXAoperation.

SASLClosed Simple Authentication and Security Layer and certification management are not supported.

To enable the user to authenticate with LDAP, do the following:

  1. Make sure that your LDAP server is already set up for authenticating database users.

  2. Log in to EXAoperation.
  3. Shut down the database.
  4. Edit the database and add the LDAP Server URLs starting with either ldap:// or ldaps://.
    For more information about how to edit a database, see Edit a Database.
  5. Click Apply to save the changes.
  6. Start the database.
  7. Log in to your database using an SQL client.
  8. Create a user with LDAP authentication, execute the following statement:
    Copy
    CREATE USER user_3 IDENTIFIED AT LDAP
    AS 'cn=user_3,dc=authorization,dc=exasol,dc=com';
    GRANT CREATE SESSION TO user_3;

    To get the username and other details you can use various tools depending on your platform, such as ldapsearch (Linux) or get-aduser (Windows). For more information, refer to the documentation for your operating system.

To learn more about how to use LDAP with Exasol, see the following Knowledge Base articles:

Authenticate using OpenID

Exasol allows authentication of database users with OpenID. OpenID verifies client identities using authentication done by an authorization server. Exasol acts as a resource server and authenticates database connections opened by Exasol ODBC, JDBC, ADO.NET, or WebSockets clients using an OpenID/OAuth access token or refresh token. The implementation follows OpenID Connect Core 1.0 and internet standards RFC 6749, RFC 7517, and RFC 7519.

To enable a user to authenticate against the database using OpenID you must add the following parameters for either access token or refresh token authentication. For more information, see Edit a Database.

Configuration parameters used to enable access token based authentication:

Parameter Description
-oidcProviderJKU OpenID provider endpoint to retrieve the JSON Web Key Set (JWKS) used for signing JWS tokens (mandatory).
-oidcProviderAUD Verifies the audience in an OpenID access token (optional/recommended).
-oidcProviderISS Verifies the issuer in an OpenID access token (optional/recommended).

Configuration parameters used to enable refresh token based authentication:

Parameter Description
-oidcProviderTokenEndpoint OpenID provider endpoint to retrieve OAuth 2.0 and OpenID connect tokens (mandatory).
-oidcProviderClientId OpenID Client ID registered with OpenID provider (mandatory).
-oidcProviderClientSecret

Client secret of the OpenID Client registered with the OpenID Provider (mandatory).

If Proof Key for Code Exchange (PKCE) is enabled, the parameter is not set.
-oidcRefreshTokenGrantScope OpenID scopes to be requested during refresh token exchange (optional).
Default: offline_access
This parameter is required for integrations with Azure AD, which requires that you provide at least one application-specific scope with offline_access. Separate multiple application-specific scopes with a white space. To configure the scope in Azure, refer to the Azure documentation.
-oidcRefreshTokenCacheCapacity

Cache capacity of the refresh token based authentication information (optional).

Default: 10000
-oidcRefreshTokenCacheCompactionInterval

Interval (in seconds) for Exasol to perform maintenance procedures for a refresh token based authentication information cache (optional).

Default: 3600 seconds (1 hour)

Optional additional configuration parameters for OpenID authentication:

Optional parameter Description
-oidcJKUFetchInterval

Interval (in seconds) for Exasol to fetch new signing keys from OpenID provider JWKS.

Default: 86400 seconds (24 hours)
-oidcProxy

The proxy URL to be used for connections in a refresh token OpenID provider endpoints.

Supported protocols:

  • http
  • https
  • socks4
  • socks4a
  • socks5
  • socks5h

After the parameters are set up, run the following statement to create a new user or modify an existing user with OpenID authentication.

Copy 
CREATE USER oidctestuser IDENTIFIED BY OPENID SUBJECT 'database-user@exasol.example';
GRANT CREATE SESSION TO oidctestuser;
Copy 
ALTER USER oidctestuser IDENTIFIED BY OPENID SUBJECT 'database-user@exasol.example';

Multi-factor authentication (MFA)

Multi-factor authentication (MFA) can be used if it is supported by the authentication provider. For more information, refer to the authentication provider’s documentation.