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 }
}
Syntax Elements
- 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 Vantage 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 Vantage assumes the name is a database name.
- If the object name is not qualified and no database having that name is found, then Vantage 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 the system 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 the information about the USING request modifier in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146. Vantage 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 the information about 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 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:
- 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:
- 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™ - Analytics Database 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 Teradata Vantage™ - Analytics Database 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 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 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 Vantage 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:
- 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 Vantage 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)