You can create permanent database users with a CREATE USER statement, for example:
CREATE USER user_name FROM database_name or user_name AS PASSWORD = password PERM = permanent_space SPOOL = spool_space TEMPORARY = temporary_space ACCOUNT = [‘a ccount_string1’..., ‘account_string2’...] DEFAULT DATABASE = database_name PROFILE = profile_ name DEFAULT ROLE = role_name OPTION = value,... ;
where:
| Syntax Element | Explanation |
|---|---|
| CREATE USER user_name | The name of an individual Teradata Vantage user. Recommendation: Do not allow multiple users to share a username for accessing the database. |
| FROM database_name or user_name | The name of the database or user that owns the space in which the user is created. Recommendation: Create users in the space owned by the principal administrator, either DBADMIN or SECADMIN. For information on typical administrator configuration, see the topics beginning with Working with Administrative Users. |
| AS PASSWORD = password | Required. A temporary password. If the system is set up according to the recommendations in Creating the Security Administrator User, the user is prompted to change the temporary password to a private password at first logon. For information on password requirements, see Managing Database Passwords. |
| PERM = permanent_space | Required. The space in bytes available to contain objects that the user creates or owns. Permanent space comes from, and cannot exceed, the current PERM of the immediate owner of the user, which is normally the database or user specified in the FROM clause of the CREATE USER statement.
Recommendation: Administrators, database programmers, and other users that need to create and store objects in owned space require a perm space allocation. Even if a user does not need perm space, a value of 0 is required. For information about the assignment and use of perm space, see Teradata Vantage™ - Database Administration, B035-1093. If you allocate space to a new user, the user automatically receives certain ownership privileges on any objects that may inhabit the space.
For information on the privileges implicit in object ownership, see Ownership Privileges. For a list of privileges automatically granted to newly created users, see Privilege Dictionary. |
| SPOOL = spool_space | Optional. Defines the maximum amount of space available to the user for intermediate query results, volatile tables, or formatted answer sets to queries. The system borrows spool space from unused system perm space, such as the spool reserve in Creating the Spool Space Reserve. The spool space specified in a profile takes precedence over spool space specified in a CREATE USER statement. Recommendation: Specify spool space as part of a profile unless the user requires unique spool considerations. The spool space allocation should be approximately 20% of the perm space available to the largest database the user accesses. |
| TEMPORARY = temporary_space | Optional. Only objects such as global temporary tables and volatile tables require temporary space. Recommendation: You should develop a strategy for use of the database objects that require temp space before you assign temp space to users. You can add a temp space specification later with a MODIFY USER or MODIFY PROFILE statement. For additional information, see Teradata Vantage™ - Database Administration, B035-1093. |
| ACCOUNT = [‘a ccount_string1 ’..., ‘ account_string2 ’ ...] | Optional. User account information that forms the basis of various user management options. Recommendation: Specify accounts at the profile level. For information on creating accounts, see Teradata Vantage™ - Database Administration, B035-1093. |
| DEFAULT DATABASE = database_name | Optional. The database that owns the space in which the SQL Engine stores or searches for new or target objects in an SQL query, unless a different database is specified in the query. Recommendation: Specify the database most often accessed by the user. Use the SET SESSION DATABASE statement to establish a new default database for the current session. Use the SELECT DATABASE statement to check the current session database.
|
| PROFILE = profile_ name | Optional. Specify the name of an existing profile to give the user membership in the profile. When syntax elements appear in both the CREATE USER statement for a user, and in a profile of which the user is a member, the profile values take precedence. A permanent user can be a member of only one profile. Recommendation: Specify membership in a profile for each user. |
| DEFAULT ROLE = role_name | Required if the user is a role member. Identifies the default role used for determining user privileges in the database for the current session. You must specify a default role even if the user is a member of only one role. The role must exist in the database before you can be specify it in a CREATE USER statement. Users that are members of more than one role can use the SET ROLE statement at the beginning of a session or transaction to change from the default role to another role.
Recommendation: Specify the role associated with the activities performed most frequently by the user. |
| Additional Options | |
| FALLBACK | Optional. Automatically creates a duplicate of each table stored in the user space, in addition to the duplicates already created by disk mirroring. The system reverts to the fallback tables in the event of a failure. You cannot use the NO FALLBACK option and the NO FALLBACK default on platforms optimized for fallback.
For information on the use of FALLBACK, see Teradata Vantage™ - Database Administration, B035-1093. |
| Collation | Optional. Database default values that apply to all users are already set as part of system installation and configuration. Recommendation: Do not change the values for an individual user unless the default values are not applicable. All users must use the same collation value.
|
| Time Zone | |
| Default Date Form | |
| Default Character Set | |