CREATE USER

Purpose

Use this statement to add users to the database. Exasol uses the specified password for authentication.

Prerequisite

You need to have the system privilege CREATE USER.

Syntax

create_user::=

Create User

Usage Notes

  • For the user to be able to login subsequently, the system privilege CREATE SESSION must be granted.
  • For the user name, the same rules as for SQL identifiers apply. However, even with identifiers in quotation marks, no attention is paid to case sensitivity. This means that the usernames "Test", "TEST", and test are synonymous. For more information, refer to the SQL Identifier section. Furthermore, different from other SQL identifiers, the dot symbol is allowed in usernames.
  • For authentication, you can use passwords, Kerberos, or LDAP.
  • A user created will not be automatically associated with a schema.
  • A user can be altered by the ALTER USER command and renamed by using the RENAME command.
  • 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, refer to the Database Users and Roles and Privileges sections.

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; 

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 user's password at the time of login. The password has to be specified as identifiers. If you use delimited (quoted) identifiers, then the password is case sensitive. For more information, refer to the SQL Identifier section.

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 LDAPClosedLightweight 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 SASLClosedSimple Authentication and Security Layer and certification management are not supported.

Make sure your active directory system 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.
      For example, C:\Users\john\powershell.exe get-aduser john
      b) dsquerry: Use the command through cmd with admin privilege. To use this method, you need Remote Server Administration Tools installed on your system.
      For example, C:\Users\john\dsquery user -name john*
    • Linux OS: You can use LDAPSEARCH command to find the information from your LDAP server. For more information, see Linux man page for LDAPSEARCH.
      For example, ldapsearch -LLL "sn=Doe"
      or ldapsearch -LLL "givenName=John"
Further Information on LDAP in Exasol

If you want to know more about LDAP authentication in Exasol, see the following Exasol KB articles: