Restrictions on Granted Privileges | GRANT (SQL Form) | Teradata Vantage - Restrictions on Granted 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

When you explicitly grant privileges to another user, database, role, or PUBLIC, there are rules that determine whether, how, and on what object the requested privilege can be implemented. The restrictions that apply to explicitly granted privileges are detailed in the following table.

The first column of the table lists the privilege type, the second column describes restrictions if the privilege is granted on a database, user, role, or PUBLIC, and the third column describes restrictions if the privilege is granted on a table, view, function, function mapping, procedure, method, UDT, or macro.

Privilege Object (Database, User, Role, PUBLIC) Table, View, Function, Function Mapping, GLOP, Method, UDT, Procedure, Constraint, or Macro
ALL All privileges the grantor can grant on an object.
  • Grants UDTMETHOD on the SYSUDTLIB database.

    If granted WITH GRANT OPTION, permits grantee to grant others UDTMETHOD, UDTTYPE, and UDTUSAGE, optionally WITH GRANT OPTION.

The following are true only if the grantor owns the privilege:
  • Grants EXECUTE and DROP on a macro.
  • Grants DROP, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, RESTORE, CREATE TRIGGER, DROP TRIGGER, and DUMP on a data table.
  • Grants DROP on hash and join indexes.
  • Grants DROP, DELETE, INSERT, SELECT, and UPDATE on a view.
  • Grants INSERT, DUMP, RESTORE, and CHECKPOINT on a journal table.
  • Grants ALTER PROCEDURE, DROP PROCEDURE, and EXECUTE PROCEDURE privileges on a procedure.
  • Grants ALTER FUNCTION, DROP FUNCTION, and EXECUTE FUNCTION privileges on a specified UDF.
  • Grants CREATE GLOP, GLOP, and DROP GLOP to a user.
  • Grants GLOP MEMBER to a specified user or database.
  • Grants NONTEMPORAL to a specified user, database, or table.

    For more information, see Teradata Vantage™ - Temporal Table Support, B035-1182.

ALTER FUNCTION

ALTER PROCEDURE

Privilege applies to all external UDFs or internal procedures in the specified space.

ALTER FUNCTION is not valid for UDFs.

Privilege applies to the specified external UDFs or procedures.

ALTER FUNCTION is not valid for UDFs.

ALTER EXTERNAL PROCEDURE Privilege applies to all external procedures in the specified space. Privilege applies to the specified external procedure.
CHECKPOINT Privilege applies to the journal table in the specified database. Privilege applies to the named journal table.
CONSTRAINT ASSIGNMENT   A system-level privilege granted to user DBC WITH GRANT OPTION. DBC can grant the privilege to any other user or role defined for your system.
CONSTRAINT ASSIGNMENT applies to the following SQL statements used to maintain constraint assignments for profiles, tables, and users.
  • ALTER TABLE
  • CREATE PROFILE
  • CREATE TABLE
  • CREATE USER
  • MODIFY PROFILE
  • MODIFY USER
  • SHOW CONSTRAINT
CONSTRAINT DEFINITION   A system-level privilege granted to user DBC WITH GRANT OPTION. DBC can grant the privilege to any other user or role defined for your system.
CONSTRAINT DEFINITION applies to executing the following SQL statements.
  • ALTER CONSTRAINT
  • CREATE CONSTRAINT
  • DROP CONSTRAINT
CREATE DATABASE

CREATE USER

CREATE granted for the specified space. Not applicable.
CREATE DATASET SCHEMA Grants CREATE DATASET SCHEMA on the SYSUDTLIB database.

A database-level privilege granted to user DBC WITH GRANT OPTION.

DBC must explicitly grant it with or without grant option to any other users or databases that are created. DBC must explicitly grant it without grant option to any role that is created.

Not applicable.
  • CREATE EXTERNAL PROCEDURE
  • CREATE FUNCTION
  • CREATE MACRO
  • CREATE PROCEDURE
  • CREATE TABLE
  • CREATE VIEW
CREATE granted for the object type for the specified space. Not applicable.
CREATE GLOP CREATE granted for the specified user.

This privilege is not granted automatically when a user or database is created.

  • CREATE MAP
  • CREATE PROFILE
  • CREATE ROLE
  • CREATE ZONE
Can only be granted to a user. Cannot be granted on an object. Can only be granted to a user. Cannot be granted on an object.
CREATE TRIGGER CREATE granted for the object type for the specified space. CREATE granted for the specified table.
CTCONTROL Privilege is a total system privilege and is not granted to or revoked from specific tables or databases.

CTCONTROL can only be granted to a user.

