GRANT (SQL Form) Statement | SQL Data Control Language | Teradata Vantage - 17.05 - GRANT (SQL Form) - 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

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

You must be one of the following to grant privileges on an object using the SQL GRANT statement:
  • 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.

To grant the following privileges, you must have the CONSTRAINT ASSIGNMENT privilege.
  • OVERRIDE DELETE CONSTRAINT
  • OVERRIDE DUMP CONSTRAINT
  • OVERRIDE INSERT CONSTRAINT
  • OVERRIDE RESTORE CONSTRAINT
  • OVERRIDE SELECT CONSTRAINT
  • OVERRIDE UPDATE CONSTRAINT
Note the following details about the privileges required to execute a GRANT (SQL Form) request.
  • 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 }
}