User Privileges - Teradata Vantage

Teradata® VantageCloud Lake

Deployment

VantageCloud

Edition

Lake

Product

Teradata Vantage

Published

January 2023

Language

English (United States)

Last Update

2024-04-03

dita:mapPath

phg1621910019905.ditamap

dita:ditavalPath

pny1626732985837.ditaval

dita:id

phg1621910019905

Typical user types, job functions, and privileges.

User	Description	Privileges
General users	Database end-users who only need to read the data or to run pre-existing queries or macros to generate reports.	The following privileges are automatically granted to a user for their space: CHECKPOINT CREATE MACRO CREATE TABLE CREATE TRIGGER CREATE VIEW DROP FUNCTION DROP MACRO DROP TABLE DROP TRIGGER DROP VIEW DUMP EXECUTE INSERT RESTORE SELECT
Update users	Privileged users who perform some general user functions and who also may need to insert, update, or delete data, and create new database objects.	In addition to the privileges automatically granted, batch users need the following privileges: CREATE DATABASE DELETE INSERT MODIFY DATABASE UPDATE
Batch users	High-level users who typically perform batch-level functions, for example: Load, update, and export operations, including creation and deletion of staging tables. Data backup, archive, and restore operations.	In addition to the privileges automatically granted, batch users need the following privileges: CREATE DATASET SCHEMA PROCEDURE CREATE VIEW or VIEW DELETE DROP VIEW DUMP EXECUTE PROCEDURE LOGON RESTORE SHOW
Business analysts Data analysts Database programmers	Users who design and create queries, macros, stored procedures, and database objects for use by the user community. Programmers may require administrator privileges within a development database, while needing only limited privileges in the main production database.	In addition to the privileges automatically granted, business analysts, data analysts and database programmers need the following privileges: ALTER FUNCTION CREATE DATASET SCHEMA CREATE FUNCTION PROCEDURE CREATE VIEW DELETE DROP VIEW EXECUTE FUNCTION EXECUTE PROCEDURE HELP FUNCTION SHOW SHOW FUNCTION UPDATE
Assistant administrators	Administrative users who assist the principal administrator, SYSDBA. Assistant administrative users may have most or all of the same privileges granted to user SYSDBA, but have a much smaller permanent space allocation because they have a limited ownership of objects.	In addition to the privileges automatically granted, assistant administrators need the following privileges: CONSTRAINT ASSIGNMENT CONSTRAINT DEFINITION CREATE DATABASE CREATE DATASET SCHEMA CREATE COMPUTE GROUP CREATE COMPUTE PROFILE CREATE MAP PROCEDURE CREATE PROFILE CREATE ROLE CREATE USER CREATE VIEW CREATE ZONE CTCONTROL DELETE DELETE DATABASE DROP COMPUTE GROUP DROP COMPUTE PROFILE DROP DATABASE DROP MAP DROP PROFILE DROP ROLE DROP VIEW DROP ZONE EXECUTE FUNCTION EXECUTE PROCEDURE MODIFY DATABASE MONITOR SESSION SHOW UPDATE

Create additional user types, as needed, to identify functional differences; however, the examples provided in this document are based on these user types.

Granting Access to System Calendar

Sys_Calendar is a system database that has the Sys_Calendar.CalDates table and Sys_Calendar.Calendar view. The Calendar view is a user-accessible tool for date arithmetic. It permits easy specification of arithmetic expressions and aggregation, and is particularly useful when requesting values aggregated by weeks, months, year-to-date, years, and so on. You can use the Calendar view to get attributes for any date between the years 1900 and 2100, such as which day of the week or which week of the month a date occurs.

After creating the database administrator user, SYSDBA, use the following procedure to grant SELECT privilege on Sys_Calendar to all Vantage users:

Log on to Vantage as SYSDBA.
Run the following command to grant access:
```
GRANT SELECT ON Sys_Calendar TO PUBLIC;
```
Log out as user SYSDBA.

See Creating Database Administrators Using SQL to learn about SYSDBA.