16.10 - Concepts, Policies, User Profiles, and Rules - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

Isolating Users from the Database Using Views

This topic presents a well-designed database structure based on a structure formulated and used by a Teradata customer.

The design incorporates different task-oriented profiles that access only a series of views and macros defined within several task-oriented databases.

Only these task-oriented access definition databases can access the base tables defined for the database. This design structure builds a virtual firewall between all users and the base tables they access.

Flow Diagram of the Database Structure

The following graphic illustrates the work flows and individual task-oriented profiles and database objects defined for this database structure.



Note that Tables_Database contains only base tables and their associated index subtables.

The example database defines three types of end user profile. The following table describes those types.

End User Profiles

        Profile                          Description Permitted SQL Statements and               Utility Commands
Query_Profile Defines privileges to the views, macros, and stored procedures defined in Query_Database.
  • EXECUTE
  • SELECT
Upsert_Profile Defines privileges to the views, macros, and stored procedures defined in Upsert_Database.
  • DELETE
  • EXECUTE
  • INSERT
  • SELECT
  • UPDATE
Batch_Profile Defines privileges to the tables defined in Tables_Database.
  • CHECKPOINT
  • CREATE TABLE
  • DROP TABLE
  • DUMP
  • RESTORE

Rules for End User Profiles

The following rules apply to all user profiles except Batch_Profile:

  • All users belong to one or more task profiles.
  • Users inherit their privileges from the task profiles to which they belong.
  • A user can belong to more than one task profile.
  • Privileges are granted at DATABASE or USER levels only.
  • Query_Profile and Upsert_Profile only have access to databases that contain macros, stored procedure definitions, and views exclusively.
  • Batch_Profile is the only profile that permits the base tables in Tables_Database to be accessed directly.

Rules for Batch_Profile

  • Users inherit their privileges from Batch_Profile.
  • Privileges are granted at Database or User levels only.
  • Batch_Profile has direct access to the base tables in Tables_Database because of the functions performed using it.
  • Batch_Profile users can perform the following functions against Tables_Database
    • CHECKPOINT transactions
    • CREATE TABLEs
    • DROP TABLEs
    • DUMP databases
    • RESTORE databases

Rules for Query_Database

Note that Query_Database objects are organized in such a way that the entire database can be archived in one operation. You can also archive Upsert_Database in the same operation if you choose to do so.

The following rules apply to Query_Database.

  • Both Query_Profile and Upsert_Profile users can perform the following functions against Query_Database.
    • CALL permitted stored procedures
    • EXECUTE permitted macros
    • SELECT rows from Tables_Database objects through views
  • The database contains only views, stored procedure definitions, and macros that permit an end user to query or grant privileges on Tables_Database objects indirectly.

Rules for Upsert_Database

Note that Upsert_Database objects are organized in such a way that the entire database can be archived in one operation. You can also archive Query_Database in the same operation if you choose to do so.

The word upsert derives from update-in sert and implicitly refers to delete as well.

Only Upsert_Profile users exclusively can perform the following functions against Upsert_Database:

  • CALL permitted stored procedures
  • DELETE rows in Tables_Database objects through views
  • EXECUTE permitted macros
  • INSERT rows into Tables_Database objects through views
  • SELECT rows from Tables_Database objects through views
  • UPDATE rows in Tables_Database objects through views

The database contains only views, stored procedure definitions, and macros that permit an end user to perform the following actions on Tables_Database objects indirectly.

  • DELETE rows
  • INSERT rows
  • SELECT rows
  • UPDATE rows
  • GRANT privileges on database objects