Syntax Elements of GRANT (SQL Form) | SQL Data Control Language | Vantage - Syntax Elements - Advanced SQL Engine

ALL PRIVILEGES

User or database receives all privileges that can be granted on the specified object. GRANT ALL means that all implicit and explicit object privileges owned by the grantor WITH GRANT OPTION that pertain to the type of object, and only those privileges, are granted on the specified database object.

To include the ability for the designated user to GRANT object privileges to other users or databases, specify WITH GRANT OPTION.

ALL cannot be granted to roles.

An error is returned if the grantor has no privileges WITH GRANT OPTION on the object.

You must use the monitor form of the GRANT statement to grant monitor privileges.

See GRANT (Monitor Form).

privilege

One of the privileges listed under the topic Privilege Dictionary or one of the keywords for a privilege combination.

See Granting Multiple Privileges With a Single Keywordfor a list of the combination privilege keywords.

INSERT, REFERENCES, SELECT, and UPDATE privileges have table-level and column-level options.

See REFERENCES Privilege.

The DELETE privilege applies only to the DELETE DML statement, not to DELETE USER or DELETE DATABASE, which are granted by DROP USER and DROP DATABASE, respectively.

See also Rules for privilege Keywords.

For an explanation of the privileges a specific statement requires, see Required Privileges section in the appropriate volume of the SQL book set.

ALL BUT privilege

User is to receive all privileges that can be granted on the specified object except for those specified in the privilege list. As in ALL, only those object privileges owned by the grantor WITH GRANT OPTION are granted.

ALL BUT is a Teradata extension to the ANSI/ISO SQL:2011 standard.

Granting privileges on a database or user is a Teradata extension to the ANSI/ISO SQL:2011 standard.

database_name

user_name

Name of the database or user to which the privilege set is granted.

All objects contained by the specified database or user space are granted the specified privilege set.

database_name.object_name

user_name.object_name

Name of the immediate owning database or user for the specified database object and the name of the database object (table, view, procedure, or macro) to which the privilege set is granted. Only the specified database object is granted the specified privilege set.

Name of the immediately owning database or user and the name of the object (table, view, procedure, or macro) on which the privileges are to be granted. Only the named object is affected.

object_name

Name of a database object (table, join index, hash index, view, procedure, UDF, UDM, UDT, macro, or authorization name) on which the privileges set is granted.

If the form of the privileges option includes a set of column names, then object_name must specify either a table or view.

You should always qualify object names when granting privileges because Teradata Database checks for matching database names before checking for object names.

If the object name is not qualified and the system finds a database with that name, then Teradata Database assumes the name is a database name.
If the object name is not qualified and no database having that name is found, then Teradata Database assumes the object is within the current default database.
If the object name is not qualified by either a database name or user name and there is an object with the same name under the current database of the executing user and that of the grantee, then the object is assumed to be in the current database of the executing user. The only exception to this is for all objects related to UDTs, including UDT-related UDFs and methods, all of which must be contained within the SYSUDTLIB database.
An unqualified object name is considered to be that of the current database if the name is that of the current database and also the name of a table either within the current database or within the database of the grantee.
If neither a database nor an object is found with the specified name, then Teradata Database aborts the request and returns an error to the requestor.

PROCEDURE

database_name

user_name

procedure_name

Object is a procedure.

PROCEDURE is optional if the privilege being granted contains the PROCEDURE keyword.

See Usage Notes for Procedure-Specific Privileges.

database_name and user_name are optional. You can qualify procedure_name by its containing database_name or user_name.

You can grant the following privileges if you specify PROCEDURE:

If you specify EXECUTE, you grant the EXECUTE PROCEDURE privilege.
If you specify DROP, you grant the DROP PROCEDURE privilege.

You must grant the ALTER EXTERNAL PROCEDURE and CREATE EXTERNAL PROCEDURE privileges explicitly.

SPECIFIC FUNCTION

database_name

user_name

specific_function_name

Name of the function on which a privilege set is to be granted.

You must specify the SPECIFIC FUNCTION keywords when a privilege is granted on an overloaded function.

database_name and user_name are optional. You can qualify specific_function_name by its containing database_name or user_name.

FUNCTION

database_name

user_name

function_name

Name of the UDF on which a privilege set is to be granted.

FUNCTION is optional if the privilege being granted contains the FUNCTION keyword.

The FUNCTION keyword must be specified when a privilege is granted on a UDF by its calling name.

database_name and user_name are optional. You can qualify function_name by its containing database_name or user_name if necessary.

parameter_name

data_type

Parenthetical comma-separated list of data types and optional parameter names for the variables to be passed to the UDF. The data types are required in order to differentiate overloaded functions with the same name.

BLOB and CLOB types must be represented by a locator. For a description of locators, see “USING Request Modifier” in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146. Teradata Database does not support in-memory LOB parameters: an AS LOCATOR phrase must be specified for each LOB parameter and return value.

You must specify opening and closing parentheses even if no parameters are passed to the function.

parameter_name is optional. If you specify one parameter name, then you must specify names for all the parameters passed to the function. See “HELP FUNCTION” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

The data type associated with each parameter is the type of the parameter or returned value. All Teradata Database data types are valid. Character data can also specify a CHARACTER SET clause.

TYPE SYSUDTLIB.

UDT_name

Name of a UDT on which a privilege set is to be granted.

SYSUDTLIB is optional. If you specify a containing database for UDT_name, you must specify SYSUDTLIB.

The TYPE privileges that you can grant to a UDT are listed in the following list:

You can grant UDTMETHOD only on the SYSUDTLIB database.
You can grant UDTTYPE only on the SYSUDTLIB database.
You can grant UDTUSAGE on the SUSUDTLIB database, on TYPE, or on both.

