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' 234,100,295 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type if you specify only a user name without a database name , ocient assumes the user is associated with the database of the active connection 82,108,143,250 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type define user parameters ( \<user definition> ) you can apply the following optional parameters when creating a new user object 178,100,328 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 \[, ] 234,100,311 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 } 148,100,409 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 241,100,301 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 148,100,409 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 \[, ] 148,100,409 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 \[, ] 151,100,409 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 153,100,409 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 155,100,408 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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> 188,125,291 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 151,100,409 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 docid\ sf8ojgyg8bqcetvtpj87c for more details 257,233,155,171,171,100,100,100,100,100 true unhandled content type left unhandled content type left unhandled content type unhandled content type left unhandled content type left unhandled content type unhandled content type left unhandled content type left unhandled content type unhandled content type left unhandled content type left unhandled content type unhandled content type left unhandled content type left unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type left unhandled content type left unhandled content type unhandled content type left unhandled content type left unhandled content type unhandled content type left unhandled content type left unhandled content type unhandled content type left unhandled content type left unhandled content type unhandled content type left unhandled content type left unhandled content type unhandled content type left unhandled content type left unhandled content type unhandled content type left unhandled content type left unhandled content type unhandled content type left unhandled content type left unhandled content type unhandled content type left unhandled content type left unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 ] 151,100,409 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 150,100,409 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 150,100,409 2 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type for the service class definition parameters \<service class definition> , see docid\ jygpvmqcl3xgpbvftt4qp 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 154,100,409 2 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type for descriptions of the \<service class definition> parameters, see docid\ jygpvmqcl3xgpbvftt4qp 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 150,100,409 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type example this example sets the priority of the sql query 34566 to 2 alter query 34566 set priority 2; related links docid\ k rpf30u 1c3jfq 7ahiu docid\ f55ngxtki0f7kkmyatvug docid\ jfltms91v2bfledppksy8