You can use the Access Control Using SQL (DCL) to control the security of the database and the access to it. You can manage the users and roles to specify who is allowed to perform actions in the database.
The following SQL statements are the components of the DCL:
- CREATE USER: Creates a user.
- ALTER USER: Changes the password of a user.
- DROP USER: Deletes a user.
- CREATE ROLE: Creates a role.
- DROP ROLE: Deletes a role.
- GRANT: Gives roles, system privileges, and object privileges to users or roles.
- REVOKE: Withdraws roles, system privileges, and object privileges from users or roles.
- ALTER SCHEMA: Changes the owner of a schema (and all its schema objects) or sets schema quotas.
Users
An administrator can create an account for users who want to connect to the database with CREATE USER SQL statement. The new users get either an LDAP configuration, Kerberos Principal, or a password (can be changed later).
The password security policy defines how complex the user's passwords should be. It is listed in system table EXA_PARAMETERS (entry PASSWORD_SECURITY_POLICY) and you can change it through ALTER SCHEMA.
The naming conventions for user names and passwords are same as SQL identifiers (identifier for database objects such as table names. For more information, see SQL Identifier). However, with this, case sensitivity is insignificant.
User name and user roles are not case sensitive.
Appropriate privileges are required for a user to perform an action in the database. These privileges are given or withdrawn by an administrator or other users (administrator users). For example, a user needs the system privilege CREATE SESSION to connect to the database. If you want to disable the user temporarily, this system privilege can be withdrawn.
For changing the user identity after the login, you can use the statement IMPERSONATE.
SYS User
The database has a special user SYS that cannot be deleted. The SYS user has universal privileges.
The default password of the SYS user is exasol. You should change the password at first login to prevent any security risk.
Security
To enable security rules for the user accounts, you can set policies individually for your requirements. By default, the policies are deactivated and should be enabled through system parameters or user-specific settings. The following mechanisms are only applied for password-authorized users. In case of LDAP-authorized users, such rules are configured in the external LDAP service.
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 is either OFF (completely deactivated) or consists of a list of rules that are separated by colons. Here is an example for the password security policy.
The parameters for the password security are:
- MIN_LENGTH: Minimum length for passwords.
- MAX_LENGTH: Maximum length for passwords (128 characters at maximum).
- MIN_LOWER_CASE: Minimum number of lower-case characters.
- MIN_UPPER_CASE: Maximum number of upper-case characters.
- MIN_NUMERIC_CHARS: Minimum number of numbers.
- MIN_SPECIAL_CHARS: Minimum number of special characters (all UTF-8 non-numerical characters that don't have any lower / uppercase spelling).
- 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.
Do note the maximal number of failed login attempts to avoid brute force attacks (MAX_FAILED_LOGIN_ATTEMPTS). 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
After a database restart, the number of failed login attempts will be reset to 0 for all users.
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. You can find the policy as a string value in the system table EXA_PARAMETERS and can change through ALTER SYSTEM.
Its value is either OFF (passwords never expire) or consists of two parameters that are separated by a colon. Here is an example for the password expiry policy:
The parameters for the password expiry are:
- EXPIRY_DAYS: Number of days after which a password expires.
- GRACE_DAYS: Number of days within the user must change the password before being locked out.
After the expiry of the password, the user has a grace period (GRACE_DAYS) to log in and change the password. User must change the passwords before being allowed to 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 keep the status even if you change the expiry policy.
It is recommended to let the (temporary) password expire instantly by using the EXPIRE clause of the ALTER USER command, for ensuring that it will be changed by the user.
User-specific Settings
You can overwrite the system-wide password expiry policy for specific users through the ALTER USER statement as shown in the following 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
If you want to lock out certain users temporarily from the system, you can revoke the CREATE SESSION privilege.
This is not applicable if you grant that privilege to role PUBLIC (or another role of the user) instead of the users directly.
Roles
Role facilitates the grouping of users and simplifies the rights management. You can use the CREATE ROLE statement to create roles. You can assign multiple roles to the same user with GRANT SQL statement. If you are giving similar privileges to many users, you can create a new role with those privileges and assign the role to the users. A hierarchical structure of privileges is also possible by assigning roles to roles.
Roles cannot be disabled. If you want to reverse the assignment of a role, you can withdraw it by using the REVOKE SQL statement.
There are two predefined roles:
- PUBLIC: Every user receives this role automatically. It simplifies the grant or withdraw privileges from the users of the database. However, it should only happen if you are sure that it is safe to grant the respective rights and the shared data should be publicly accessible. The PUBLIC role cannot be deleted.
- DBA: This role is for the database administrator and has all the possible privileges. This role should only be assigned to very few users because it provides full access to the database. The DBA role cannot be deleted.
