GrantPrivileges Stored Procedure | Application Programming Reference| Vantage - 17.10 - GrantPrivileges - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1090-171K
Language
English (United States)

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

Syntax

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

Syntax Elements

UserName
User name of the grantee.
WithGrantOption
[Optional] Indicator that the WITH GRANT OPTION privilege is granted. Possible values are Y or N.
Default: 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 Information

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