CREATE USER

Purpose

Use this statement to add users to the database. Exasol uses password, Kerberos SSO, OpenID, or LDAP for authentication of the database users.

Prerequisite

You must have the system privilege CREATE USER.

Syntax

create_user::=

Create User

Usage Notes

  • For the user to be able to log in, the system privilege CREATE SESSION must be granted.

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

  • For authentication, you can use passwords, 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 the 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.

Examples

CREATE USER user_1 IDENTIFIED BY "h12_xhz";
CREATE USER user_2 IDENTIFIED AT LDAP
AS 'cn=user_2,dc=authorization,dc=exasol,dc=com';
GRANT CREATE SESSION TO user_1; 
CREATE USER oidctestuser IDENTIFIED BY OPENID SUBJECT 'database-user@exasol.example';
GRANT CREATE SESSION TO oidctestuser;

Authentication using password

If you want to authenticate the database user with a password, create a user authenticated by a password. The database checks the password when they log in. The password has to be specified as identifier. If you use delimited (quoted) identifiers, then the password is case sensitive. For more information, see SQL identifier.

In case of a regular identifier (unquoted) the password will be set to uppercase letters and has to be adjusted for the login.

Authentication using Kerberos

If you want to enable single sign on with Kerberos, create a user authenticated by Kerberos. The JDBC and ODBC drivers will then authenticate the user by Kerberos service (single sign-on). The defined principal looks like <user>@<realm>.

For additional information about the overall Kerberos configuration, refer to the Kerberos Single Sign-On section.

Authentication using LDAP

If you want to authenticate the database user with LDAPClosed Lightweight Directory Access Protocol (authentication service), create a user authenticated by LDAP. The database checks the password against an LDAP server which can be configured per database within EXAoperation. The parameter dn-string (string in single quotes) specifies the so-called distinguished name which is the username configured in an LDAP server. The SASLClosed Simple Authentication and Security Layer and certification management are not supported.

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

Do the following to configure the LDAP authentication of database users:

  1. Log in to EXAoperation.
  2. Shut down the database.
  3. 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.
  4. Click Apply to save the changes.
  5. Start the database.
  6. Log in to your database using an SQL client.
  7. Run the following statement to create a user with LDAP authentication.
    CREATE USER j.doe IDENTIFIED AT LDAP
    AS 'cn=j.doe,dc=authorization,dc=exasol,dc=com';

    You can find the required information for the above statement from the following methods depending on your operating system:

    • Windows OS: You can use one of the following commands to find the information from your LDAP server:
      a) Get-ADUser: Use the command through Powershell. To use this method, you need Remote Server Administration Tools installed and the feature Active Directory-Module for Windows PowerShell at your system. Example:

      C:\Users\john\powershell.exe get-aduser john


      b) dsquery: Use the command through cmd with admin privilege. To use this method, you need Remote Server Administration Tools installed on your system. Example:

      C:\Users\john\dsquery user john

    • Linux OS: You can use the LDAPSEARCH command to find the information from your LDAP server. Examples:

      ldapsearch -LLL "sn=Doe"
      ldapsearch -LLL "givenName=John"

      For more information, see Linux man page for LDAPSEARCH.

Further Information on LDAP in Exasol

If you want to learn more about LDAP authentication in Exasol, see the following Exasol Knowledge Base articles:

Authentication using OpenID

Exasol allows you to authenticate database users by OpenID. OpenID verifies clients' identities using authentication done by an Authorization Server. With this method, Exasol acts as a resource server and authenticates database connections opened by Exasol ODBC, JDBC, ADO.NET, or WebSockets clients using OpenID/OAuth access token or refresh token. The implementation follows OpenID Connect Core 1.0, Internet standards RFC 6749, RFC 7517, and RFC 7519.

To configure OpenID authentication of database users, you must add the below parameters for either Access token or Refresh Token authentication. For more information, see Edit a Database.

The following table contains configuration parameters used to enable Access Token based authentication:

Configuration 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).

The following table contains configuration parameters used to enable Refresh Token based authentication:

Configuration 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, it 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, see Quickstart: Configure an application to expose a web API - Add a scope.
-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)

The following table lists 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.

CREATE USER oidctestuser IDENTIFIED BY OPENID SUBJECT 'database-user@exasol.example';
GRANT CREATE SESSION TO oidctestuser;
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.