User SQL Commands

NOTE:

This functionality is for Dremio v18.0+ Enterprise Edition only.

User management may be performed via the SQL manager. This allows you to perform basic functions on a user-by-user basis, such as creating users, changing their password, changing the user type, and removing a user.

CREATE USER

The following SQL command may be used to create new local user accounts in Dremio.

Syntax

CREATE USER <username>

Here is an example of the SQL command:

CREATE USER user1

NOTE

Anywhere in this documentation that a value is enclosed in < and >, should be treated as an indicator of where a custom value should be provided. The value you enter should not be enclosed in < or >.

Required Parameters

<username>

Indicates the username being associated with the new user account. This is the value a new user will enter at the Dremio login screen.

Multiple Users

When creating multiple users via a single SQL command, you may include additional usernames separating each entry by a single comma. See the example below:

CREATE USER user1,user2

ALTER USER

The following SQL command may be used to set new passwords or change a user type from local to external. This command may only be performed on local users for both scenarios described previously.

Syntax

ALTER USER <username> SET PASSWORD '<password>'
ALTER USER <username> UNSET PASSWORD

NOTE

Anywhere in this documentation that a value is enclosed in < and >, should be treated as an indicator of where a custom value should be provided. The value you enter should not be enclosed in < or >.

Example 1: Setting a Password

Here is an example of the SQL command when setting a password for a local user:

ALTER USER user1 SET PASSWORD 'password123'

Example 2: Changing a Local User to External

Here is an example of the SQL command when changing a local user to an external user:

ALTER USER user1 UNSET PASSWORD

Required Parameters

<username>

Indicates the username associated with the account being changed.

<password>

Indicates the password associated with the affected user account. The user must now use this password when attempting to log in on Dremio.

Error Messages

If you’re trying to set a password for an external user via the ALTER USER command, Dremio will return with an error message. Password changes may not be done for external users with Dremio.

When changing a local user to an external user with the ALTER USER SQL command, Dremio will immediately check with any integrated services to verify whether the username currently exists. If it doesn’t, then Dremio will return with an appropriate error message. The username must exist in the external service to allow for this alteration.

DROP USER

The following SQL command may be used to delete user accounts from Dremio.

Syntax

DROP USER <username>

Here is an example of the SQL command:

DROP USER user1

NOTE

Anywhere in this documentation that a value is enclosed in < and >, should be treated as an indicator of where a custom value should be provided. The value you enter should not be enclosed in < or >.

Required Parameters

<username>

Indicates the username associated with the account being deleted.

Deleting External Users

When deleting an external user from Dremio, this does not prevent them from accessing Dremio again. The next time that user logs in on Dremio using their external credential manager, a new account is created for them in Dremio automatically.

Deleting Multiple Users

When deleting multiple users via a single SQL command, you may include additional usernames, separating each entry by a single comma. See the example below:

DROP USER user1,user2

System Table

Administrators may view a list of all existing users, the privileges they’ve been granted, and their user type from the sys.users system table. This is accessed from the SQL Editor and is viewable only by administrators in Dremio.

To view users, enter the following command:

SELECT * FROM sys.users