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.
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
CREATE USER [ IF NOT EXISTS ] fully_qualified_username <user_definition> [, ...] ]

<user_definition> ::=
    PASSWORD [=] 'password_string' |
    [ FIRST_NAME [=] 'first_name_string' ] |
    [ LAST_NAME [=] 'last_name_string' ] | 
    [ EMAIL [=] 'email_string'
CREATE USER [ IF NOT EXISTS ] fully_qualified_username <user_definition> [, ...] ]

<user_definition> ::=
    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 . 
﻿
A 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.
﻿
﻿Defining User Parameters (<user_definition>)
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
CREATE USER 
    "jmack@test-database" PASSWORD = 'pass1234', 
    FIRST_NAME = 'Johnny', 
    LAST_NAME = 'Mack';
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
DROP USER [ IF EXISTS ] fully_qualified_username
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. 
﻿
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.
SQL
DROP USER "jmack@test-database";
DROP USER "jmack@test-database";
﻿
ALTER USER
﻿Changes the ﻿password of﻿ an existing user. The fully_qualified_username identifier should be enclosed in double quotes.
Syntax
SQL
ALTER USER [ IF EXISTS ] "fully_qualified_username" SET PASSWORD = password_string
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. 
﻿
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'.
SQL
ALTER USER "jmack@test-database" SET PASSWORD = 'newpass';
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
CREATE GROUP [ IF NOT EXISTS ] group_name
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
CREATE GROUP group1;
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
DROP GROUP [ IF EXISTS ] group_name
DROP GROUP [ IF EXISTS ] group_name
﻿
Parameter
Data Type
Description
group_name
string
The identifier of the group to drop. 
Example
This example removes a group named group1.
SQL
DROP GROUP group1;
DROP GROUP group1;
﻿
ALTER GROUP
ALTER GROUP USER
ALTER GROUP adds or removes users from the group by using the USER keyword.
Syntax
SQL
ALTER GROUP [ IF EXISTS ] groupname { ADD | DROP } USER username [, ...]
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
ALTER GROUP group1 ADD USER user1;
ALTER GROUP group1 ADD USER user1;
﻿
ALTER GROUP RENAME
ALTER GROUP renames an existing group by including the RENAME TO keyword.
Syntax
SQL
ALTER GROUP [ IF EXISTS ] group_name RENAME TO new_group_name
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
ALTER GROUP group1 RENAME to group2;
ALTER GROUP group1 RENAME to group2;
﻿
﻿ALTER GROUP SET SERVICE CLASS﻿
Set a service class for the specified group. 
Syntax
SQL
ALTER GROUP group_name SET SERVICE CLASS sc_name
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
ALTER GROUP "wlm_test" SET SERVICE CLASS high_priority;
ALTER GROUP "wlm_test" SET SERVICE CLASS high_priority;
﻿
SERVICE CLASS
CREATE SERVICE CLASS
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.
Syntax
SQL
CREATE SERVICE CLASS [ IF NOT EXISTS ] sc_name [ <service_class_definition> [, ...] ]

<service_class_definition> ::=
  NAME [=] name |
  MAX_TEMP_DISK_USAGE [=] percentage |
  MAX_ELAPSED_TIME [=] time |
  MAX_CONCURRENT_QUERIES [=] queries |
  MAX_ROWS_RETURNED [=] rows |
  SCHEDULING_PRIORITY [=] priority |
  PSO_THRESHOLD [=] pso |
  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
CREATE SERVICE CLASS [ IF NOT EXISTS ] sc_name [ <service_class_definition> [, ...] ]

<service_class_definition> ::=
  NAME [=] name |
  MAX_TEMP_DISK_USAGE [=] percentage |
  MAX_ELAPSED_TIME [=] time |
  MAX_CONCURRENT_QUERIES [=] queries |
  MAX_ROWS_RETURNED [=] rows |
  SCHEDULING_PRIORITY [=] priority |
  PSO_THRESHOLD [=] pso |
  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
﻿
Parameter
Data Type
Description
sc_name
string
The identifier of the service class. 
Defining Service Classes ( <service_class_definition> )
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.
﻿
Example
This example creates a service class called sc_name.
SQL
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, 
    PSO_THRESHOLD = -1, 
    CACHE_MAX_BYTES = 1000, 
    CACHE_MAX_TIME = 25, 
    MAX_ELAPSED_TIME_FOR_CACHING = 50;
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, 
    PSO_THRESHOLD = -1, 
    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
