Granting Privileges to a Role - Teradata Database

Granting Privileges to a Role

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 Start Teradata Administrator and log on as user DBADMIN.

2 From the main screen, click the Preview Mode button to allow future display of generated SQL.

3 Grant privileges to each role as follows:

a Click Tools>Grant/Revoke>Object Rights.

b In the Grant/Revoke Objects dialog, specify values for the following:

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” on page 116. Recommendation: Consider the privileges that the user may get without formal granting, as shown in “Types of Privileges” on page 117, 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.

Note: 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.

c Click the Grant button. The Query Window displays the generated GRANT statement, for example:

GRANT  EXECUTE, SELECT, INSERT, UPDATE, DELETE, DUMP, RESTORE, CHECKPOINT, CREATE TABLE, DROP TABLE ON "Tables_Database" TO "Role_BatchUser_2"

d In the Query Window, click the Run Query button . A Query Complete message appears in the bottom left corner of the main window when the query executes.

e Close the Query Window and the Grant/Revoke Objects dialog box.

4 Repeat this procedure to grant privileges to all roles.

5 Close Teradata Administrator or go to “Granting User Membership in a Role.”

Reference Information

For Step...	Information on...	Is available in...
3	syntax and options for the GRANT (SQL Form) statement	SQL Data Control Language
	using roles to manage user access	Security Administration