ALTER USER

Use the ALTER USER command to modify a user account. Any attributes that you do not specify remain unchanged. For example, if you do not specify an expiration time, the previously set expiration time continues to apply.

Use the ALTER USER command to set or change the attributes of a user account:

Set or change a password.
Set an expiration time for the password or for the account itself.
Set session timeout, query timeout, and rowset limits.
Add a user to a user group. (The ALTER USER command cannot be used to remove a user from a group; to do that, you must use the ALTER GROUP command.)
Unlock an account after the maximum number of logon attempts is exceeded.

Syntax

Syntax for modifying a user account:

ALTER USER <username> [WITH] <clause> [<clause>…]

Where <clause> represents:

RENAME TO <newname>
RESET ACCOUNT
PASSWORD {'<pw>'|NULL} |
EXPIRE PASSWORD
PASSWORDEXPIRY <days>
AUTH {LOCAL|DEFAULT}
SYSID <userid> |
IN GROUP <usergrp>[,<usergrp>…] |
IN RESOURCEGROUP <rsg> 
VALID UNTIL '<valid_date>' |
DEFPRIORITY {CRITICAL|HIGH|NORMAL|LOW|NONE} |
MAXPRIORITY {CRITICAL|HIGH|NORMAL|LOW|NONE} |
ROWSETLIMIT <rslimit> |
SESSIONTIMEOUT <sessiontimeout> |
QUERYTIMEOUT <querytimeout> |
CONCURRENT SESSIONS <concsessions> |
SECURITY LABEL {'<seclabel>|PUBLIC::'} |
AUDIT CATEGORY {NONE|'<category>[,<category>…]'}
COLLECT HISTORY {ON|OFF|DEFAULT} |
ALLOW CROSS JOIN {TRUE|FALSE|NULL} |
ACCESS TIME {ALL|DEFAULT|(<access-time>[,<access-time>…])}

Inputs

The ALTER USER command takes the following inputs:

