Users, Groups, and Service Classes
Users, Groups, and Service Classes are managed using DDL commands 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.
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
Parameter | Data Type | Description |
|---|---|---|
fully_qualified_username | string |
If you specify only a user_name without a database_name, Ocient assumes the user is associated with the database of the active connection.
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.
DROP USER removes an existing user from the system.
To remove a user, you must possess the DROP USER privilege for the user.
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. A 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.
This example drops multiple users.
Changes the password of an existing user. The fully_qualified_username identifier should be enclosed in double quotes.
Syntax
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. A 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'.
CREATE GROUP creates a new group. The name must be distinct from the name of any existing group in the database.
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.
DROP GROUP removes an existing group from the system.
To remove a group, you must possess the DROP GROUP privilege for the group.
Syntax
Parameter | Data Type | Description |
|---|---|---|
group_name | string | The identifier of the group to drop. You can drop multiple groups by specifying additional group names and separating each with commas. |
Example
This example removes a group named group1.
This example removes multiple groups.
ALTER GROUP adds or removes users from the group by using the USER keyword.
Syntax
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.
ALTER GROUP renames an existing group by including the RENAME TO keyword.
Syntax
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.
Set a service class for the specified group.
Syntax
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
A service class defines a set of limits on various system parameters.
The Ocient System applies service classes on a per-group basis. 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 in alphabetical order by service class name.
Syntax
Parameter | Data Type | Description |
|---|---|---|
sc_name | string | The identifier of the service class. |
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 that you specify.
See the Workload Management Walkthrough for more detail.
For details on using the CACHE_MAX_BYTES and CACHE_MAX_TIME parameters, see Result Set Caching.
For details on using the LOW_LATENCY parameter, see Low-Latency Settings.
Example
This example creates a service class named sc_name.
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
Parameter | Data Type | Description |
|---|---|---|
sc_name | string | The identifier of the service class. |
Example
Rename a service class.
Syntax
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
Alter a service class with new parameter definitions by using the SET keyword.
Syntax
Parameter | Data Type | Description |
|---|---|---|
sc_name | string | The identifier of the service class. |
For the service class definition parameters <service_class_definition>, see Define Service Classes.
Examples
This example changes the value of MAX_ROWS_RETURNED to 51 on the service class named sc_name.
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.
This example sets the service class sc_name to handle any queries that match the regular expression .*my_table.*.
Alter a service class by restoring default values for one or more specified settings.
Syntax
Parameter | Data Type | Description |
|---|---|---|
sc_name | string | The identifier of the service class. |
For descriptions of the <service_class_definition> parameters, see Defining Service Classes.
Example
This example resets the value of MAX_ROWS_RETURNED on the service class named sc_name.
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
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.