17.05 - Examples of Granting Privileges - Teradata Database

Teradata Vantageā„¢ - SQL Data Control Language

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1149-170K

Example of Granting Privileges to a Group of Users

The following request grants privileges to a group of users. In this example, all users created under the finance database are granted the privilege to SELECT data from the department table, which is in the personnel database:

GRANT SELECT
ON personnel.department
TO ALL finance;

Example of Granting SELECT on Any Object

The following statement allows user Moffit to retrieve information from any object in the personnel database:

GRANT SELECT
ON personnel
TO moffit;

Example of Granting SELECT on One Column of a Table

This example grants the SELECT privilege only on column_1 of the table named department in the personnel database to user Moffit.

GRANT SELECT (column_1)
ON personnel.department
TO moffit;

Example of Granting SELECT, INSERT, UPDATE, and DELETE on Any Object

The following statement allows user Peterson to perform selects, inserts, updates, and deletes on any object in the personnel database:

GRANT SELECT, INSERT, UPDATE, DELETE
ON personnel
TO peterson;

Example of Granting USER Privilege On a Database

The following statement allows user Phan to create users within the personnel database space, but it does not allow him to modify or drop any users within the personnel database except for the ones that he creates.

GRANT USER
ON personnel
TO phan;

Example of Granting Privileges Related to Triggers

The following request grants CREATE TRIGGER and DROP TRIGGER privileges to user TrigUser for any table in database TrigDB:

GRANT CREATE TRIGGER, DROP TRIGGER
ON TrigDB
TO TrigUser;

The following request grants CREATE TRIGGER and DROP TRIGGER privileges to user TrigUser for TrigDB as the subject table .

GRANT CREATE TRIGGER, DROP TRIGGER
ON TrigTable
TO TrigUser;

You can also grant the same privileges as in the previous requests using the short form TRIGGER to mean both CREATE TRIGGER and DROP TRIGGER.

For example:

GRANT TRIGGER
ON TrigDB TO TrigUser;

GRANT TRIGGER
ON TrigTable
TO TrigUser;

Example of Granting Privileges on an External Function

The following GRANT requests grant the following respective privileges:
  • ALTER FUNCTION on the specific external function find_text to users named user_dba and user_in_house.
  • ALTER FUNCTION on all external UDFs in the SYSLIB database to user_dba.
  • CREATE FUNCTION on the database document_db to user_dba.
  • EXECUTE FUNCTION on the overloaded function text_process to user sammy.
GRANT ALTER FUNCTION
ON SPECIFIC FUNCTION find_text
TO user_dba, user_in_house;

GRANT ALTER FUNCTION
ON syslib
TO user_dba;

GRANT CREATE FUNCTION
ON document_db
TO user_dba, user_in_house;

GRANT EXECUTE
ON FUNCTION text_process(CHAR,CHAR,INTEGER)
TO sammy;