15.00 - Roles - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Roles

Roles define privileges on database objects. A user who is assigned a role can access all the objects that the role has privileges to.

Roles simplify management of user privileges. A database administrator can create different roles for different job functions and responsibilities, grant specific privileges on database objects to the roles, and then grant membership to the roles to users.

Advantages of Using Roles

  • Simplify privilege administration.
  • A database administrator can grant privileges on database objects to a role and have the privileges automatically applied to all users assigned to that role.

    When a user’s function within an organization changes, changing the user’s role is far easier than deleting old privileges and granting new privileges to go along with the new function.

  • Reduce Data Dictionary disk space.
  • Maintaining privileges on a role level rather than on an individual level makes the size of the DBC.AccessRights table much smaller. Instead of inserting one row per user per privilege on a database object, Teradata Database inserts one row per role per privilege in DBC.AccessRights, and one row per role member in DBC.RoleGrants.

    Usage

    The following steps describe how to manage user privileges using roles.

    1 Define a role.

    A CREATE ROLE statement defines a role. A newly created role does not have any associated privileges.

    2 Add privileges to the role.

    Use the GRANT statement to grant privileges to roles on databases, tables, views, macros, columns, triggers, stored procedures, join indexes, hash indexes, and UDFs.

    3 Grant the role to users or other roles.

    Use the GRANT statement to grant a role to users or other roles.

    4 Assign default roles to users.

    Use the DEFAULT ROLE option of the CREATE USER or MODIFY USER statement to specify the default role for a user, where:

     

    DEFAULT ROLE = …

    Specifies …

    role_name

    the name of one role to assign as the default role for a user.

    NONE

    that the user does not have a default role.

     

    NULL

    ALL

    the default role to be all roles that are directly or indirectly granted to the user.

    At logon time, the default role of the user becomes the current role for the session.

    Privilege validation uses the active roles for a user, which include the current role and all nested roles.

    5 If necessary, change the current role for a session.

    Use the SET ROLE statement to change the current role for a session.

    Managing role-based privileges requires sufficient privileges. For example, the CREATE ROLE statement is only authorized to users who have the CREATE ROLE system privilege.

    Related Topics

    For information on the syntax and usage of roles, see SQL Data Definition Language.