User SQL Commands
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. To assign a user to a role, use either the associated Roles screen or the CREATE ROLE SQL command
Anywhere in this topic 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 >.
The following SQL command may be used to create new local user accounts in Dremio.
CREATE USER <username>
Here is an example of the SQL command:Example
CREATE USER user1
Indicates the username being associated with the new user account. This is the value a new user will enter at the Dremio login screen.
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.
ALTER USER <username> SET PASSWORD '<password>'
ALTER USER <username> UNSET PASSWORD
Example 1: Setting a Password
Here is an example of the SQL command when setting a password for a local user:Set a password
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:Change local user to external user
ALTER USER user1 UNSET PASSWORD
Indicates the username associated with the account being changed.
Indicates the password associated with the affected user account. The user must now use this password when attempting to log in on Dremio.
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.
The following SQL command may be used to delete user accounts from Dremio.
SyntaxDROP USER syntax
DROP USER <username>
Here is an example of the SQL command:DROP USER example
DROP USER user1
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.
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:View users
SELECT * FROM sys.users