16.10 - Granting Privileges to a Role - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Published
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)
Last Update
2018-04-26

Use the GRANT statement to grant privileges to the roles necessary to serve the basic user types. A GRANT statement takes the following basic form:

GRANT  privilege 
ON  database_object_name 
TO  role_name;
  1. From the client program of your choice, log on to Teradata Database as user DBADMIN.
  2. Grant privileges to each role as follows:
    Fields and Controls Explanation
    Database Name Required.
    • To grant privileges on an entire database, specify the name of the database.
    • To grant privileges on an object, specify the parent database that contains the object.

    Recommendation: When specifying privileges for:

    • General users, select a read-only Views database.
    • Update users, select a read-only Views database or an updatable Views database.
    • Batch users, select the Tables_Database.
    • Administrative users, select DBADMIN.
    • Database programmers, select a Development database.
    Object Type Required.

    Specify the type of database object on which privileges are being granted.

    For example:

    • To grant privileges on the Tables_Database, specify Database.
    • To grant privileges on the tables located in the Tables_Database, specify Table.

    Recommendation:Always grant privileges at the highest level that is appropriate, while observing security concerns and user “need to know.”

    Objects Required when objects are displayed.

    If the Object Type is Database, this field is blank and not required. Otherwise, the Objects field displays all of the database objects of the object type contained in the named database.

    For example, if Database Name is Tables_Database and Object Type is Table, this field displays all of the tables contained in the Tables_Database.

    Select the specific database object on which privileges are being granted.

    To/From User Not applicable for granting privileges to roles.
    Role Required. The role to which the specified privileges are granted.

    Select the name of a previously created role from the displayed list.

    Public Optional.

    Checking the Public box grants the specified privileges to PUBLIC, a group of rights accessible by any user with logon privileges, even if no other privileges have been granted.

    Recommendation:Do not grant additional privileges to PUBLIC at this time.

    Normal Required. Specify the privileges by checking the boxes, based on the guidelines in User Types and Minimum Required Privileges.

    Recommendation:Consider the privileges that the user may get without formal granting, as shown in Types of Privileges, before deciding on what privileges to grant.

    Create
    Drop
    All Optional. Do not use for roles.

    Grants all privileges that apply on the database object. This box is usually used only for the master administrator, DBADMIN.

    Dictionary Optional. Grants administrative privileges on data dictionary tables.
    Access Optional.

    Provides basic SELECT and EXECUTE privileges on the specified object.

    All But Optional.

    When the All But box is checked, role members receive all privileges except those checked in the Normal, Create, and Drop sections.

    Not all privileges need to be granted using roles. It may be more efficient to grant some specialized privileges directly to a user, rather than creating a role with a membership of one.

    Any user can grant privileges on any database object it owns to any other user or role, excluding the CREATE and DROP privileges, which are not ownership privileges.

    For example:

    GRANT  EXECUTE, SELECT, INSERT, UPDATE, DELETE, DUMP, RESTORE, CHECKPOINT, CREATE TABLE, DROP TABLE ON "Tables_Database" TO "Role_BatchUser_2";
  3. Repeat this procedure to grant privileges to all roles.