Table 1. ALTER USER inputs
Input	Description
<username>	The name of the user account to be modified.
RENAME TO <newname>	The new name for this user account.
RESET ACCOUNT	Unlock the account after the maximum number of logons was exceeded.
PASSWORD <pw>	The new password for this account. Specify NULL if the user account is to have no password. You can specify a password when you alter a database user account, but the password is used only for LOCAL authentication. When using LOCAL authentication, the user must have a password to log on; a null password is not allowed. If you change authentication from LDAP or Kerberos to LOCAL, use the ALTER USER command to specify a password for the user.
EXPIRE PASSWORD	Force the user to change the password after logging in for the first time.
PASSWORDEXPIRY	The number of days that the password is valid. If you do not specify the option, or if you specify a value of 0, the password does not expire. After the password expires, the database displays the message `Restricted Session. User password has expired` the next time that the user logs in to the database. The user must change the account password such as by using the ALTER USER command to regain full capabilities. The expiration is used for locally authenticated accounts. If you do not set an expiration for the user account, the account password could expire if there is a expiration for a database group in which the user is a member, or if the system-wide expiration is set.
AUTH	The authentication type to be used by the user: LOCAL LOCAL authentication. DEFAULT The authentication type (LOCAL, LDAP, or KERBEROS) that is specified for the connection.
SYSID <userid>	The user ID to be associated with the new user account.
IN GROUP <usergrp>	The user group or groups to which the user is to be added as a member. (The ALTER USER command cannot be used to remove a user from a group; to do that, you must use the ALTER GROUP command.)
IN RESOURCEGROUP <rsg>	The resource group to which the user is to be assigned.
VALID UNTIL '<valid_date>'	The date (and, optionally, the time) when this user account expires.
DEFPRIORITY	The default priority for queries submitted by the user.
MAXPRIORITY	The maximum priority for a query submitted by the user.
ROWSETLIMIT <rslimit>	The maximum number of rows that a query made by the user can return. The value can be: A number in the range 1 - 2,147,483,647 0 for an unlimited number of rows.
SESSIONTIMEOUT <sessiontimeout>	The number of minutes that a session can be idle before the system terminates it. The value can be: A number in the range 1 - 35,791,394 0 for an unlimited number of minutes.
QUERYTIMEOUT <querytimeout>	The number of minutes that a query can run before the system notifies the administrator. The value can be: A number in the range 1 - 35,791,394 0 for an unlimited number of minutes. For the administrator to receive the issued message, the RunAwayQuery event rule must be enabled. This rule is described in the IBM® Netezza® System Administrator’s Guide.
CONCURRENT SESSIONS <concsessions>	The maximum number of concurrent sessions this user can have. The value can be: A number in the range 1 - 35,791,394 0 for an unlimited number of sessions. A value of 0 means no limit to the number of concurrent sessions, unless a limit is imposed by a group. In that case, the minimum limit of concurrent sessions across all such groups is used.
SECURITY LABEL <seclabel>	The security label of the user. The label must be specified in single quotation marks so that identifiers in the label do not conflict with other user clauses.
AUDIT CATEGORY	One or more audit categories that are to be added to the security label during audit logging for the user.
COLLECT HISTORY	Whether the system is to collect history data for the sessions of this user: ON History is collected for this user when this user is connected to a database for which COLLECT HISTORY is set to ON. OFF History is not collected for this user. DEFAULT History is collected for this user when this user is connected to a database for which COLLECT HISTORY is set to ON and when one of the following criteria apply: This user is not a member of any user group. All the user groups of which this user is a member have COLLECT HISTORY set to DEFAULT. This user is a member of at least one user group that has COLLECT HISTORY set to ON.
ALLOW CROSS JOIN	Whether the user can carry out explicit cross joins: TRUE The user can carry out explicit cross joins. FALSE The user cannot carry out explicit cross joins. NULL The user can carry out explicit cross joins unless the user is a member of one or more user groups for which ALLOW CROSS JOIN is set to FALSE.
ACCESS TIME	When the user is allowed to start sessions on the system: ALL The user can start sessions at any time and on any day. DEFAULT The combination of the access time settings of the groups of which the user is a member determine when the user can start sessions. For example, if a user is a member of two groups, one of which specifies ACCESS TIME '1,2,3' and the other of which specifies ACCESS TIME '5,6,7', the user can start a session only on Wednesday (day 4). <access-time> Each access time subclause specifies one or more days of the week and one time interval during which the user can start a session. Each day is represented by an SQL day number (1 = Sunday, 7 = Saturday, etc.). The keyword ALL is equivalent to specifying days 1,2,3,4,5,6,7. An access time subclause optionally contains one time bound. If no time bound is specified, then the group can create a session at any time on the specified day.

Outputs

The ALTER USER command produces the following outputs:

Table 2. ALTER USER outputs
Output	Description
`ALTER USER`	The user account was successfully altered.
`ERROR: ALTER USER: user 'username' does not exist`	The database does not recognize the user.
`ERROR: permission denied.`	You must have Manage Security privilege to set one or more of the specified options.
`ERROR: invalid security label.`	The security label is either incorrectly formatted or refers to a security level, cohort, or category that does not exist.
`ERROR: object <category_name> not found.`	The indicated audit category does not exist.
`ERROR: ALTER USER: User password cannot be repeated within the last '<num>' passwords`	The password cannot be a string that used in the last <num> password changes. The system keeps a history of the passwords and enforces a policy for how frequently a password can be reused. For more information about password and security controls, see the IBM Netezza System Administrator’s Guide

Privileges

To issue this command:

If you specify the SECURITY LABEL, AUDIT CATEGORY, or COLLECT HISTORY options, you must have the Manage Security privilege.
If you specify any of the other options, you must satisfy at least one of the following criteria:
- You are the admin user.
- You are the owner of the user account.
- Your account has the Alter privilege for the user account or for the User object class.
If you specify the IN GROUP option, you must also have List access to the corresponding group or to the Group object class.

Usage

The following provides sample usage.

Change a user password:

MYDB.SCH1(USER)=> ALTER USER davide WITH PASSWORD 'hu8jmn3';

Set the user account expiration:

MYDB.SCH1(USER)=> ALTER USER manuel WITH VALID UNTIL 'Jan 31 2030';

Configure the account password to expire after 60 days:

MYDB.SCH1(USER)=> ALTER USER sales_usr WITH 
  PASSWORDEXPIRY 60;

Set the user rowset limits:

MYDB.SCH1(USER)=> ALTER USER mark WITH ROWSETLIMIT 10000;