Database Users and Roles

This article provides an overview of how database users and roles are managed in Exasol.

Access rights in the database are managed using data control language (DCL) SQL statements. Database administrators grant privileges to users and roles to determine who is permitted to perform the different actions in the database.

The following SQL statements are used to manage access control in Exasol:

SQL statement Usage
CREATE USER Create a new user
ALTER USER Change the user password
DROP USER Remove a user
CREATE ROLE Create a role
DROP ROLE Remove a role
GRANT Give privileges to users and roles
REVOKE Remove privileges from users and roles
ALTER SCHEMA Change the owner of a schema and its objects, or set schema quotas

Users

Database administrators can add users to the database with the CREATE USER statement. The new user can be configured to use either LDAP, Kerberos SSO, or a password for authentication. When using password authentication, the required complexity of the password is determined by the PASSWORD_SECURITY_POLICY entry in the system table EXA_PARAMETERS.

The naming conventions for users and passwords are the same as for SQL identifiers. For more information about SQL identifiers, see SQL identifier.

Database user names and role names are not case sensitive.

User privileges

To perform an action in the database, a user must have the appropriate privileges for that action. These privileges are (granted) and withdrawn (revoked) by a database administrator. For example: to connect to the database, a user needs the system privilege CREATE SESSION. To temporarily disable access to the database for that user, the administrator can revoke the CREATE SESSION privilege.

SYS user

The database has a special user SYS that cannot be deleted. The SYS user has universal privileges and can perform all actions on the database.

The default password of the SYS user is exasol. To change the SYS user password you must be logged in as SYS or as a user with the ALTER USER privilege: 

ALTER USER sys IDENTIFIED BY "<new_password>";

To prevent a security risk, always change the default password of the SYS user to a secure password.

Roles

Using roles facilitates grouping of users and simplifies rights management. You use CREATE ROLE to create roles, then use GRANT to assign roles to users. You can grant multiple roles to the same user. If you want to assign the same privileges to a number of users, you create a role with those privileges and grant that role to the users. You can also create a hierarchical structure of privileges by assigning roles to roles.

A role cannot be disabled. To remove privileges that have been assigned to a user through a role, you must withdraw the role using REVOKE.

There are two predefined roles in Exasol:

  • PUBLIC: Every user receives this role automatically when the user is created. This simplifies granting and revoking privileges on all users of the database. The PUBLIC role cannot be deleted.
  • DBA: This role is for the database administrator and has universal privileges. This role should only be assigned to very few users, since it provides full access to the database. The DBA role cannot be deleted.

Security

To enable security rules for user accounts, you can set policies individually. The policies are deactivated by default and must be enabled through system parameters or user-specific settings.

The following methods apply to database users that are configured to use password authentication. For users authenticating using LDAP or Kerberos, security rules are configured in the respective external services.

Password security

You can specify rules for passwords by adjusting the system parameter PASSWORD_SECURITY_POLICY. You can find the current policy in form of a string value in the system table EXA_PARAMETERS and change it through ALTER SYSTEM command.

The value can be either OFF (completely deactivated) or contain of a list of rules separated by colons.

  • MIN_LENGTH: Minimum number of characters
  • MAX_LENGTH: Maximum number of characters (maximum value is 128)
  • MIN_LOWER_CASE: Minimum number of lowercase characters
  • MIN_UPPER_CASE: Maximum number of uppercase characters
  • MIN_NUMERIC_CHARS: Minimum number of numerical characters (0-9)
  • MIN_SPECIAL_CHARS: Minimum number of special characters
  • REUSABLE_AFTER_CHANGES: Number of password changes after which an old password may be reused
  • REUSABLE_AFTER_DAYS: Number of days after which an old password may be reused
  • MAX_FAILED_LOGIN_ATTEMPTS: Maximum number of failed login attempts after which the user will be locked out

For example:

ALTER SYSTEM SET PASSWORD_SECURITY_POLICY='MIN_LENGTH=8:MIN_NUMERIC_CHARS=1';

The purpose of the MAX_FAILED_LOGIN_ATTEMPTS parameter is to provide protection against brute force attacks. The actual number of failed attempts since the last successful one can be displayed in the system tables EXA_USER_USERS and EXA_DBA_USERS. If the limit is reached, a warning appears in the log and the user will not be able to log in anymore. An administrator can unlock the user with the following command:

ALTER USER u1 RESET FAILED LOGIN ATTEMPTS;

The number of failed login attempts will be reset to 0 for all users after a database restart.

Password expiry policy

The system parameter PASSWORD_EXPIRY_POLICY defines when a user password expires and how much time there is to change the passwords. The policy is a string value in the system table EXA_PARAMETERS that can be changed using ALTER SYSTEM.

The value of this parameter is either OFF (passwords never expire), or contains the following two rules separated by a colon:

  • EXPIRY_DAYS: Number of days after which a password expires.
  • GRACE_DAYS: Number of days after password expiration when the user can still log in to change the password.

For example:

ALTER SYSTEM SET PASSWORD_EXPIRY_POLICY='EXPIRY_DAYS=180:GRACE_DAYS=7';

After the password has expired, the user has a grace period defined in GRACE_DAYS when they can log in and change the password but not execute any SQL command or query. If the password is not changed within the grace period, an administrator can unlock it by setting a new password.

  • The SYS user password never expires.
  • Already expired passwords will still be expired even if you change the expiry policy.

We recommended to let the password that is set on user creation to expire instantly by using the EXPIRE clause of the ALTER USER command. This ensures that the password will be changed immediately by the user after logging in for the first time.

For example:

ALTER USER u1 IDENTIFIED BY "temporary_password_CH73X"; 
ALTER USER u1 PASSWORD EXPIRE;

User-specific settings

You can overwrite the system-wide password expiry policy for specific users through the ALTER USER statement.

For example:

ALTER SYSTEM SET PASSWORD_EXPIRY_POLICY='EXPIRY_DAYS=180:GRACE_DAYS=7'; 
ALTER USER u1 SET PASSWORD_EXPIRY_POLICY='OFF';
ALTER USER u2 SET PASSWORD_EXPIRY_POLICY='EXPIRY_DAYS=180:GRACE_DAYS=30';

If the value is not set (it is equals to NULL), the system-wide setting (EXA_PARAMETERS) will be used. In the example above, that setting is overwritten. For u1 the expiry is explicitly deactivated, while for u2 the grace period was extended to 30 days.

Users can see their personal settings in the system table EXA_USER_USERS. For administrators, this information is also available for all users in the system table EXA_DBA_USERS.

Locking accounts

To temporarily lock out a user from the system, you can revoke the CREATE SESSION privilege if it was granted specifically on that user.

If you have granted the CREATE SESSION privilege on the role PUBLIC or on another role that has been granted to the user, the user will still be able to log in.