User DBC can grant CTCONTROL to any other user WITH GRANT OPTION.

  • DATABASE
  • FUNCTION
  • GLOP
  • INDEX
  • MACRO
  • MAP
  • PROCEDURE
  • PROFILE
  • ROLE
  • TABLE
  • TRIGGER
  • USER
  • VIEW
  • ZONE
CREATE and DROP granted for the type for the specified space.

CREATE MAP and DROP MAP are system-level privileges that can only be granted to users or roles. CREATE MAP and DROP MAP cannot be granted on an object or to PUBLIC.

CREATE PROFILE, DROP PROFILE, CREATE ROLE, DROP ROLE, CREATE ZONE, and DROP ZONE can only be granted to users. They cannot be granted on an object.

Not applicable.

TRIGGER applies to the table on which a trigger is defined.

DELETE

INSERT

SELECT

UPDATE

Privilege applies to all tables or views in the specified database.

UPDATE, INSERT, and SELECT apply to a table or column set of the table.

For a grantee to use the granted privileges on a view, the immediate owner of a view must have appropriate privileges on the tables and views referenced by the view.

Privilege applies only to the specified table, view, or columns.

UPDATE cannot be granted on a GENERATED ALWAYS identity column.

For a grantee to use the granted privileges on a view, the immediate owner of a view must have appropriate privileges on the tables and views referenced by the view.

DROP Not applicable. DROP granted for specified:
  • Procedures when PROCEDURE is specified before the procedure name or for the specified function.
  • Functions when FUNCTION or SPECIFIC FUNCTION is specified before the function name.
DROP DATABASE

DROP USER

DROP granted for the specified space. Not applicable.
DROP DATASET SCHEMA Grants DROP DATASET SCHEMA on the SYSUDTLIB database.

A database-level or schema-level privilege granted to user DBC WITH GRANT OPTION.

DBC must explicitly grant it with or without grant option to any other users or databases that are created. DBC must explicitly grant it without grant option to any role that is created.

The creator of a schema is automatically granted this privilege with grant option on the created schema.

Dropping a schema is only allowed if the schema is not in use.

Not applicable.
  • DROP FUNCTION
  • DROP MACRO
  • DROP PROCEDURE
  • DROP TABLE
  • DROP TRIGGER
  • DROP VIEW
DROP granted for the object type for the specified space.

DROP granted for the specified UDF, function mapping, macro, procedure, table, or view.

DROP TRIGGER applies to the table on which a trigger is defined.

DROP GLOP DROP granted for the specified GLOP set to the specified user or database.

This privilege is not granted automatically when a user or database is created.

This privilege is automatically granted to the creator and owner of the GLOP set.

DROP MAP

DROP PROFILE

DROP ROLE

DROP ZONE

Can only be granted to a user. Cannot be granted on an object. Can only be granted to a user. Cannot be granted on an object.
DUMP

RESTORE

Privilege applies to all tables in the specified database. Privilege applies to the named data table or journal table only.
EXECUTE Privilege applies to all UDFs or macros in the specified database.

For the grantee to use the privilege on a UDF or macro, the immediate owner of the macro or UDF must have appropriate privileges on the objects referenced by the macro or UDF.

Privilege applies to the specified macro if the keywords FUNCTION, SPECIFIC FUNCTION, or PROCEDURE are not specified.

For the grantee to use the privilege on a UDF or macro, the immediate owner of the macro must have appropriate privileges on the objects referenced by the macro.

Privilege applies to the specified UDFs or procedures if the keywords FUNCTION, SPECIFIC FUNCTION, or PROCEDURE are specified.

For a grantee to use the SCRIPT table operator, the database user executing the script query must have the following privileges:
  • The EXECUTE FUNCTION privilege on TD_SYSFNLIB.SCRIPT to enable its use.
  • The EXECUTE privilege to bind it to an appropriate OS user. SYSUIF.DEFAULT_AUTH is provided as an authorization object target for the EXECUTE privilege.
EXECUTE FUNCTION

EXECUTE PROCEDURE

Privilege applies to all UDFs or procedures in the specified space.

For the grantee to use the privilege on a procedure, the immediate owner of the procedure must have the appropriate privileges on the objects referenced by the procedure.

Privilege applies to the specified UDFs, function mappings, or procedures.

For the grantee to use the privilege on a procedure, the immediate owner of the procedure must have the appropriate privileges on the objects referenced by the procedure.

GLOP MEMBER Privilege enables an external routine to access the GLOP set specified in the MEMBER OF GLOP SET clause of its definition when that GLOP set is not contained within the containing user or database for the routine.

This privilege is not granted automatically when a user or database is created.

INDEX Not applicable. Privilege applies to tables, hash indexes, and join indexes.
NONTEMPORAL Privilege applies to a table, database, or user.

