Understand Teradata Access Rights Before You Regret
Types of Teradata Access Rights
Teradata distinguishes between these four types of access rights:
- 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 objects.
The creator is the user who executed the DDL statement to create an object. Thus, only one user can be the creator of an object.
The owners of a database object are all databases and users which are above the object in the database hierarchy.
When a database object is created, the database which holds the new object is called the immediate owner.
Here is an example. If DBA creates “Object” then DBAis the Creator and “System Admin” and “Database 1” are owners.
Teradata Access Rights – Implicit Privileges
Implicit privileges are given to object owners. They don't require a row in DBC.Accessrights (but these rows are added by default). Ownership privileges can't be revoked!
The owner of an object can grant explicit privileges to other users, databases, and roles (WITH and WITHOUT GRANT option).
In our example from above, “System Admin” and “Database 1” have implicit privileges on “Object”.
Teradata Access Rights – Explicit Privileges
Explicit privileges are managed with the GRANT and REVOKE statements.
The GRANT command is used to assign 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 the same privileges on this object and grant authority (“WITH GRANT OPTION”)
Explicit grants can be revoked it the revoking user needs:
- The permissions to grant the permissions
- To be Owner of the object
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.
Additionally, if a user creates a new database or user, not only the creator will get automatic permissions on the new object, but also the newly created object will get some automatic privileges (delete, insert, update, select, etc.).
Not too much to say: These are inherited from the role.