map_privilege

Grant create and drop map privileges to users and roles.

You can specify the following privileges:

CREATE MAP
DROP MAP
MAP

MAP is shorthand, not a separate privilege. Specify MAP to grant both the CREATE MAP and DROP MAP privileges.

role_privilege

One of the following role privileges:

CREATE ROLE
DROP ROLE
ROLE

The following request allows user Franklin to create and drop roles:

     GRANT ROLE
     TO Franklin;

This form does not have an ON clause. If you specify an ON clause for the role privilege, the system returns an error.

ROLE is shorthand, not a separate privilege. Specify ROLE to grant both the CREATE ROLE and DROP ROLE privileges.

Role privileges cannot be granted to a role or to PUBLIC.

profile_privilege

One of the following privileges:

CREATE PROFILE
DROP PROFILE
PROFILE
PROFILE is shorthand, not a separate privilege. Specify PROFILE to grant both the CREATE PROFILE and DROP PROFILE privileges.

Profile privileges can only be granted to a set of users or to a role. They cannot be granted on an object.

You can assign row-level security privileges to a profile and then map a directory user to that profile. The constraints apply to that directory user even if the user is not mapped to a permanent user. See Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100, for more information.

zone_privilege

One of the following privileges:

CREATE ZONE
DROP ZONE
ZONE

ZONE is shorthand, not a separate privilege. Specify ZONE to grant both the CREATE ZONE and DROP ZONE privileges.

Zone privileges cannot be granted to a role.

See “Implementing Teradata Secure Zones” in Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100.

database_name

user_name

role_name

PUBLIC

Name of a database, user, role, or PUBLIC on which the explicitly granted privileges are to be granted. All objects contained by this database or user space are affected.

You cannot grant row-level security privileges to PUBLIC.

database_name

user_name

Name of a database or user on which the privileges are to be granted. All objects in this database or user space are affected.

database_name.object_name

user_name.object_name

Name of the immediately owning database and optionally the name of the object or the name of the user and the name of the object (table, view, procedure, or macro) on which the privileges are to be granted. Only the named object is affected.

CONSTRAINT ASSIGNMENT

System-level CONSTRAINT ASSIGNMENT privilege.

CONSTRAINT ASSIGNMENT enables a user to maintain row-level security constraint object assignments to users, profiles, and tables such as ALTER TABLE, CREATE PROFILE, CREATE TABLE, CREATE USER, MODIFY PROFILE, and MODIFY USER.

You can only grant CONSTRAINT ASSIGNMENT to specific users or roles, not to tables or databases.

You cannot grant CONSTRAINT ASSIGNMENT to PUBLIC.

See "CONSTRAINT ASSIGNMENT Privilege" in System-Level Privileges for Row-Level Security for more information.

CONSTRAINT DEFINITION

System-level CONSTRAINT DEFINITION privilege.

CONSTRAINT DEFINITION enables a user to create and maintain row-level security constraints, such as ALTER CONSTRAINT, CREATE CONSTRAINT, and DROP CONSTRAINT.

You can only grant CONSTRAINT DEFINITION to specific users or roles, not on tables or databases.

You cannot grant CONSTRAINT DEFINITION to PUBLIC.

See "CONSTRAINT DEFINITION Privilege" in System-Level Privileges for Row-Level Security for more information.

CTCONTROL

System-level CTCONTROL privilege.

CTCONTROL authorizes a user to grant or revoke the CONNECT THROUGH privilege (see GRANT CONNECT THROUGH) using the GRANT CONNECT or REVOKE CONNECT statements.

In the ON clause, you can only grant CTCONTROL to users, not to other types of database objects.

You cannot grant CTCONTROL to PUBLIC.

Use care when granting the CTCONTROL privilege, because this privilege applies to all database objects in the system.

See CTCONTROL Privilege.

ALL

user_name

database_name

Name of a database or user that identifies the recipient. user_name must be the identifier of a user already defined to the system.

You can specify a maximum of 25 names per GRANT request.

ALL is optional. If you specify ALL, then the object privileges are granted to the named database or user and to every database or user owned by that database or user now and in the future.

ALL user_name is a Teradata extension to the ANSI/ISO SQL:2011 standard.

The following statement allows all current and future users created under the personnel database to select data from the department table:

GRANT SELECT
ON personnel.department
TO ALL personnel;

PUBLIC

Privileges are inherited by all existing and future Teradata Database users and databases.

For example:

GRANT SELECT
ON personnel.department
TO PUBLIC;

WITH GRANT OPTION

Grantee receives the granted privileges WITH GRANT OPTION.

This option does not apply to grantees that are roles or who are zone guests in a zone.

A GRANT statement that specifies WITH GRANT OPTION privilege when there is a role or a zone guest in the list of grantees aborts and returns an error message.

WITH GRANT OPTION applies to the indicated privileges only.

In this example, user George grants all privileges that he has on his own user space to user Marston with the following restrictions:

George can grant only those privileges for which he has WITH GRANT OPTION at the user level.
George must have the WITH GRANT OPTION privilege on the privileges he grants to Marston.
```
GRANT ALL
ON marston
TO marston
WITH GRANT OPTION;
```

database_name

user_name

Containing database or user for role_name, if different from the current database or user.

role_name

Name of an existing role, which can be a Teradata Database role or an EXTERNAL role.

Row-level security constraint values cannot be assigned to roles. They can only be assigned to users and profiles.

These privileges can be granted to databases, users, and roles, except for the USER privileges, which cannot be granted to roles:

CREATE DATABASE
CREATE FUNCTION
CREATE MACRO
CREATE TABLE
CREATE VIEW
CREATE PROCEDURE
CREATE USER (cannot be granted to roles)
DROP DATABASE
DROP USER (cannot be granted to roles)