fbpx

Types of Teradata Access Rights

Teradata distinguishes between these four types of access rights:

  1. Implicit: The Teradata privileges of a user or database owning an object (“ownership privilege”)
  2. Explicit: The Privileges explicitly granted with the GRANT statement
  3. Inherited: The privileges inherited by being a role member
  4. 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 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.

Understand Teradata Access Rights Before You Regret 1

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!

An object’s owner can grant explicit privileges to other users, databases, and roles (WITH and WITHOUT GRANT option).

In our example above, “System Admin” and “Database 1” has implicit privileges on “Object”.

Teradata Access Rights – Explicit Privileges

Explicit privileges are managed with the GRANT and REVOKE statements.

The GRANT command 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 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 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.

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.).

Inherited Privileges

Not too much to say: These are inherited from the role.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
GET OUR TERADATA BOOK

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

  • Hi Roland,
    Good basic information clearly presented. Well done.

    However, I think that your description of ‘inherited’ Access Rights is missing something. I understand why you’ve described Access Rights from Roles as being inherited (I’d never thought of them that way but I see your point), but the term ‘inherited Access Right’ is often used to describe something else.

    An ‘inherited’ Access Right is one that is granted to a user/database specifically during the “CREATE USER/DATABASE” command. Assume the following hierarchy and commands.

    A user name DEV_USERS exists.
    The following command is executed: “GRANT SELECT ON db1 TO ALL DEV_USERS;”
    The following command is now executed: “CREATE USER Fred FROM DEV_USERS;”

    As a result of this (specifically the ‘ALL DEV_USERS’), when ‘fred’ is created, this userid will also be granted the SELECT Access Right on database ‘db1’. this user has inherited the Access Right from an owner.

    Cheers,
    Dave

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like

    >