> ## Documentation Index > Fetch the complete documentation index at: https://docs.ocient.com/llms.txt > Use this file to discover all available pages before exploring further. # Users, Groups, and Service Classes export const Ocient = "Ocient®"; Users, Groups, and Service Classes are managed using DDL SQL statements that allow administrators to create, drop, or alter each type of database object. These commands also allow administrators to associate service classes with groups and control service class settings for workload management. ## USER ### CREATE USER `CREATE USER` creates a new user, which is scoped to the database of the active connection processing the DDL statement. This means that usernames can be reused across different databases. **Syntax** ```sql SQL theme={null} CREATE USER [ IF NOT EXISTS ] fully_qualified_username [, ...] ] ::= PASSWORD [=] 'password_string' | [ FIRST_NAME [=] 'first_name_string' ] | [ LAST_NAME [=] 'last_name_string' ] | [ EMAIL [=] 'email_string' ``` | **Parameter** | **Data Type** | **Description** | | -------------------------- | ------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `fully_qualified_username` | string | A fully qualified username (FQUN) that follows the pattern `user_name@database_name` and uniquely identifies a user in {Ocient}.
An FQUN can reference a user associated with a different database than the one you are connected to.
For FQUN examples, see the table below. | If you specify only a `user_name` without a `database_name`, Ocient assumes the user is associated with the database of the active connection. | **user id** | **user name** | **database name** | **fully qualified user name (FQUN)** | | ----------- | ------------- | ----------------- | ------------------------------------ | | 000 | alice | db1 | alice\@db1 | | 001 | alice | db2 | alice\@db2 | | 002 | jimmy | system | jimmy\@system | #### Define User Parameters (``) You can apply the following optional parameters when creating a new user object. | **Parameter** | **Data Type** | **Description** | | ------------------- | ------------- | ------------------------------------------------------------ | | `password_string` | string | A password for the user account. This parameter is required. | | `first_name_string` | string | Optional. The first name of the user. | | `last_name_string` | string | Optional. The last name of the user. | | `email_string` | string | Optional. The email of the user. | **Example** This example creates a new user named `jmack@test-database` with the password `pass1234`. ```sql SQL theme={null} CREATE USER "jmack@test-database" PASSWORD = 'pass1234', FIRST_NAME = 'Johnny', LAST_NAME = 'Mack'; ``` ### DROP USER `DROP USER` removes an existing user from the system. To remove a user, you must possess the `DROP USER` privilege for the user. ```sql SQL theme={null} DROP USER [ IF EXISTS ] fully_qualified_username [,...] ``` | **Parameter** | **Data Type** | **Description** | | -------------------------- | ------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `fully_qualified_username` | string | A fully qualified username (FQUN) that follows the pattern `user_name@database_name` and uniquely identifies a user in Ocient.
You can drop multiple users by specifying additional usernames and separating each with commas.
An FQUN can reference a user associated with a different database than the one you are connected to. | If you specify only a `user_name` without a `database_name`, Ocient assumes the user is associated with the database of the active connection. **Example** This example removes a user named `jmack@test-database`. ```sql SQL theme={null} DROP USER "jmack@test-database"; ``` This example drops multiple users. ```sql SQL theme={null} DROP USER "jmack@test-database", "jcollins@test-database"; ``` ### ALTER USER Change the status of a user using the `ALTER USER` SQL statement. **Required Privileges** To change the state of a user, you must have `ALTER` privileges on the user or one of these roles: * System Administrator Role * Security Administrator Role * Administrator Role on the database of the user Users cannot change their own state. **Syntax** ```sql SQL theme={null} ALTER USER { ENABLE | DISABLE | PASSWORD_EXPIRED } ``` | **Parameter** | **Data Type** | **Description** | | ------------- | ------------- | --------------------- | | `user_name` | string | The name of the user. | **Examples** **Enable a User** Enable a user named `example_user`. ```sql SQL theme={null} ALTER USER example_user ENABLE; ``` **Disable a User** Disable a user named `example_user`. ```sql SQL theme={null} ALTER USER example_user DISABLE; ``` **Expire the Password of a User** Change the `example_user` user to the `password_expired` state. This action requires the user to update their password on their next successful login. ```sql SQL theme={null} ALTER USER example_user PASSWORD_EXPIRED; ``` #### ALTER USER SET Changes the password of an existing user. **Syntax** ```sql SQL theme={null} ALTER USER [ IF EXISTS ] fully_qualified_username SET PASSWORD = password_string ``` | **Parameter** | **Data Type** | **Description** | | -------------------------- | ------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `fully_qualified_username` | string | A fully qualified username (FQUN) that follows the pattern `user_name@database_name` and uniquely identifies a user in Ocient.
An FQUN can reference a user associated with a different database than the one you are connected to. | | `password_string` | string | A password for the user account. There are no restrictions on the string used for a password. | If you specify only a `user_name` without a `database_name`, Ocient assumes the user is associated with the database of the active connection. **Example** This example changes the password of user `"jmack@test-database"` to `'newpass'`. ```sql SQL theme={null} ALTER USER "jmack@test-database" SET PASSWORD = 'newpass'; ``` ## GROUP ### CREATE GROUP `CREATE GROUP` creates a new group. The name must be distinct from the name of any existing group in the database. ```sql SQL theme={null} CREATE GROUP [ IF NOT EXISTS ] group_name ``` | **Parameter** | **Data Type** | **Description** | | ------------- | ------------- | ------------------------------------------------------------ | | `group_name` | string | A group name unique from any existing group in the database. | **Example** This example creates a new group named `group1`. ```sql SQL theme={null} CREATE GROUP "group1"; ``` ### DROP GROUP `DROP GROUP` removes an existing group from the system. To remove a group, you must possess the `DROP GROUP` privilege for the group. **Syntax** ```sql SQL theme={null} DROP GROUP [ IF EXISTS ] group_name [, ...] ``` | **Parameter** | **Data Type** | **Description** | | ------------- | ------------- | ---------------------------------------------------------------------------------------------------------------------------------- | | `group_name` | string | The identifier of the group to drop.
You can drop multiple groups by specifying additional group names, separated by commas. | **Example** This example removes a group named `group1`. ```sql SQL theme={null} DROP GROUP "group1"; ``` This example removes multiple groups. ```sql SQL theme={null} DROP GROUP "group1", "group2"; ``` ### ALTER GROUP #### ALTER GROUP USER `ALTER GROUP` adds or removes users from the group using the `USER` keyword. **Syntax** ```sql SQL theme={null} ALTER GROUP [ IF EXISTS ] groupname { ADD | DROP } USER username [, ...] ``` | **Parameter** | **Data Type** | **Description** | | ------------- | ------------- | ------------------------------------------------------------------ | | `group_name` | string | The identifier of the group to alter. | | `username` | string | A username that uniquely identifies a user in the specified group. | If you specify only a `user_name` without a `database_name`, Ocient assumes the user is associated with the database of the active connection. **Example** This example adds `user1` to an existing group named `group1`. ```sql SQL theme={null} ALTER GROUP "group1" ADD USER "user1"; ``` #### ALTER GROUP RENAME `ALTER GROUP` renames an existing group by including the `RENAME TO` keyword. **Syntax** ```sql SQL theme={null} ALTER GROUP [ IF EXISTS ] group_name RENAME TO new_group_name ``` | **Parameter** | **Data Type** | **Description** | | ---------------- | ------------- | ------------------------------------- | | `group_name` | string | The identifier of the group to alter. | | `new_group_name` | string | The new name for the group. | **Example** This example renames an existing group `group1` to `group2`. ```sql SQL theme={null} ALTER GROUP "group1" RENAME TO "group2"; ``` #### ALTER GROUP SET SERVICE CLASS Set a service class for the specified group. **Syntax** ```sql SQL theme={null} ALTER GROUP group_name SET SERVICE CLASS sc_name ``` | **Parameter** | **Data Type** | **Description** | | ------------- | ------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `group_name` | string | The identifier of the group to alter. | | `sc_name` | string | The new name of the service class to assign to the group.
To unset a service class from a group and restore defaults, specify "DEFAULT" as the service class name. | **Example** ```sql SQL theme={null} ALTER GROUP "wlm_test" SET SERVICE CLASS "high_priority"; ``` #### ALTER GROUP ALTER SECURITY Set the security settings at the group level using the `ALTER GROUP ALTER SECURITY` SQL statement. Replace `` with the security setting and `` with the value. **Syntax** ```sql SQL theme={null} ALTER GROUP group_name ALTER SECURITY [=] ``` | **Parameter** | **Data** **Type** | **Description** | | ------------------ | ----------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `group_name` | string | The identifier of the group for setting security settings. | | `security_setting` | string | The security setting with values:
\* `password_minimum_length`
\* `password_complexity_level`
\* `password_no_repeat_count`
\* `password_lifetime_days`
\* `password_invalid_attempt_limit`
For details about these values, see [Database Password Security Settings](/database-password-security-settings). | | `value` | numeric | An integer to represent one of the security settings. For details about this value, see [Database Password Security Settings](/database-password-security-settings). | **Example** Set the password lifetime to 10 days for the group `example_group`. ```sql SQL theme={null} ALTER GROUP example_group ALTER SECURITY password_lifetime_days = 10; ``` ## SERVICE CLASS ### CREATE SERVICE CLASS A service class defines a set of limits on various system parameters. The Ocient System applies service classes per group. By default, all groups are in the `DEFAULT` service class, which has no limits. If a user belongs to multiple groups with different service classes, the system uses the first service class alphabetically by service class name. **Syntax** ```sql SQL theme={null} CREATE SERVICE CLASS [ IF NOT EXISTS ] sc_name [ [, ...] ] ::= MAX_TEMP_DISK_USAGE [=] percentage | MAX_ELAPSED_TIME [=] time | MAX_CONCURRENT_QUERIES [=] queries | MAX_ROWS_RETURNED [=] rows | MINIMIZE_QUERY_DEBUG_RECORDS [=] minimize_query_debug_records | SCHEDULING_PRIORITY [=] priority | CACHE_MAX_BYTES [=] cache_max_bytes | CACHE_MAX_TIME [=] cache_max_time | MAX_ELAPSED_TIME_FOR_CACHING [=] max_elapsed_time_for_caching | MAX_COLUMNS_IN_RESULT_SET [=] max_columns_in_result_set | PRIORITY_ADJUSTMENT_FACTOR [=] priority_adjustment_factor | PRIORITY_ADJUSTMENT_TIME [=] priority_adjustment_time | MIN_PRIORITY [=] priority | MAX_PRIORITY [=] priority | STATEMENT_TEXT { LIKE | REGEX } [=] statement_text_pattern ``` | **Parameter** | **Data Type** | **Description** | | ------------- | ------------- | ------------------------------------ | | `sc_name` | string | The identifier of the service class. | #### Define Service Classes ( `` ) When you create a new service class, the Ocient System sets any omitted service class definitions to the maximum or least restrictive value. Each definition should be comma-separated. When you alter a service class definition, the system modifies only the definition you specify. See the [Workload Management Walkthrough](/workload-management-walkthrough) for more details. | **Parameter** | **Description** | **Values** | | ------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `MAX_TEMP_DISK_USAGE` | Limit the percentage of temporary disk space used by the total service class relative to the amount of remaining free space. If all running queries for a particular service class exceed the given percentage, queries will fail. | A percentage integer from 0-100. | | `MAX_ELAPSED_TIME` | Limit query runtime. The database process kills any queries that exceed this limit. | -1 for unlimited, or a positive integer for the number of seconds. | | `MAX_CONCURRENT_QUERIES` | Limit the maximum number of queries that can run concurrently for a given service class. The database process queues additional queries. | -1 for unlimited, or a positive integer for the number of queries. | | `MAX_ROWS_RETURNED` | Limit number of rows returned by a query. The database process kills queries that exceed this limit. | -1 for unlimited, or a positive integer for the number of rows. | | `MINIMIZE_QUERY_DEBUG_RECORDS` | Stop the load of the `sys.completed_operator_instances` system catalog table and the generation of specific debug logs associated with the query. | Boolean `true` for minimizing the capture of metadata and logs, and `false` or unset (default) for capturing this data. | | `SCHEDULING_PRIORITY` | Limit initial effective query priority. | Any decimal value between the `MIN_PRIORITY` and `MAX_PRIORITY`. | | `CACHE_MAX_BYTES` | Maximum number of bytes in a result set if the result set can be stored in the cache. This value is the number of bytes in the wire-protocol representation, or what is sent to a client. | -1 for none, or a positive integer for the number of bytes. | | `CACHE_MAX_TIME` | Maximum time rows are cached. | -1 for none, or a positive integer for the number of seconds. | | `MAX_ELAPSED_TIME_FOR_CACHING` | Maximum elapsed time for caching. If you set this parameter, queries that exceed maximum elapsed time return no results, but continue to run in the background until either the query reaches the maximum elapsed time for caching or the query successfully completes and caches the result set. For details, see documentation. | -1 for none, or a positive integer for the number of seconds. | | `MAX_COLUMNS_IN_RESULT_SET` | Limit number of columns returned by a query. The database process kills queries that exceed this limit. | -1 for unlimited, or a positive integer for the number of columns. | | `PRIORITY_ADJUSTMENT_FACTOR` | Percentage amount to adjust query priority. You can override this limit at the query level or the session level. Formula is:
`(new_priority = current_priority * priority_adjustment_factor)` | 0 to disable dynamic priority adjustment (default), or a positive double value that is greater than 0. Values less than 1 decrease priority over time until the priority reaches the service class minimum priority, while values greater than 1 increase priority over time until the priority reaches the service class maximum priority. | | `PRIORITY_ADJUSTMENT_TIME` | Time period that indicates the frequency for the adjustment of query priority during execution of the query. You can override this limit at the query level or the session level. | 0 to disable dynamic priority adjustment (default), or a positive integer (unsigned 32-bit integer) for the number of seconds that is greater than 0. | | `MIN_PRIORITY` | Limit the minimum query priority. The current effective priority cannot be smaller than this value. | 0 (default) or any decimal value (double) greater than 0. | | `MAX_PRIORITY` | Limit the maximum query priority. The current effective priority cannot be greater than this value. | -1 (default) that indicates infinity or any decimal value (double) greater than 0. | | `STATEMENT_TEXT` | The system uses the specified service class for any queries that match the specified text pattern.
The system uses service classes with statement text before any service classes that lack statement text.
If there are multiple service classes with statement text available, the system attempts to match them in alphabetical order. This matching allows you to set up service classes so that the system can assign certain query types automatically to higher-priority service classes. | A string that uses either LIKE or REGEX pattern matching.
For example, `STATEMENT_TEXT REGEX ".*my_table.*\|.*your_table.*"` would match any queries that include the strings `my_table` or `your_table`. | | `LOW_LATENCY` | Optional settings that enable you to manage low-latency service classes. | Boolean `true` or `false`, or a Key-Value setting map. | | `MEMORY_OPTIMAL_STRATEGY` | Direct the Ocient System to reduce query memory usage, even if the operation might increase execution time. Contact Ocient Support when you change this parameter. | Boolean `true` instructs the system to apply stricter scheduling and execution rules to minimize memory consumption and avoid excessive concurrent usage. Otherwise, setting this parameter to `false` instructs the system to disable such strategies, even if the service class default enables them. By default, this parameter is unset. If you leave this parameter unset, the system reverts to higher-level default behavior or other service class settings. | **Example** This example creates a service class named `sc_name`. ```sql SQL theme={null} CREATE SERVICE CLASS sc_name MAX_TEMP_DISK_USAGE = 80, MAX_ELAPSED_TIME = 100, MAX_CONCURRENT_QUERIES = 10, MAX_ROWS_RETURNED = 100, SCHEDULING_PRIORITY = 5.0, CACHE_MAX_BYTES = 1000, CACHE_MAX_TIME = 25, MAX_ELAPSED_TIME_FOR_CACHING = 50; ``` ### DROP SERVICE CLASS Remove an existing service class from the system. When dropping a service class, you can use the `FORCE` flag to drop a service class and unset it from any dependent groups. If `FORCE` is not used, the function throws an error if there are dependent groups. **Syntax** ```sql SQL theme={null} DROP SERVICE CLASS sc_name [ IF EXISTS ] [ FORCE ] ``` | **Parameter** | **Data Type** | **Description** | | ------------- | ------------- | ------------------------------------ | | `sc_name` | string | The identifier of the service class. | **Example** ```sql SQL theme={null} DROP SERVICE CLASS "my_sc"; ``` ### ALTER SERVICE CLASS #### ALTER SERVICE CLASS RENAME Rename a service class. **Syntax** ```sql SQL theme={null} ALTER SERVICE CLASS [ IF EXISTS ] sc_name RENAME TO new_name ``` | **Parameter** | **Data Type** | **Description** | | ------------- | ------------- | --------------------------------------------------- | | `sc_name` | string | The old identifier of the service class. | | `new_name` | string | The new identifier for the `sc_name` service class. | **Example** ```sql SQL theme={null} ALTER SERVICE CLASS "my_sc" RENAME TO "my_new_sc"; ``` #### ALTER SERVICE CLASS SET Alter a service class with new parameter definitions by using the `SET` keyword. **Syntax** ```sql SQL theme={null} ALTER SERVICE CLASS [ IF EXISTS ] sc_name SET [, ...] ::= MAX_TEMP_DISK_USAGE [=] percentage | MAX_ELAPSED_TIME [=] time | MAX_CONCURRENT_QUERIES [=] queries | MAX_ROWS_RETURNED [=] rows | SCHEDULING_PRIORITY [=] priority | CACHE_MAX_BYTES [=] cache_max_bytes | CACHE_MAX_TIME [=] cache_max_time | MAX_ELAPSED_TIME_FOR_CACHING [=] max_elapsed_time_for_caching | MAX_COLUMNS_IN_RESULT_SET [=] max_columns_in_result_set | PRIORITY_ADJUSTMENT_FACTOR [=] priority_adjustment_factor | PRIORITY_ADJUSTMENT_TIME [=] priority_adjustment_time | MIN_PRIORITY [=] priority | MAX_PRIORITY [=] priority | STATEMENT_TEXT { LIKE | REGEX } [=] statement_text_pattern ``` | **Parameter** | **Data Type** | **Description** | | ------------- | ------------- | ------------------------------------ | | `sc_name` | string | The identifier of the service class. | For the service class definition parameters ``, see [Define Service Classes](#define-service-classes-\). **Examples** This example changes the value of `MAX_ROWS_RETURNED` to `51` on the service class named `sc_name`. ```sql SQL theme={null} ALTER SERVICE CLASS "sc_name" SET MAX_ROWS_RETURNED = 51; ``` This example makes dynamic priority adjustments at the service class level for a priority adjustment time of 15 seconds, priority adjustment factor of 0.75, minimum priority of 2.0, and maximum priority of 5.0. ```sql SQL theme={null} ALTER SERVICE CLASS "sc_name" SET PRIORITY_ADJUSTMENT_TIME = 15; ALTER SERVICE CLASS "sc_name" SET PRIORITY_ADJUSTMENT_FACTOR = 0.75; ALTER SERVICE CLASS "sc_name" SET MIN_PRIORITY = 2.0; ALTER SERVICE CLASS "sc_name" SET MAX_PRIORITY = 5.0; ``` This example sets the service class `sc_name` to handle queries matching the regular expression `.*my_table.*`. ```sql SQL theme={null} ALTER SERVICE CLASS "sc_name" SET STATEMENT_TEXT REGEX ".*my_table.*"; ``` #### ALTER SERVICE CLASS RESET Alter a service class by restoring default values for one or more specified settings. **Syntax** ```sql SQL theme={null} ALTER SERVICE CLASS [ IF EXISTS ] sc_name RESET [, ...] ::= NAME [=] name | MAX_TEMP_DISK_USAGE [=] percentage | MAX_ELAPSED_TIME [=] time | MAX_CONCURRENT_QUERIES [=] queries | MAX_ROWS_RETURNED [=] rows | SCHEDULING_PRIORITY [=] priority | CACHE_MAX_BYTES [=] cache max bytes | CACHE_MAX_TIME [=] cache max time | MAX_ELAPSED_TIME_FOR_CACHING [=] max elapsed time for caching | MAX_COLUMNS_IN_RESULT_SET [=] max columns in result set | PRIORITY_ADJUSTMENT_FACTOR [=] priority adjustment factor | PRIORITY_ADJUSTMENT_TIME [=] priority adjustment time | MIN_PRIORITY [=] priority | MAX_PRIORITY [=] priority | STATEMENT_TEXT { LIKE | REGEX } [=] statement_text_pattern ``` | **Parameter** | **Data Type** | **Description** | | ------------- | ------------- | ------------------------------------ | | `sc_name` | string | The identifier of the service class. | For descriptions of the `` parameters, see [Defining Service Classes](#define-service-classes-\). **Example** This example resets the value of `MAX_ROWS_RETURNED` on the service class named `sc_name`. ```sql SQL theme={null} ALTER SERVICE CLASS "sc_name" RESET MAX_ROWS_RETURNED; ``` ### ALTER QUERY Set the priority of a SQL query that is running. This priority takes effect immediately. The priority must remain within service class priority limits. The Ocient System overrides other preset priorities for this query, including any existing dynamic priority adjustments. **Syntax** ```sql SQL theme={null} ALTER QUERY query_id SET PRIORITY [=] priority ``` | **Parameter** | **Data Type** | **Description** | | ------------- | ------------- | ------------------------------------------------ | | `query_id` | numeric | The identifier of the SQL query that is running. | | `priority` | numeric | The priority of the specified SQL query. | **Example** This example sets the priority of the SQL query `34566` to `2`. ```sql SQL theme={null} ALTER QUERY 34566 SET PRIORITY 2; ``` ## Related Links [Manage Users, Groups, and Roles](/manage-users-groups-and-roles) [Data Control Language (DCL) Statement Reference](/data-control-language-dcl-statement-reference) [Database Password Security Settings](/database-password-security-settings)