User Types and Required Privileges | Teradata Vantage - 17.00 - 17.05 - User Types and Minimum Required Privileges - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval

The following table describes the minimal privileges required by the three basic user types.

User Type Privilege Requirements
General Database end-users who typically read data and execute macros in a read-only Views database.
Assign the following privileges on a read-only Views database to all General users:
  • EXECUTE
  • SELECT

The read-only Views database must have privileges on the Tables_Database, as shown in Working with Table Access Privileges for Views.

Update Privileged database users who update data generally require the following privileges:
Privileges required on a read-only Views database:
  • EXECUTE
  • SELECT
  • CREATE VIEW and DROP VIEW
  • CREATE MACRO and DROP MACRO
Privileges required on an updatable Views database:
  • EXECUTE
  • SELECT
  • INSERT, UPDATE, and DELETE
  • EXECUTE PROCEDURE
  • EXECUTE FUNCTION
  • CREATE VIEW and DROP VIEW
  • CREATE MACRO and DROP MACRO
Additional recommended privileges on an updatable Views database for database programmers:
  • SHOW
  • ALTER, CREATE, and DROP PROCEDURE
  • ALTER, CREATE, and DROP FUNCTION
  • ALTER and CREATE EXTERNAL PROCEDURE
  • CREATE TRIGGER and DROP TRIGGER

To provide update capability on views, you must grant the updatable Views database SELECT, INSERT, UPDATE, DELETE WITH GRANT OPTION privileges on the referenced tables in the Tables_Database, as shown in Working with Table Access Privileges for Views.

Batch Data movers, who typically perform batch-level functions, such as:
  • Batch data load, update, and export operations.
  • Data backup, archive, and restore operations, including creating and deleting staging tables.
Batch users must have the following privileges on the Tables_Database:
  • SELECT
  • INSERT, UPDATE, DELETE
  • DUMP and RESTORE
  • CHECKPOINT
  • CREATE TABLE and DROP TABLE
Assistant Administrators If you need one or more assistant administrators to help share administrative duties, they may require the same privileges as those granted to DBADMIN, or a subset of those privileges. Use the procedure shown in Setting Up the Database Administrator User to create assistant administrators and grant the privileges they need.
Database Programmers Database programmers may require administrator-level privileges within the development database to allow them to create and test database objects. Administrators can then deploy the objects to the production database.