16.10 - Creating Roles - Teradata Database

Teradata Database Security Administration

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
Administration
Security
Publication ID
B035-1100-161K
Language
English (United States)

Use the CREATE ROLE statement to create a roles. When you create a role, you automatically receive creator privileges, including the DROP ROLE and WITH ADMIN OPTION privileges. Implicit ownership privileges do not apply.

Creator privileges do not give you the privilege to assign a default role to a user. These parameters are specified in the DEFAULT ROLE clause of CREATE/MODIFY USER, for which you need the CREATE USER or DROP USER privilege, respectively.

Example: Create a Role and Grant it SELECT Privilege

Create a role for general users, grant it SELECT privileges on the entire Accounting database, and grant role membership to the general users Alan, Betty, David, and Ellen:

CREATE ROLE AcctUserRole;
GRANT SELECT ON Accounting TO AcctUserRole;
GRANT AcctUserRole TO Alan, Betty, David, Ellen;

Example: Create a Role and Grant Membership to a User

Create a role for updating the accounts payable table, grant it data change privileges on the Accounting.AccPay view (an updatable view), and then grant role membership to a single high-level accounting user, Charles:

CREATE ROLE AcctUpdateRole;
GRANT SELECT, UPDATE, INSERT, DELETE ON Accounting.AccPay TO AcctUpdateRole;
GRANT AcctEndUserRole, AcctUpdateRole TO Charles WITH ADMIN OPTION;
WITH ADMIN OPTION allows Charles to grant, revoke, or drop either of the specified roles.

Example: Grant CREATE ROLE to Administrators

Grant CREATE ROLE privileges to administrators to perform role administration tasks:

GRANT CREATE ROLE TO Admin2, Admin3;

Assistant administrators Admin2 and Admin3 can now submit the following commands:

CREATE ROLE Role3;
GRANT Role1, Role3 TO Francis;
REVOKE Role1 FROM David;
DROP ROLE Role2;

Related Information

Information on... Is available in...
using external roles for directory users Using Roles for Directory-Based Users.
CREATE ROLE syntax and options SQL Data Definition Language - Syntax and Examples, B035-1144.
usage notes for CREATE ROLE SQL Data Definition Language - Detailed Topics , B035-1184 .