Teradata Access Rights: Implicit, Explicit, Inherited, and Automatic
Learn about the different types of access rights in Teradata, including implicit, explicit, inherited, and automatic privileges, and how they are granted to owners and creators of database objects.
Types of Teradata Access Rights
Teradata categorizes access privileges into four types:
- Implicit: The Teradata privileges of a user or database owning an object ("ownership privilege")
- Explicit: The privileges explicitly granted with the GRANT statement
- Inherited: The privileges inherited by being a role member
- Automatic: The privileges automatically granted by Teradata
Owners and Creators
Teradata distinguishes between creators and owners of database entities.
The creator of an object is the user who executes the DDL statement for its creation. As a result, only a single user can have this role.
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Early subscribers keep launch access before the paid plan launches.
The database object's owners comprise all users and databases positioned higher in the database hierarchy.
The database of a new object is referred to as its immediate owner.
If DBA creates an "Object," they are the creator, while "System Admin" and "Database 1" are the owners.
Teradata Access Rights - Implicit Privileges
Object owners are granted implicit privileges and do not require a corresponding row in the DBC.Accessrights table, although Teradata does create such rows by default, which can be deleted. Ownership privileges cannot be revoked.
Get the next issue by email.
An object's owner can grant explicit privileges to other users, databases, and roles (WITH and WITHOUT GRANT options).
The "System Admin" and "Database 1" databases possess implicit privileges on the "Object".
Teradata Access Rights - Explicit Privileges
Explicit privileges are managed through the use of GRANT and REVOKE statements.
The GRANT statement assigns explicit privileges to databases, users, tables, indexes, views, stored procedures, UDFs, macros, and roles:
GRANT <privileges> ON <object> TO <user> [WITH GRANT OPTION];
To grant explicit privileges, the granting user needs to have the same privileges on this object and grant authority ("WITH GRANT OPTION")
Explicit grants may be revoked upon fulfillment of the following requirements:
- We have the permission to grant the permissions, or
- We are the owner of the object.
Automatic Privileges
Certain privileges are automatically granted when a user creates an object, such as users, databases, tables, views, etc. Automatic privileges are automatically granted by Teradata ("Creator Privilege"). If a table is created, the assigned privileges are DELETE, INSERT, UPDATE, SELECT, etc.
Suppose a user creates a new database or user. In that case, they will receive automatic permissions on the object, and the new object will be granted certain privileges, including delete, insert, update, and select.
Inherited Privileges
Privileges inherited through roles simplify administration by allowing for the assignment of multiple rights to a user through a single role. This eliminates the need to assign individual rights repeatedly to each user. They are therefore available indirectly through an assigned role rather than being granted to a user directly. This approach is preferred for granting permissions.
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe before the paid plan launches and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.