MODIFY PROFILE Syntax Elements - Teradata Vantage

profile_name

Name of the profile to be modified.

A profile name must be unique among profiles on a Vantage system.

ACCOUNT

Add one or more accounts to any accounts already specified for the profile.

account_string

Each position in an account string has a specific meaning and format requirement. See Creating Account Strings.

Each account_string must follow Vantage object naming rules. See Object Names.

You must separate each entry in a list of account strings with a COMMA character and enclose the list with LEFT PARENTHESIS and RIGHT PARENTHESIS characters.

Accounts do not exist independently in the database. Accounts only exist as specified in user, database, and profile definitions. You can specify the same account string in multiple object definitions, if needed.

Account strings defined in the profile supersede the accounts defined in the user definitions of profile member users. Newly added accounts are immediately available.

The first account string specified for the profile is the default account for profile members.

If you do not specify an account string for a profile, the system defaults to the accounts specified for individual users. If a user has no account in either the user or profile definitions, the user inherits the first account string that is defined for the immediate owner of the user.

Users with multiple accounts can access a non-default account by specifying the account in a:

• Logon string.
• SET SESSION ACCOUNT statement.

For a user to specify an account in the logon string or in a SET SESSION ACCOUNT statement, the account must be assigned in the user or profile definition.

COMPUTE GROUP

Default compute cluster group for the profile.

DEFAULT MAP

You can specify an existing contiguous or sparse map as the default map for the profile.

You must have been granted the specified map unless the map is the same as the default map of the profile modifier.

map_name

Name of an existing contiguous or sparse map.

You cannot specify TD_DataDictonaryMap or TD_GlobalMap.

OVERRIDE ON ERROR

Use the default map if an error occurs when the MAP clause is specified for a CREATE TABLE or CREATE JOIN INDEX statement.

This is the default.

OVERRIDE NOT ON ERROR

Do not use the default map if an error occurs when the MAP clause is specified for a CREATE TABLE OR CREATE JOIN INDEX statement.

NULL

A default map is not associated with the database. You can specify NULL to remove the default map that is set for the profile.

DEFAULT STORAGE

You can specify default storage and an override option for a user or database.

If you omit this clause, the user has no default storage or override option.

NULL

User has no default storage or override option.

storage_name

Name of user default storage. The storage_name must exist.

OVERRIDE [NOT] ON ERROR

Determines what a CREATE TABLE statement does when its STORAGE clause that causes an error:

OVERRIDE ON ERROR (Default)	CREATE TABLE does the following and warns you: Uses the user default storage. Ignores clauses that are invalid for the user default storage. For example, if the default storage is NOS, CREATE TABLE ignores the PRIMARY INDEX clause.
OVERRIDE NOT ON ERROR	CREATE TABLE does not use user default storage and one or more errors occur.

DEFAULT DATABASE

The default database established each time a user with this profile logs onto Vantage. A change to the default database takes place at the next user logon.

Vantage returns an error if a profile member user tries to create or reference an object within a nonexistent database.

If the default database is NULL or is not defined in the profile assigned to a user, Vantage uses the setting defined for the individual user.

database_name

Name of the default database.

SPOOL

Maximum number of bytes allowed for spool files in profile_name. The default is null, which uses the setting defined for the individual user assigned to the profile.

Changes to spool space limits in a profile take effect immediately on submitting the MODIFY PROFILE request.

n

You can enter the number of bytes as an integer, decimal, or floating point value or as a constant expression whose evaluation determines the number of bytes. You can also enter the value using exponential notation. For example, you can write one thousand as either 1000 or 1E3.

n cannot exceed the spool space parameter in the profile of the creator. If no spool space is defined for that profile, then Vantage uses the spool space limit defined for the individual user-creator.

constant_expression

A constant expression is any SQL expression that does not make any column references. Specifying an appropriate constant expression for the SPOOL size of a database enables Vantage to assign an optimal quantity of SPOOL that scales to the size of your system.

When you specify a SPOOL size based on a constant expression, the assigned size does not automatically scale if, for example, you add more AMPs to your system.

BYTES

Optional keyword that redundantly specifies the unit for the amount of space allowed.

TEMPORARY

The number of bytes to allocate for global temporary table space.

