Examples of Granting Privileges - Advanced SQL Engine - Teradata Database

SQL Data Control Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
sqd1591723147563.ditamap
dita:ditavalPath
sqd1591723147563.ditaval
dita:id
B035-1149
lifecycle
previous
Product Category
Teradata® Vantage™ NewSQLEngine

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;