Grants the EXECUTE PROCEDURE and SELECT privileges on the TDSTATS database to the specified user.
REPLACE PROCEDURE TDSTATS.GrantPrivileges (IN UserName VARCHAR(128) CHARACTER SET UNICODE, IN WithGrantOption CHAR(1) CHARACTER SET LATIN) . . . ;
|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.
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:
||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
|For more information on ...||See ..|
|the DIPSTATS script||
|the GRANT, WITH GRANT OPTION, or STATISTICS privileges||
|calling RunCollect or RecollectTable|