16.10 - User Types and Minimum Required Privileges - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

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.