If no temporary space is defined in the profile assigned to a user, Vantage uses the setting defined for the individual user. Temporary space is reserved before spool space for any user defined with this characteristic. Disk usage for a materialized global temporary table is charged to the temporary space allocation of the user who referenced the table.

The default is NULL, which uses the setting defined for the individual user. Changes to the temporary space allocation in a profile take effect immediately on submitting the MODIFY PROFILE request.

n

n can be an integer, a decimal value, or a floating point value.

n cannot exceed the temporary space parameter in the profile of the creator. If no temporary space limit is defined for that profile, then Vantage uses the temporary space limit defined for the individual user-creator.

n refers to bytes, whether or not the optional BYTES keyword is specified.

constant_expression

Any SQL expression that does not make any column references. Specifying an appropriate constant expression for the TEMPORARY space size of a user enables Vantage to assign an optimal quantity of TEMPORARY space that scales to the size of your system by allocating TEMPORARY space on a per AMP basis.

When you specify a TEMPORARY space size based on a constant expression, the assigned size does not automatically scale if, for example, you add more AMPs to your system.

BYTES

Optional keyword.

PASSWORD

If you specify the PASSWORD or PASSWORD ATTRIBUTES phrase, you must list one or more password control option and specify either a value or NULL (the default) for each.

Password security attributes in a modified profile take effect the next time a profile member user logs on after the modification.

Password controls only affect users authenticated by the database. Externally authenticated users are unaffected.

ATTRIBUTES

Keywords that introduces a set of password control attributes. Following are brief descriptions of the password controls.

attribute_name = value

If you specify a value in a profile for a password control, the value supersedes the global value for profile members.

attribute_name = NULL

If the value for an attribute is NULL or if you do not specify the PASSWORD or PASSWORD ATTRIBUTES phrase, the profile defaults to the global password control settings in DBC.SysSecDefaults.

EXPIRE=n

The number of days to elapse before the password expires.

A value of 0 for n indicates the password never expires.

NULL indicates that the EXPIRE option is not set for the profile.

You can specify any non-zero value to cause new users who are profile members to replace the temporary password specified in their user definitions with a permanent private password at first log on. Users must use the MODIFY USER statement to change their password.

MINCHAR=n

The minimum number of characters in a password string.

The valid range for n is 1-127 UNICODE characters.

NULL indicates that the MINCHAR option is not set for the profile.

MAXCHAR=n

The maximum number of characters in a password string.

The valid range for n is 1-127 UNICODE characters.

NULL indicates that the MAXCHAR option is not set for the profile.

DIGITS=c

Specifies whether at least one digit must appear in a password string.

• If c is N or n, digits are not permitted in a password string.
• If c is R or r, At least one digit is required in a password string.
• If c is Y or y, digits are optional in a password string.
• NULL indicates that the DIGITS option is not set for the profile.

SPECCHAR=c

Specifies whether certain characters or the user name are allowed, not allowed, or required in a user password string.

The value for SPECCHAR must be one of the single letter option codes shown in Working with the SPECCHAR Password Control, where the letter code you specify for the SPECCHAR option represents a unique set of possible SPECCHAR rules.

MAXLOGONATTEMPTS=n

Number of incorrect logon attempts allowed before locking the user from further attempts, where n is a value from 0 to 127.

A value of 0 for n indicates never to lock the user.

NULL indicates that the MAXLOGONATTEMPTS option is not set for the profile.

LOCKEDUSEREXPIRE=n

Number of minutes to elapse before unlocking a locked user.

• If n is 0, Vantage unlocks the user immediately.
• If n is -1, Vantage locks the user indefinitely.
• NULL indicates that the LOCKEDUSEREXPIRE option is not set for the profile.

REUSE=n

Number of days to elapse before a password can be reused.

A value of 0 for n allows the password to be reused immediately.

NULL indicates that the REUSE option is not set for the profile.

RESTRICTWORDS=c

Specifies whether certain words are restricted from use within a password string.

The valid values for cfollow.

• If c is Y or y, any words listed in DBC.PasswordRestrictions cannot be used in password strings.
• If c is N or n, use of words listed in DBC.PasswordRestrictions in password strings is allowed.
• NULL indicates that the RESTRICTWORDS option is not set for the profile.