The privilege is not granted automatically and must be explicitly granted by user DBC or by a user who has been granted the privilege WITH GRANT OPTION.

For more information, see Teradata Vantage™ - Temporal Table Support, B035-1182.

Privilege applies at the table level.

For more information, see Teradata Vantage™ - Temporal Table Support, B035-1182.

  • OVERRIDE DELETE CONSTRAINT
  • OVERRIDE INSERT CONSTRAINT
  • OVERRIDE SELECT CONSTRAINT
  • OVERRIDE UPDATE CONSTRAINT
  Object-level privileges that can be granted to a table, database, column, or user.

If a row-level security function has not been created for a statement-action type, that type of statement can only be executed by a user who has the override privilege required to execute the statement.

If a user has the override privilege, the request must specify the values to be assigned to the constraint columns of the target rows.

You must have the CONSTRAINT ASSIGNMENT privilege to grant these privileges.
  • If the object is a table, the OVERRIDE privilege is granted on all of its constraint columns.
  • If the object is a database or user, the OVERRIDE privilege is granted on all of the constraint columns of all of the tables contained within that database or user.

A GRANT request that specifies these privileges must also specify the CONSTRAINT object on which the privilege is granted.

OVERRIDE DUMP CONSTRAINT

OVERRIDE RESTORE CONSTRAINT

OVERRIDE DUMP CONSTRAINT and OVERRIDE RESTORE CONSTRAINT can only be granted to users and roles. Object-level privileges that can be granted to a user or a role.
You must have the CONSTRAINT ASSIGNMENT privilege to grant these privileges.
  • If the object is a table, it must be defined with at least one row-level security constraint.
  • If the object is a user, it must contain at least one table that has one or more row-level security constraints.
    It is not necessary for a database to have a table that contains one or more row-level security constraints.
REFERENCES Not applicable. Privilege applies on a table or column of the table.
SHOW Not applicable. Privilege applies on a table, hash or join index, or database.

Privilege does not allow the grantee the ability to perform any operations on the granted database object other than to make a HELP or SHOW request against it.

STATISTICS Not applicable. Privilege to collect statistics on a table, hash index, join index, or database.
UDTMETHOD Can only be granted on the SYSUDTLIB database.

Privilege applies to all UDTs, methods, and UDFs contained within the SYSUDTLIB database.

Effectively grants UDTUSAGE on all UDTs contained within SYSUDTLIB as well as UDTTYPE on the SYSUDTLIB database.

Privilege applies to the specified UDT object.
  • Grants ability to access a UDT column in a table or view.
  • Grants CREATE TYPE, ALTER TYPE, and DROP TYPE including method signatures.
  • Grants CREATE ORDERING and DROP ORDERING.
  • Grants CREATE CAST and DROP CAST.
  • Grants CREATE TRANSFORM and DROP TRANSFORM.
  • Grants CREATE TABLE for UDT columns.
  • Grants ability to reference any UDT in a UDF or procedure.
  • Grants ability to execute all methods.
  • Grants CREATE METHOD, ALTER METHOD, and REPLACE METHOD.
Not applicable.
UDTTYPE Can only be granted on the SYSUDTLIB database.

Privilege applies to all UDTs, methods, and UDFs contained within the SYSUDTLIB database.

Effectively grants UDTUSAGE on all UDTs contained within the SYSUDTLIB database.

Privilege applies to the specified UDT object.
  • Grants ability to access a UDT column in a table or view.
  • Grants CREATE TYPE and ALTER TYPE only if no method signatures are specified.
  • Grants DROP TYPE.
  • Grants CREATE ORDERING and DROP ORDERING.
  • Grants CREATE CAST and DROP CAST.
  • Grants CREATE TRANSFORM and DROP TRANSFORM
  • Grants CREATE TABLE for UDT columns.
  • Grants ability to reference any UDT in a UDF or procedure.
  • Grants ability to execute all methods.
Not applicable.
UDTUSAGE Can be granted on the following:
  • SYSUDTLIB database.
  • TYPE.

Privilege applies to all UDTs, methods, and UDFs contained within the SYSUDTLIB database.

Privilege applies to the specified UDT object.
  • Grants ability to access a UDT column in a table or view.
  • Grants ability to execute all methods associated wholly with the specified UDT, but no others.
WITH DATASET SCHEMA A database-level or schema-level privilege granted to user DBC WITH GRANT OPTION.

DBC must explicitly grant it with or without grant option to any other users or databases that are created. DBC must explicitly grant it without grant option to any role that is created.

The creator of a schema is automatically granted this privilege with grant option on the created schema.

Gives users permission to associate a created schema with a table column.