- You can specify any combination of privileges. However, the user submitting the statement must have the specified privileges in order to grant them.
- The recipient of a privilege can perform the corresponding statement on the object for which the privilege was granted.
For example, if user_1 receives the CREATE TABLE privilege on database DbTest, user_1 can then perform a CREATE TABLE statement in which the new table is directed to DBTest, where the target database is resolved either implicitly, as determined by the default database for user1, or explicitly with a fully qualified table name.
- The CREATE DATASET SCHEMA privilege can only be granted at the database level. DROP DATASET SCHEMA and WITH DATASET SCHEMA can be granted at the database or at the individual schema level.
- The STATISTICS privilege authorizes COLLECT STATISTICS statements. STATISTICS can be granted at the table and database levels.
- The CHECKPOINT privilege refers to the execution of the SQL statement and the Host Utilities (HUT) command.
The DUMP and RESTORE privileges refer to the corresponding HUT command performed on the specified object.
RESTORE also refers to execution of the HUT commands ROLLBACK, ROLLFORWARD, and DELETE JOURNAL.
- The CREATE forms of DATABASE, FUNCTION, MACRO, TABLE, VIEW, PROCEDURE, or USER, and the DROP forms of DATABASE and USER are allowed only on databases or users.
- DROP TABLE authorizes the ALTER TABLE, CREATE INDEX, and the DROP INDEX statements on the tables.
- DROP MACRO, PROCEDURE, or VIEW includes REPLACE MACRO, PROCEDURE, or VIEW.
- Only DROP MACRO and EXECUTE are allowed on macros.
- Only ALTER PROCEDURE, DROP PROCEDURE, EXECUTE PROCEDURE, and CREATE OWNER PROCEDURE are allowed on procedures.
- CREATE OWNER PROCEDURE is an explicit privilege that must be granted explicitly to a user or database.
- ALTER EXTERNAL PROCEDURE is required to change the execution state of an external procedure between the PROTECTED and NOT PROTECTED states using the ALTER PROCEDURE (External Form) statement.
See “ALTER PROCEDURE (External Form)” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
- Only ALTER FUNCTION, DROP FUNCTION, and EXECUTE FUNCTION are allowed on UDFs.
- You cannot grant the ALTER FUNCTION privilege on a UDF.
- DUMP, RESTORE, INDEX, and CHECKPOINT are not allowed on views.
- DATABASE, FUNCTION, INDEX, GLOP, MACRO, PROCEDURE, PROFILE, ROLE, TABLE, USER, and VIEW confer CREATE and DROP privileges.
If the DATABASE, FUNCTION, INDEX, GLOP, MACRO, PROCEDURE, PROFILE, ROLE, TABLE, USER, or VIEW keyword is specified without CREATE or DROP, CREATE and DROP are assumed by default.
If the target of USER is a user, then USER confers CREATE and DROP privileges. If the target of USER is a database, then USER confers the privilege to CREATE users within the database.
- INDEX authorizes the CREATE INDEX and DROP INDEX statements on the tables.
- INSERT, REFERENCES, SELECT, and UPDATE have both table-level and column-level options.
- You cannot grant the UPDATE privilege on a GENERATED BY DEFAULT identity column.
- You cannot grant privileges on a trigger, only on the database or table to which the trigger applies.
The CREATE TRIGGER and DROP TRIGGER privileges are granted to a user, either on the specified database or on the subject table of the trigger.
- You can grant CREATE PROFILE, DROP PROFILE, CREATE ROLE, DROP ROLE, CREATE ZONE, DROP ZONE, and ZONE OVERRIDE privileges to users only, not to roles, databases, or PUBLIC.
- CREATE PROFILE, DROP PROFILE, CREATE ROLE, DROP ROLE, CREATE ZONE, DROP ZONE, and ZONE OVERRIDE are all system-level privileges; you grant the privileges to a user, but not on a specific object.
- The ZONE privilege (shorthand for the CREATE ZONE and DROP ZONE privileges) cannot be combined with any other privilege when you use GRANT. Similarly, the ZONE OVERRIDE privilege cannot be combined with any other privilege when you use GRANT. For example, you cannot use syntax such as GRANT ZONE, ZONE OVERRIDE to u1; or GRANT ROLE, ZONE to u1; these commands fail.
- A GRANT or REVOKE request for the CONSTRAINT ASSIGNMENT, CONSTRAINT DEFINITION, and CTCONTROL system privileges can only be submitted by user DBC or by a user who has previously been granted the privilege WITH GRANT OPTION.
You cannot grant these privileges to PUBLIC or to EXTUSER.
- The name of the user or role you specify when you grant the CONSTRAINT ASSIGNMENT, CONSTRAINT DEFINITION, or CTCONTROL privilege must be a unique identifier assigned to an existing user or role. It cannot be PUBLIC or EXTUSER.
- The SHOW privilege enables a user to view the definition (using a SHOW object_type request), or request help about definitions and other information (using a HELP object_type request) for a database object without also being able to change the definition of the object or retrieve rows from it.
SHOW, which must be granted explicitly, can be granted at the object and database levels. The creator of an object does not receive the SHOW privilege automatically on the object it creates. User DBC can also grant this privilege on the dictionary tables to other users/databases.
SHOW CONSTRAINT requires CONSTRAINT ASSIGNMENT or CONSTRAINT DEFINITION privilege.
You can use SHOW to provide access to statements such as SHOW TABLE, HELP TABLE, and HELP STATISTICS that require any privilege of any kind. All statements that require any privilege can also use SHOW.
This privilege enables DBAs to grant a user the ability to SHOW a table or view, or do HELP STATISTICS, and so on without having SELECT access to a table.
The following rules apply to using the privilege keywords: