GrantPrivileges Stored Procedure | Application Programming Reference| Vantage - 17.05 - GrantPrivileges - Teradata Database

Teradata Vantage™ - Application Programming Reference

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1090-170K

Grants the EXECUTE PROCEDURE and SELECT privileges on the TDSTATS database to the specified user.

Definition

REPLACE PROCEDURE TDSTATS.GrantPrivileges
 (IN UserName        VARCHAR(128) CHARACTER SET UNICODE,
  IN WithGrantOption CHAR(1) CHARACTER SET LATIN)
                       .
                       .
                       .
;

Input Parameters

Parameter Description
UserName User name of the grantee.
WithGrantOption Indicator that the WITH GRANT OPTION privilege is granted. Possible values are Y or N.
The default value is N.

Usage Notes

After the DIPSTATS script is run, user DBC (that is, the primary system user and owner or parent of all users, databases, and other objects) has all the privileges on the TDSTATS database, including the WITH GRANT OPTION privilege.

User DBC can call GrantPrivileges for users who need to call the TDSTATS external stored procedures and manually grant additional privileges (for example INSERT, DELETE, an UPDATE) on the TDSTATS database.

Granting the STATISTICS Privilege

To ... You should ...
call the following external stored procedures:
  • RunCollect or RecollectTable
  • AnalyzeStats or AnalyzeStatsUsage
Ensure you have permissions to grant the STATISTICS privilege.

The STATISTICS privilege on user objects is not granted as part of GrantPrivileges.

Issue the following command on all user objects that have the SQL COLLECT STATISTICS statements submitted on them (where user_object is the database name):

GRANT STATISTICS ON  user_objects  TO TDSTATS

This command is performed only once for a given object.

reduce the number of individual privileges that must be maintained in the dictionary perform the GRANT statement at the database level (that is, user_object) whenever possible.

Example: Using GrantPrivileges

The following example grants automated statistics management privileges to user PersonnelDBA.

.logon mysystem,dbc
CALL TDSTATS.GrantPrivileges ('PersonnelDBA','N');
*** Procedure has been executed.
*** Total elapsed time was 1 second

Related Topics

For more information on ... See ..
the DIPSTATS script
the GRANT, WITH GRANT OPTION, or STATISTICS privileges
  • Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100.
  • Teradata Vantage™ - SQL Data Control Language, B035-1149.
calling RunCollect or RecollectTable