QUERY_BAND

Modify the default query band defined in the profile. The name-value pairs you specify replace any existing query band settings. Changes to the profile query band of logged on users take effect on the next request.

You must enclose the set of name=value pairs for the query band with APOSTROPHE characters (').

The number of name=value pairs is limited to the maximum length of the string, which is 4,096 UNICODE characters, including pad characters.

QUERY_BAND

Keyword to introduce query band options you specify.

pair_name

Name component of a query band specification.

The maximum size for each pair_name is 128 UNICODE characters. For more information about database object names, see Object Names.

pair_value

Value component of a query band specification.

pair_value can be up to 256 UNICODE characters in length.

NULL

Removes the entire query band from the profile.

DEFAULT

The values are set as default values that can be overwritten using the SET QUERY_BAND statement for the session or transaction.

NOT DEFAULT

Overwriting the pairs in the profile query band is not permitted. Any pair in a SET QUERY_BAND statement matching a name in the profile query band is discarded from the query band.

IGNORE QUERY_BAND VALUES

You must enclose the set of name=value pairs for the query band with APOSTROPHE characters (').

The number of name=value pairs is limited to the maximum length of the string, which is 4,096 UNICODE characters, including pad characters.

pair_name=;

To embed an APOSTROPHE character in a pair name or value, type it twice. Otherwise, it is interpreted as a pair_name=pair_value string terminator.

If a profile has a query band that is defined as NOT DEFAULT, it is redundant to define the IGNORE QUERY_BAND VALUES with name-value pairs having the same names as defined in the profile query band. The NOT DEFAULT profile query band causes the SET QUERY_BAND statement to discard any name-value pair matching the names in the profile query band.

Changes to the profile IGNORE QUERY_BAND VALUES of logged on users do not affect existing query bands in the session. Subsequent SET QUERY_BAND statements in the session are validated according to the updated profile IGNORE QUERY_BAND VALUES.

IGNORE QUERY_BAND VALUES

Keywords to introduce query band options to ignore.

pair_name

Name component of a query band specification.

The maximum size for each pair_name is 128 UNICODE characters. For more information about database object names, see Object Names.

pair_value

Value component of a query band specification.

pair_value can be up to 256 UNICODE characters in length.

NULL

Removes the entire ignore query band setting from the profile.

TRANSFORM

Adds UDT transform groups to the profile or removes UDT transform groups from the profile.

You can specify up to 256 data_type-transform_group pairs for a profile.

You can only add, replace, or remove entire transform group settings. You cannot add or remove individual data_type-transform_group pairs.

After transform group settings are changed, the user must log out and log in again to use the new settings.

data_type

Name of the user-defined data type (UDT).

group_name

Name of transform group for the data type.

cost_profile_name

Name of cost profile to be associated with profile_name.

NULL

The default is NULL, meaning use the default system profile.

Optimizer cost profiles are not intended for use on production systems. The COST PROFILE parameter is for use only under the direction of Teradata Support personnel.

CONSTRAINT

Up to 6 hierarchical constraints and 2 non-hierarchical constraints can be assigned per profile.

Changes take effect at the next logon by profile members after the profile definition is modified.

row_level_security_constraint_name

Name of one or more row-level security constraints, each followed by a list of the hierarchical levels or non-hierarchical categories, valid for the constraint, which are being assigned to the profile_name.

level_name

List of hierarchical levels. Example of hierarchical level constraint assignment:

CONSTRAINT = Classification_Level (Secret, Unclassified DEFAULT)

DEFAULT

DEFAULT can be specified for 1 classification level from the specified list of levels for a hierarchical constraint. The specified level becomes the default value for the constraint when a session is established for the user to which the profile is assigned.

If you do not specify DEFAULT for any of the listed classification levels, then the first level listed becomes the default for the profile.

DEFAULT is not valid for non-hierarchical constraints.

category_name

List of non-hierarchical categories. Example non-hierarchical category constraint assignment:

CONSTRAINT = Classification_Country (US, UK, GER)

If the constraint is already assigned to the profile, the level and category names you specify for the constraint replace existing level and constraint specifications.

NULL

If you specify NULL for a constraint previously assigned to the profile, the constraint is dropped from the profile definition.