DROP SERVICE CLASS sc_name [ IF EXISTS ] [ FORCE ]
DROP SERVICE CLASS sc_name [ IF EXISTS ] [ FORCE ]
﻿
Parameter
Data Type
Description
sc_name
string
The identifier of the service class. 
Example
SQL
DROP SERVICE CLASS my_sc;
DROP SERVICE CLASS my_sc;
﻿
ALTER SERVICE CLASS
ALTER SERVICE CLASS RENAME
Rename a service class. 
Syntax
SQL
ALTER SERVICE CLASS [ IF EXISTS ] sc_name RENAME TO new_name
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
ALTER SERVICE CLASS my_sc RENAME TO my_new_sc; 
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
ALTER SERVICE CLASS [ IF EXISTS ] sc_name SET <service_class_definition> [, ...]

<service_class_definition> ::=
  NAME [=] name |
  MAX_TEMP_DISK_USAGE [=] percentage |
  MAX_ELAPSED_TIME [=] time |
  MAX_CONCURRENT_QUERIES [=] queries |
  MAX_ROWS_RETURNED [=] rows |
  SCHEDULING_PRIORITY [=] priority |
  PSO_THRESHOLD [=] pso |
  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
ALTER SERVICE CLASS [ IF EXISTS ] sc_name SET <service_class_definition> [, ...]

<service_class_definition> ::=
  NAME [=] name |
  MAX_TEMP_DISK_USAGE [=] percentage |
  MAX_ELAPSED_TIME [=] time |
  MAX_CONCURRENT_QUERIES [=] queries |
  MAX_ROWS_RETURNED [=] rows |
  SCHEDULING_PRIORITY [=] priority |
  PSO_THRESHOLD [=] pso |
  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
﻿
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﻿.
Examples
This example changes the value of MAX_ROWS_RETURNED to 51 on service class named sc_name.
SQL
ALTER SERVICE CLASS sc_name SET MAX_ROWS_RETURNED = 51;
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
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;
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;
﻿
ALTER SERVICE CLASS RESET
Alter a service class by restoring default values for one or more specified settings. 
Syntax
SQL
ALTER SERVICE CLASS [ IF EXISTS ] sc_name RESET <service_class_definition> [, ...]

<service_class_definition> ::=
  NAME [=] name |
  MAX_TEMP_DISK_USAGE [=] percentage |
  MAX_ELAPSED_TIME [=] time |
  MAX_CONCURRENT_QUERIES [=] queries |
  MAX_ROWS_RETURNED [=] rows |
  SCHEDULING_PRIORITY [=] priority |
  PSO_THRESHOLD [=] pso |
  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
ALTER SERVICE CLASS [ IF EXISTS ] sc_name RESET <service_class_definition> [, ...]

<service_class_definition> ::=
  NAME [=] name |
  MAX_TEMP_DISK_USAGE [=] percentage |
  MAX_ELAPSED_TIME [=] time |
  MAX_CONCURRENT_QUERIES [=] queries |
  MAX_ROWS_RETURNED [=] rows |
  SCHEDULING_PRIORITY [=] priority |
  PSO_THRESHOLD [=] pso |
  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
﻿
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 service class named sc_name.
SQL
ALTER SERVICE CLASS sc_name RESET MAX_ROWS_RETURNED;
ALTER SERVICE CLASS sc_name RESET MAX_ROWS_RETURNED;
﻿
﻿

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 . A FQUN can reference a user associated with a different database than the one you are connected to. For FQUN examples, see the table below.

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.

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.

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.

Parameter	Data Type	Description
group_name	string	A group name unique from any existing group in the database.

Parameter	Data Type	Description
group_name	string	The identifier of the group to drop.

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.

Parameter	Data Type	Description
group_name	string	The identifier of the group to alter.
new_group_name	string	The new name for the group.

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.

Parameter	Data Type	Description
sc_name	string	The identifier of the service class.

Parameter	Data Type	Description
sc_name	string	The identifier of the service class.

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.

Parameter	Data Type	Description
sc_name	string	The identifier of the service class.

Parameter	Data Type	Description
sc_name	string	The identifier of the service class.

Updated 11 Jan 2024

Did this page help you?

Rebuild Tasks and Check Data

Data Integrity and Storage

Users, Groups, and Service Classes

USER

CREATE USER

﻿Defining User Parameters (<user_definition>)

DROP USER

ALTER USER

GROUP

CREATE GROUP

DROP GROUP

ALTER GROUP

ALTER GROUP USER

ALTER GROUP RENAME

﻿ALTER GROUP SET SERVICE CLASS﻿

SERVICE CLASS

CREATE SERVICE CLASS

Defining Service Classes ( <service_class_definition> )

DROP SERVICE CLASS

ALTER SERVICE CLASS

ALTER SERVICE CLASS RENAME

ALTER SERVICE CLASS SET

ALTER SERVICE CLASS RESET

Defining User Parameters (<user_definition>)

ALTER GROUP SET SERVICE CLASS