Teradata DBA Assistant

Upgrade your skills with our Video Modules and become a proficient Teradata DBA ready to excel in any organization.

Teradata User Management

1. Teradata User Management Overview

Teradata User Management encompasses the control and maintenance of user accounts, roles, privileges, and permissions in a Teradata Database setting. It is crucial for regulating user access to data and resources, upholding security measures, ensuring compliance, and safeguarding data integrity. The fundamental components of Teradata User Management are essential for effective database administration.

SELECT * FROM DBC.TABLESV ORDER BY TABLENAME;

Understanding the distinction between a Teradata User and a Teradata Database is crucial before creating a user in Teradata. This knowledge will be valuable when collaborating with the security team to manage Teradata User Access Rights as a Teradata DBA. In Teradata, each user is treated as a database, with the key difference being that databases cannot log in, while users can.

To comprehend Teradata effectively, it is essential to grasp the concept of the DBC super user. The DBC super user is established during system initialization. Upon receiving the Teradata Server with the database installed and configured, DBC becomes the primary database/user, housing a variety of system tables, views, macros, UDFs, and procedures. The optimal approach to familiarize oneself with Teradata DBC is to query the Teradata DBC table, as the table names are largely self-explanatory.

2. Teradata User Profile

Learn more about Teradata User Profile Management in this informative video tutorial. Discover how to create a Teradata User Profile to enforce consistent settings across multiple users, such as Password Security, Password Expiry, Password Locking, and shared spool size allocation. Explore how management decisions can be implemented through Teradata profiles to allocate specific resources to different user groups.

CREATE PROFILE HR_profile AS SPOOL = 5000000000 BYTES,

TEMPORARY = 5000000000 BYTES,

PASSWORD = (EXPIRE = 1, MINCHAR = 4, MAXCHAR = 8,

DIGITS = 'R',SPECCHAR = 'R', RESTRICTWORDS = null,

MAXLOGONATTEMPTS = 3,LOCKEDUSEREXPIRE = 0,REUSE = 3);

(n) Digits are not permitted in a password string.

(r) At least one digit is required in a password string.

(y) Digits are permitted in a password string, but not required.

(NULL) Indicates that the DIGITS option is not set for the profile.

Specifies whether certain words are restricted from use within a password string. 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.

MAXLOGONATTEMPTS = 3,LOCKEDUSEREXPIRE = 0,

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 = 3 means the same string can be used 3 times maximum

Teradata User Profile parameters and function of each parameter

MODIFY PROFILE PWD_profile AS password = (LOCKEDUSEREXPIRE = -1 );

Teradata User Profile parameters modification - this example will modify LOCKEDUSEREXPIRE parameter from 3 to 1. Now after executing this only one wrong password attempt of logon will lock the user

MODIFY USER "test_user "as profile="newpassword"

How to Create Teradata User Profile

How to Modify Teradata User Profile

2. Teradata User

MODIFY USER "test_user " AS RELEASE PASSWORD LOCK;

SELECT * FROM DBC.LogonRulesV WHERE USERNAME='test_user';

GRANT LOGON ON ALL TO "test_user";

After executing this command, please verify the entry in the logonrules table. You will notice that the user has a LOGONSTATUS=G, indicating access has been granted.

SELECT * FROM DBC.LogonRulesV WHERE USERNAME='test_user';

By default every user have logon access at the time of creation it is not necessary that each user will have an entry in this table dbc.logonrulesv but the user's whose access is revoked or granted

If User complaints about logon stating error invalid account do check the following

select * from dbc.usersv where username ='test_user';

CREATE USER username PERM = n BYTES

PASSWORD = password;

CREATE USER TEST_USER

AS PERMANENT = 0 BYTES

PASSWORD = abc$123 ;

REVOKE LOGON ON ALL FROM "test_user";

Verify the entry in the logonrules table after executing this command to confirm that the user's LOGONSTATUS is set to 'R', indicating it is REVOKED.

When creating a Teradata User, it is essential for the DBA to consider several key aspects.

When creating a new Teradata user, make sure to provide the following information:
• User Name : Name of the user
• Perm Space : Normally user should be created with zero perm space as they don't need to hold any data and this is the best practice not to assign any perm space to user.
• Password (user initial password must be supplied initially at the time of Teradata user creation.

If user locked count > value of lockeduserexpire value of user profile means user is locked due to bad password. Only thing can be fixed is update user password and share with him but unlock the user as well

How to Revoke User Access in Teradata / Disable Teradata User Logon

How to Reset Teradata User Password

MODIFY USER "test_user " AS PASSWORD ="new password";

This video tutorial is about Teradata User locking and unlocking. Sometimes Users are locked after wrong password multiple attempts. Then there is a way to release Teradata user password lock and Teradata User Password is reset.

3. Teradata User Locking & Unlocking

4. Teradata User Access Role Management

Learn how to manage Teradata User Access Roles in this video tutorial. Discover how Teradata access roles are created and assigned to users. Find out how a single Teradata Access Role can be assigned to multiple users, and vice versa. Explore setting a Default Access Role for a Teradata User with multiple access roles.

Best practice of assigning access rights to a user is through role.

CREATE EXTERNAL ROLE RL_Sales;

GRANT SELECT ON PRACTICE_DB TO RL_Sales;

Grant Access to Role in Teradata. Following will grant select access on complete DB PRACTICE_DB to RL_Sale

Grant Access to Role in Teradata. Following will grant select access only on test table of DB PRACTICE_DB to RL_Sale

GRANT SELECT ON PRACTICE_DB.test TO RL_Sales;

Grant Role to User in Teradata. Following will grant select access on complete DB PRACTICE_DB to User test_user through Role RL_Sale

GRANT RL_Sales to test_user;

One Teradata user may have multiple roles and same way multiple teradata users may have one role. In case there are multiple roles having one user must set a default role to all.

How to Create A User Access Role in Teradata

5. Assigning MultipleTeradata User Access Roles To Teradata User

Discover the process of assigning multiple Teradata User Access Roles to a User through this detailed video tutorial. Remember, setting the default role is crucial for granting access to all assigned roles.

6. Setting up a Default Role To User Who have mutiple Teradata User Access Roles

This video tutorial is about assigning a default Teradata User Access Role