Users, Groups, and Service Classes

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' 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 ( \<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 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 \[, ] 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 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 users cannot change their own state syntax alter user \<user name> { 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 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 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' 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 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 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 \[, ] 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 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 \[, ] 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 "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 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 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 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 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> 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 docid\ kuzemuw8ipvp0dzq 5dea value numeric an integer to represent one of the security settings for details about this value, see database password security settings docid\ kuzemuw8ipvp0dzq 5dea 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 | 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 ( \<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 docid\ f4egzuqnhpxncfl39bm2j for more details parameter description value max temp disk usage limit 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 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 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 ] parameter data type description sc name string the identifier of the service class 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 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 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 parameter data type description sc name string the identifier of the service class for the service class definition parameters \<service class definition> , see users, groups, and service classes docid\ vjbutywkymtamgzr6bru3 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 parameter data type description sc name string the identifier of the service class for descriptions of the \<service class definition> parameters, see users, groups, and service classes docid\ vjbutywkymtamgzr6bru3 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 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 alter query 34566 set priority 2; related links manage users, groups, and roles docid\ wkvhkkgw5oa1l6atnnlra data control language (dcl) statement reference docid\ cq7pzjpzxsakbbsqsumai database password security settings docid\ kuzemuw8ipvp0dzq 5dea