16.00 - Roles Management - Teradata Studio

Teradata Studio User Guide

Teradata Studio
March 2017
User Guide

About Roles

A role defines a set of privileges on database objects. Database administrators (DBAs) can define different roles for different job functions and responsibilities, then assign those roles to different users. Typically, users are members of more than one role. Roles can also be assigned to other roles.

Assigning a default role to a user gives that user access to all the objects for which the role has access privileges. A default role that has been granted other roles provides a user to which it is assigned additional access to all the objects on which the nested roles have privileges. The privileges of a role granted to another role are inherited by every user member of the grantee role.

Right-click a role in the Object List Viewer and click Modify Privileges to grant or revoke role privileges for that role on database objects selected in the form.

Right-click one or more roles in the Object List Viewer and click Modify System Privileges to grant or revoke system-level privileges for these Roles.

About Role Types

There are two basic types of roles.
Role Type Description
Internal Database-managed roles; useful as roles that only apply to a single database platform.
External Directory-managed; provide capability to centralize the management of enterprise-wide roles in a directory.

Required Privileges

You must have the CREATE ROLE privilege to create a role. This applies to both standard database roles and external roles. When you create a role, you automatically receive the creator privileges. This gives you the DROP ROLE privilege and the WITH ADMIN OPTION privilege, but does not give you the privilege to assign a default role to a user. A newly created role has no associated privileges until the privileges are granted.

Task Required Privilege
To modify a user's default role CREATE USER or DROP USER
To grant a role to a user or other role WITH ADMIN OPTION privilege on the role

About Dropping a Default Role

Dropping a role removes all members from the role. If you drop a default role, the system no longer uses the role by default to check privileges when the user logs on, and does not return an error or warning. The result of dropping a role does not cascade to users logged on to the database.

For More Information

For more information on roles, see SQL Data Definition Language - Syntax and Examples and Database Administration.