NETEZZA - CREATE USER command to create a new user account

 https://dwgeek.com/netezza-create-user-command-examples.html/

CREATE USER

Use the CREATE USER command to create a new user account.

Syntax

Syntax for creating a user:

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

Where <clause> represents one of:

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>…])}

Where <seclabel> represents:

[<level>]:<category>[,<category>…]:[<cohort>[,<cohort>…]

Where <access_time> represents:

DAY { ALL | <day>[,<day>…] } [ START <time> END <time> ]

Inputs

The command takes the following inputs:

Table 1. CREATE USER inputs

Input	Description
<username>	The name of the user account to be created. This name must be unique among global objects, that is, it cannot be shared by another user, or by any database, group, or scheduler rule.
PASSWORD	The password for this account. Specify NULL to create a user with no password. NULL is the default. 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. A user who has privileges to access the _t_user table can find all users with null passwords by running the following command: SELECT * FROM _t_user WHERE passwd is null
EXPIRE PASSWORD	Forces 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	The user ID to be associated with the new user account. The default is the larger of: 100 The currently highest user ID plus one
IN GROUP	The user group or groups to which the user is to be added as a new member.
IN RESOURCEGROUP	The resource group to which the user is to be assigned.
VALID UNTIL	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	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 (this is the default)
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 (this is the default)
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 (this is the default) 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	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 (this is the default) If the setting is 0, a limit might still be imposed by a group of which the user is a member. If the user is a member of several groups, the minimum setting applies.
SECURITY LABEL	The security label of the user. The default is PUBLIC::. 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 only if the user is connected to a database for which COLLECT HISTORY is set to ON. OFF History is not collected for the user. DEFAULT History is collected for the user only if the user is connected to a database for which COLLECT HISTORY is set to ON and if 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.

Output

The CREATE USER command has the following output:

Table 2. CREATE USER output

Output	Description
CREATE USER	The command completed successfully.

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 database or schema.
  • Your account has the Create User privilege
• 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.

• Create a user account with a password that must be changed when the user logs on for the first time:

  MYDB.SCH1(USER)=> CREATE USER david WITH 
    PASSWORD 'jw8s0F4' EXPIRE PASSWORD;

• Create a user account with a password that expires after 60 days:

  MYDB.SCH1(USER)=> CREATE USER sales_usr WITH 
    PASSWORD 'Temp123!' PASSWORDEXPIRY 60;

• Create a user account that expires at 1 PM on January 1, 2003:

  MYDB.SCH1(USER)=> CREATE USER miriam VALID
    UNTIL 'Jan 1 2003 13:00';

• Create a user account with an audit category:

  CREATE USER bob WITH AUDIT CATEGORY top;

• Create a user account that can start a session only Monday to Friday between 9:00 and 13:00 and Wednesday between 14:00 and 17:00:

  CREATE USER bob WITH ACCESS TIME (2,3,4,5,6 START 9:00 
    END 13:00, 4 START 14:00 END 17:00);