ACCESS RIGHTS IN TERADATA
This article contains types of access(Privileges) in Teradata.
What is a privilege ?
- A database privilege is a permission to access or to manipulate a database object or data.
- Privileges are sometimes called access rights, permissions, or authorizations.
- Privileges are used by administrators to control access to database objects and data, and to control the types of actions and activities available to users.
- The privileges are used to control which users can :
- Access, create, modify, or delete specific database objects and data
- Execute specific macros, procedures, and UDFs
- Monitor system-wide activity
- Grant privileges to other users
- Teradata Data Control Language (DCL) requests grant and revoke the privileges that enable users to perform these actions and activities.
- To grant a privilege, the user must have the privilege on the object and the right to grant the privilege.
TYPES OF ACCESS RIGHTS
There are four types of access rights available in Teradata :
Let us look at an example to understand these privileges better.
User_1 creates a database named database_1 and a user named User_2. User_2 creates a database called database_2.
IMPLICIT PRIVILEGES :
- They are given to object owners.
- They don’t require a row in DBC.AccessRights (but these rows are added by default).
- The owner of an object can grant explicit privileges to other users, databases, and roles (WITH and WITHOUT GRANT option).
- In the example seen above, User_1 and database_1 have implicit rights on DATABASE_2.
EXPLICIT PRIVILEGES :
- They are managed with the GRANT and REVOKE
- 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”)
AUTOMATIC PRIVILEGES :
- These privileges are automatically granted by Teradata (“Creator Privilege”), when a user creates an object, such as users, databases, tables, views, etc.
- 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.).
- The privileges granted to the creator and owner on the new database are WITH GRANT OPTION.
- The following privileges are automatically granted to a database when it is created:
- CREATE AUTHORIZATION
- CREATE DATABASE
- CREATE MACRO
- CREATE TABLE
- CREATE TRIGGER
- CREATE USER
- CREATE VIEW
- DROP AUTHORIZATION
- DROP DATABASE
- DROP FUNCTION
- DROP MACRO
- DROP TABLE
- DROP PROCEDURE
- DROP TABLE
- DROP TRIGGER
- DROP USER
- DROP VIEW
- The creator receives all the following privileges WITH GRANT OPTION on the newly created table:
- CREATE TRIGGER
- DROP TABLE
- DROP TRIGGER
INHERITED PRIVILEGES :
- These are inherited from the role.
- These are the Privileges that a user acquires indirectly:
- All users automatically have the privileges of PUBLIC, a role-like collection of default privileges. You can also grant or revoke privileges for PUBLIC.
- A user inherits all the privileges granted to any roles of which the user is a member.
- Directory users inherit the privileges of the database users and external roles to which they are mapped.
LEVELS OF PRIVILEGES
There are four levels of privileges offered by Teradata :
- System-level privileges
- Object-level privileges
c.Row or column
d.Other object types
- Row-level privileges
- Zone-level privileges
SYSTEM LEVEL PRIVILEGES
- System-level privileges apply system-wide and cannot be defined on a database object. Generally, these privileges are used by administrators.
- The system-level privileges are as follows:
- CONSTRAINT ASSIGNMENT
- CONSTRAINT DEFINITION
- CREATE MAP
- CREATE ROLE
- CREATE PROFILE
- CREATE ZONE
- DROP MAP
- DROP ROLE
- DROP PROFILE
- DROP ZONE
- ZONE OVERRIDE
- Teradata Database object-level privileges can be granted:
- On database objects (databases, tables, columns, maps, and other objects).
- To other users by the creator or owner of a database object.
- After the privileges are granted on the objects, users can perform the specific actions on the objects permitted by the privileges.
- Access to Teradata Database objects, for example, tables and views is primarily based on object-level user privileges. Object-level privileges provide basic access control, but are discretionary, that is, object owners automatically have the right to grant access on any owned object to any other user.
- In addition to object-level privileges, you can use Teradata row-level security (RLS) to control user access for each table row, by SQL operation. RLS access rules are based on the comparison of the RLS access capabilities of each user and the RLS access requirements for each row.
- You cannot grant privileges on objects in a secure zone to users or roles in another zone. However, you can grant privileges to non-zone users or roles under certain conditions.
Are owners and creators the same ?
The answer is NO. Teradata differentiates 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.
The example used above, User_2 is the CREATOR of the database database_2, and database_1 and User_1 are the owners of database_2.
How to check user privileges?
Data dictionary view contains information about users and the privileges they hold:
View Information it Contains
AllRightsV The explicit and automatic privileges in effect for each user.
AllRoleRights The explicit privileges in effect for each role.
RoleMembersV The members (users) for each role.