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

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'

Define User Parameters (<user_definition>)

You can apply the following optional parameters when creating a new user object.

Example

This example creates a new user named jmack@test-database with the password pass1234.

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.

DROP USER [ IF EXISTS ] fully_qualified_username [,...]

Example

This example removes a user named jmack@test-database.

DROP USER "jmack@test-database";

This example drops multiple users.

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

Syntax

ALTER USER <user_name> { ENABLE | DISABLE | PASSWORD_EXPIRED }

Examples

Enable a User

Enable a user named example_user.

ALTER USER example_user ENABLE;

Disable a User

Disable a user named example_user.

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.

ALTER USER example_user PASSWORD_EXPIRED;

ALTER USER SET

Changes the password of an existing user.

Syntax

ALTER USER [ IF EXISTS ] fully_qualified_username SET PASSWORD = password_string

Example

This example changes the password of user "jmack@test-database" to '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.

CREATE GROUP [ IF NOT EXISTS ] group_name

Example

This example creates a new group named 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

DROP GROUP [ IF EXISTS ] group_name [, ...]

Example

This example removes a group named group1.

DROP GROUP "group1";

This example removes multiple groups.

DROP GROUP "group1", "group2";

ALTER GROUP

ALTER GROUP USER

ALTER GROUP adds or removes users from the group using the USER keyword.

Syntax

ALTER GROUP [ IF EXISTS ] groupname { ADD | DROP } USER username [, ...]

Example

This example adds user1 to an existing group named group1.

ALTER GROUP "group1" ADD USER "user1";

ALTER GROUP RENAME

ALTER GROUP renames an existing group by including the RENAME TO keyword.

Syntax

ALTER GROUP [ IF EXISTS ] group_name RENAME TO new_group_name

Example

This example renames an existing group group1 to group2.

ALTER GROUP "group1" RENAME TO "group2";

ALTER GROUP SET SERVICE CLASS

Set a service class for the specified group.

Syntax

ALTER GROUP group_name SET SERVICE CLASS sc_name

Example

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 <security_setting> with the security setting and <value> with the value.

Syntax

ALTER GROUP group_name ALTER SECURITY <security_setting> [=] <value>

• password_complexity_level
• password_no_repeat_count
• password_lifetime_days
• password_invalid_attempt_limit
  For details about these values, see 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. |

Example

Set the password lifetime to 10 days for the group example_group.

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

CREATE SERVICE CLASS [ IF NOT EXISTS ] sc_name [ <service_class_definition> [, ...] ]
<service_class_definition> ::=
    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

Define 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 you specify.

See the Workload Management Walkthrough for more details.

Example

This example creates a service class named sc_name.

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

DROP SERVICE CLASS sc_name [ IF EXISTS ] [ FORCE ]

Example

DROP SERVICE CLASS "my_sc";

ALTER SERVICE CLASS

ALTER SERVICE CLASS RENAME

Rename a service class.

Syntax

ALTER SERVICE CLASS [ IF EXISTS ] sc_name RENAME TO new_name

Example

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

ALTER SERVICE CLASS [ IF EXISTS ] sc_name
   SET <service_class_definition> [, ...]
<service_class_definition> ::=
  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

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.

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.

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.*.

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

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 |
  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

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.

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

ALTER QUERY query_id SET PRIORITY [=] priority

Example

This example sets the priority of the SQL query 34566 to 2.

ALTER QUERY 34566 SET PRIORITY 2;

Related Links

Manage Users, Groups, and Roles

Data Control Language (DCL) Statement Reference

Database Password Security Settings