Grants one or more explicit privileges on a database, user, proxy logon user, table, hash index, join index, view, procedure, User-Defined Function (UDF), function mapping, User-Defined Method (UDM), User-Defined Type (UDT), or macro to a role, group of roles, user, or group of users or databases.
For a list of database privileges, see Teradata Database Privileges.
For information about granting the NONTEMPORAL privilege, see Teradata Vantage™ - Temporal Table Support , B035-1182 .
Required Privileges
- User DBC.
- An owner of the object.
For details regarding security issues associated with owner privileges, see Security Considerations With the CREATE MACRO Privilege
- A user possessing each privilege to be granted.
A user can have a privilege by having been granted it explicitly or by inheriting it from a role as a result of creating a view, macro, or procedure.
- OVERRIDE DELETE CONSTRAINT
- OVERRIDE DUMP CONSTRAINT
- OVERRIDE INSERT CONSTRAINT
- OVERRIDE RESTORE CONSTRAINT
- OVERRIDE SELECT CONSTRAINT
- OVERRIDE UPDATE CONSTRAINT
- A user need not be related to a grantor through ownership to receive a privilege.
A grantor does not need to have any privilege, including WITH ADMIN OPTION, on the grantee to grant a privilege to it, whether the grantee is a role, a user, database, or PUBLIC.
- If a GRANT statement is on a database or user, the privilege applies to all objects, both current and future, created in that space.
If a REVOKE statement later removes the privilege, the privilege is dropped for all objects, regardless of when they were created.
A REVOKE statement at the object level cannot remove a privilege from that object that was granted on the database or user.
- When you specify the WITH GRANT OPTION phrase, the recipient of the privilege can then grant that privilege to other users.
An owner implicitly has the WITH GRANT OPTION privilege on any database, user, or object it owns.
An owner can explicitly grant any or all privileges on any of the following The Privilege Can Be Granted To - a child database
- a child user
- a database object
- any other database
- any other user
- a role
- PUBLIC
You cannot assign row-level security privileges to PUBLIC.
- Any privilege granted automatically or explicitly can be revoked using the REVOKE statement.
See REVOKE (SQL Form).
- Implicit privileges cannot be revoked.
When Privileges Are Granted
GRANT takes effect immediately when the grantee issues their next statement. You do not need to log out to receive a privilege that was just granted to you.
Syntax
GRANT { { ALL [ PRIVILEGES ] | [ ALL BUT ] privilege [,...] | CTCONTROL } ON object | map_privilege [,...] | role_privilege [,...] | profile_privilege [,...] | zone_privilege [,...] | CONSTRAINT ASSIGNMENT | CONSTRAINT DEFINITION } TO { { grantee [,...] | PUBLIC } [ WITH GRANT OPTION ] | role_name [,...] } [;]
- object
-
{ { database_name | user_name } [ .object_name ] | object_name | [ PROCEDURE ] [ database_name. | user_name. ] procedure_name | SPECIFIC FUNCTION [ database_name. | user_name. ] specific_function_name | [ FUNCTION ] [ database_name. | user_name. ] function_name ( [ function_parameter [,...] ] ) | TYPE [SYSUDTLIB.] UDT_name }
- grantee
-
[ ALL ] { database_name | user_name }
- function_parameter
-
[ parameter_name ] data_type
- data_type
-
{ INTEGER | SMALLINT | BIGINT | BYTEINT | DATE | { TIME | TIMESTAMP } [ (fractional_seconds_precision) ] [WITH TIME ZONE] | INTERVAL YEAR [(precision)] [TO MONTH] | INTERVAL MONTH [(precision)] | INTERVAL DAY [(precision)] [TO { HOUR | MINUTE | SECOND [(fractional_seconds_precision)] }] | INTERVAL HOUR [(precision)] [TO { MINUTE | SECOND [(fractional_seconds_precision)] }] | INTERVAL MINUTE [(precision)] [TO SECOND [(fractional_seconds_precision)]] | INTERVAL SECOND [ (precision [, fractional_seconds_precision ] ) | PERIOD (DATE) | PERIOD ( { TIME | TIMESTAMP } [(precision)] [WITH TIME ZONE] ) | REAL | DOUBLE PRECISION | FLOAT [ (integer) ] | NUMBER [ ( { integer | * } [, integer]... ) ] | { DECIMAL | NUMERIC } [ ( integer [, integer]... ) ] | { CHAR | BYTE | GRAPHIC } [ (integer) ] | { VARCHAR | CHAR VARYING | VARBYTE | VARGRAPHIC } [ (integer) ] | LONG VARCHAR | LONG VARGRAPHIC | { BINARY LARGE OBJECT | BLOB | CHARACTER LARGE OBJECT | CLOB } ( integer [ G | K | M ] ) | [SYSUDTLIB.] { XML | XMLTYPE } [ ( integer [ G | K | M ] ) ] [ INLINE LENGTH integer ] | [SYSUDTLIB.] JSON [ ( integer [ K | M ] ) ] [ INLINE LENGTH integer ] [ CHARACTER SET { UNICODE | LATIN } | STORAGE FORMAT { BSON | UBJSON } ] | [SYSUDTLIB.] ST_GEOMETRY [ (integer [ K | M ]) ] [ INLINE LENGTH integer ] | [SYSUDTLIB.] DATASET [ (integer [ K | M ]) ] [ INLINE LENGTH integer ] [storage_format] | [SYSUDTLIB.] { UDT_name | MBR | ARRAY_name | VARRAY_name } }