Chapter 6 - Creating Users and Databases
“An invasion of Armies can be resisted, but not an idea whose time has come.”
Creating Users and Databases
Authority can be given to use the CREATE DATABASE or CREATE USER statements to any Teradata user. Then, they may create other Teradata users or databases from their own space, or if specifically authorized, from the space of another Teradata database or user.
The user DBC is the first user and at the top of the Teradata hierarchy. DBC gets things started by creating other databases and users.
Not everyone can create a database or a user. The first user in a Teradata system is user DBC, and it is DBC who starts the process of creating other database and users. DBC has the power to grant other users this Create capability, and those who are granted “Create Database with GRANT option” or “Create User with GRANT option” can grant this Create right to others as well.
Password Security Meanings
ExpirePassword - Number of days to elapse before password expiration. A zero (0) indicates that passwords do not expire and the default is 0.
PasswordMinChar - Min number of characters in a valid password string and default is 1.
PasswordMaxChar - Max number of characters in a valid password string and default is 30.
PasswordDigits - Indicates if digits are allowed in the password (Y, N, or R ) and the default is Y.The letter R indicates that one or more digits are required.
PasswordSpecChar - Indicates if special characters are allowed in the password (Y or N) and the default is Y.The options of A to P and R provide for more secure passwords.
PasswordRestrictedWords - Indicates whether or not a password is subject to content restrictions (Y or N) and the default is N.
PasswordRestrictWords - Indicates whether or not a password is subject to content restrictions (Y or N) and the default is N.
MaxLogonAttempts - Indicates the number of erroneous logons allowed before locking user. Zero (0) indicates that user is never locked and the default is 0 while the max is 32,767.
LockedUserExpire - Indicates the minutes to elapse before a locked user is unlocked. Zero (0) indicates immediate unlock while -1 = locked indefinitely. The default is 0 and max is 32,767.
PasswordReuse - Indicates the days to elapse before a password can be reused. Zero (0) indicates immediate reuse. The default is 0 and the number of days is 32,767.
Now we have Two Users in the Teradata System
DBC has now created a user called SysDBA. Now, both SysDBA and DBC can login and run queries on Teradata.
A Grant Statement so others Create a Database or User
Grant Create User, Create Database on SysDBA to SysDBA with GRANT Option;
DBC has just granted SysDBA the Create User and Create Database right. This gives SysDBA the ability to create users and databases under SysDBA. The “with GRANT Option” portion allows SysDBA to grant others these rights also.
And so the Teradata Hierarchy Begins
A user may create other users and databases from:
- The user's own space, or
- The space of another user or database (if authorized).
The Teradata Hierarchy always has DBC at the top, but as you can see, DBC created SysDBA who then created five databases. Then, under the database Sales, SysDBA created the S_Users database and then created a User in that database. That user then created other users, and one of those users created the S_Views and S_Tables databases.
Creating a Database
A database name must be unique within the Teradata system and can be looked at as a collection of tables, views, macros, triggers, stored procedures, and access rights.
CREATE DATABASE S_Tables
FROM Robert_Davis
AS
PERM = 1000000
SPOOL = 2000000
TEMP = 200000
FALLBACK
ACCOUNT= '$M_Sales';
The CREATE DATABASE statement is used to add new databases to the existing system. The permanent space for new databases comes from the immediate parent database or user. The spool and temporary definitions are not relevant, but these definitions establish the maximum and default value for all databases and users created under this database.
Users are Given Passwords While Database are Not
CREATE USER SysDBA
FROM DBC
AS
PASSWORD=abc123
PERM=2000000
SPOOL=5000000
TEMPORARY = 3000000
ACCOUNT='$Med'
DEFAULT DATABASE = SysDBA
NO FALLBACK;
CREATE DATABASE S_Tables
FROM Robert_Davis
AS
PERM = 1000000
SPOOL = 2000000
TEMP = 200000
FALLBACK
ACCOUNT= '$M_Sales';
Users have passwords while databases do not. User passwords allow users to log on to the Teradata database and run queries. When new user is created, a temporary password for the user is also created. When the user logs on for the first time, they are prompted to change the password. If a user forgets the password, the DBA can assign a new temporary password.
Teradata Administrator Can CREATE Users
You can use Teradata Administrator to create users. Go to Teradata Administrator and to the Tools menu and select CREATE User. Above, you can see the screen.
The Modify User Statement
The MODIFY USER statement enables you to change a user's options. The options changeable without the DROP DATABASE right include:
- Password
- Default database
- Startup string
- Collation
- Fallback Protection default
- Default Dateform
- Default Character Set data type
- Timezone
- Permanent journal default options
MODIFY USER SysDBA
AS
PASSWORD = TeraTom
DEFAULT DATABASE = SQL_Class
DEFAULT ROLE = Sys_Role
PROFILE = NULL ;
Options requiring the DROP DATABASE right are:
- PERMANENT space limit
- SPOOL space limit
- TEMPORARY space limit
- Role
- Profile
- Account codes
- Release password lock
- DROP DEFAULT JOURNAL TABLE
A Clever Way to Reset a User Password
MODIFY USER SysDBA
AS
PASSWORD = NewPass FOR USER ;
The FOR USER keywords create a temporary password that can be used to logon one time by the user. This option is only effective if the ExpirePassword attribute (set in DBC.SysSecDefaults or a profile) is set to a value greater than 0.
The existing password immediately expires and is replaced by “NewPass”. In this example, “NewPass” is effectively a temporary password that allows a one-time logon. You don't need to use “NewPass”. That is just our example, so feel free to use any words you find appropriate.
The temporary password expires immediately when the user logs on for the first time. The user needs to select a new, permanent password at that time. The value for PasswordChgDate is reset to 0.
Accounts and their Associated Priorities
When a User is created their account has an associated performance priority
CREATE USER TeraTom AS PERM=0, SPOOL=300e6, PASSWORD=teacher, ACCOUNT=('$L_Training'),
CREATE USER BillyBob AS PERM=0, SPOOL=300e6, PASSWORD=braintrust, ACCOUNT=('$M_Mrkt'),
CREATE USER HiteshP AS PERM=0, SPOOL=300e6, PASSWORD=Callcenter, ACCOUNT=('$H_CallCenter'),
BillyBob queries run twice as fast as Tera-Tom's queries. HiteshP queries will run twice as fast as BillyBob's queries and four times as fast as TeraTom's queries.
Creating a User with Multiple Account Priorities
CREATE USER SQL00
FROM SysDBA
AS
PASSWORD=abc123
PERM=20000000
SPOOL=5000000
TEMPORARY=3000000
ACCOUNT=('$Med', '$Low', '$High')
DEFAULT DATABASE = SQL00
NO FALLBACK;
Notice that when this user was created, they also created three different Account IDs. The default Account ID will always be the first one listed, but now the user could login or set their session to utilize one of the other Account IDs.
This is done so a user can run queries at different priorities.
Self-Nicing to change Account Priorities
CREATE USER SQL00
FROM SysDBA
AS
PASSWORD=abc123
PERM=20000000
SPOOL=5000000
TEMPORARY = 3000000
ACCOUNT=('$Med', '$Low', '$High')
DEFAULT DATABASE = SQL00
NO FALLBACK;
You can change the Account for the next SQL statement only by running this SQL:
SET SESSION ACCOUNT = '$Low' FOR REQUEST;
You can change the Account for the rest of the session by running this SQL:
SET SESSION ACCOUNT = '$Med' FOR SESSION;
Account String Expansion (ASE)
&L This causes the logon time stamp to be inserted into the account string.
&D This causes the date to be inserted into the account string.
&T This inserts the time of day into the account string. This variable allows for one-second granularity, thus causing a row to be written for each individual SQL request.
&H This inserts the hour of the day into the account string.
&I This inserts the logon host ID/session number/request number into the account string.
&S This inserts the current session number into the account string.
MODIFY USER SQL01
AS
ACCOUNT=('$Med&L', '$Low&D', '$High&T') ;
Account String Expansion allows for additional information to be included in an account when the user logs onto Teradata. Account strings cannot exceed 30 characters. If, as a result of string expansion, you create a string longer than 30 characters, the system truncates all characters to the right of the 30th position.
The DBC.AccountInfo View
SELECT *
FROM DBC.AccountInfoV
ORDER BY 1;
| UserName | AccountName | UserOrProfile |
| CUBETEST | DBC | User |
| MrktProfile | $MAccount | Profile |
| daphPROFILE1 | $MSQL30 | Profile |
| DAPH_USER1 | DBC | User |
| DBC | DBC | User |
UserName , AccountName , UserOrProfile
Columns in this view
Above, are all the columns in this view to see the varying accounts and their names.
The DBC.AMPUsage View
| SELECT | UserName (CHAR(12)) | |
| ,AccountName (CHAR(20)) | ||
| ,SUM (CPUTime) (FORMAT 'z,zzz,zzz.99') as CPU_Used | ||
| ,SUM (DiskIO) (FORMAT 'zzz,zzz,999') as DiskIO_Used | ||
| FROM DBC.AMPUsageV | ||
| GROUP BY 1, 2 | ||
| ORDER BY 3 DESC ; | ||
SELECT COUNT(DISTINCT (Vproc)) AS “# of AMPs// in our System” FROM DBC.AMPUsageV WHERE UserName = USER;
The query just above shows the total number of AMPs and only uses a couple of rows to do so
This view is often used to charge back based on system CPU and Disk/IO usage.
Account String Expansion (ASE) in Action
&L This causes the logon time stamp to be inserted into the account string.
&D This causes the date to be inserted into the account string.
&T This inserts the time of day into the account string. This variable allows for one-second granularity, thus causing a row to be written for each individual SQL request.
&H This inserts the hour of the day into the account string.
&I This inserts the logon host ID/session number/request number into the account string.
&S This inserts the current session number into the account string.
CREATE USER TeraTom
FROM DBC
AS
PASSWORD=abc123
PERM=0
SPOOL=5000000
TEMPORARY=3000000
ACCOUNT=('$MAL&L', '$MBD&D', '$MCT&T', '$MDH&H', '$MEI&I', '$MFS&S', '$MGALL&L&D&T&H') ;
DEFAULT DATABASE=SQL_Class
NO FALLBACK;
The system truncates all characters to the right of the 30th position for Account.
After creating TeraTom, we will run queries from all accounts and check out the DBC.AmpUsage report to see exactly how the account string expansion works.
Test – Run queries Under All Accounts for TeraTom
SET SESSION ACCOUNT = '$MAL&L' FOR REQUEST;
SELECT * FROM SQL_Class.Employee_Table;
SET SESSION ACCOUNT = '$MBD&D' FOR REQUEST;
SELECT * FROM SQL_Class.Employee_Table;
SET SESSION ACCOUNT = '$MCT&T' FOR REQUEST;
SELECT * FROM SQL_Class.Employee_Table;
SET SESSION ACCOUNT = '$MDH&H' FOR REQUEST;
SELECT * FROM SQL_Class.Employee_Table;
SET SESSION ACCOUNT = '$MEI&I' FOR REQUEST;
SELECT * FROM SQL_Class.Employee_Table;
SET SESSION ACCOUNT = '$MFS&S' FOR REQUEST;
SELECT * FROM SQL_Class.Employee_Table;
SET SESSION ACCOUNT = '$MGALL&L&D&T&H' FOR REQUEST;
SELECT * FROM SQL_Class.Employee_Table;
Above, are the queries we ran in the Nexus on the Teradata system. Notice that before each query, we changed the account and used a different Account String Expansion.
The DBC.AMPUsage View
| SELECT | UserName (CHAR(12)) ,AccountName (CHAR(40)) ,SUM (CPUTime) (FORMAT 'z,zzz,zzz.99') as CPU_Used ,SUM (DiskIO) (FORMAT 'zzz,zzz,999') as DiskIO_Used |
|
| FROM DBC.AMPUsageV WHEREUserName= 'TeraTom' | ||
| GROUP BY 1, 2 ORDER BY 2; | ||
The DBC.AMPUsage report shows how Teradata reports the AccountName when the Account String Expansion (ASE) is used. I have highlighted in colors the values. This was run on the date ‘2013-06-27’. Notice the last account truncated at 30 characters. This report didn't show the CPU_Used and DiskIO_Used values that returned.