Colstats privileges???

UDA

Colstats privileges???

Hi

Could any one explain, what are the privileges(access rights) required to Collect Statistics on any Table?

Also, which is the exact DBC table to look in to for obtaining all the privileges one user has on all the objects ?

Thanks in adv.
10 REPLIES

Re: Colstats privileges???

Hi,
Drop Table Privilege is required to collect and drop statistics.
You can get privilege informations about a user from DBC.ALLRIGHTS view.

Rgds,
Aravind Hegde
Teradata Employee

Re: Colstats privileges???

You can also collect stats if you have the INDEX right (though that can only be granted on individual tables and not at the database level).

And if you are using roles, you will need to look at RoleMembers and AllRoleRights in addition to AllRights.

Re: Colstats privileges???

In case of join index ,(index or drop) privileges are required on both join index and base tables for collecting stats.
N/A

Re: Colstats privileges???

We have Teradata 12. Is there any way to give COLLECT STATS permission to ETL developers, with out using privileges such as DROP TABLE (too risky) or INDEX (maintenance headache since its on table level) ?
N/A

Re: Colstats privileges???

No in TD12, in TD13 there's a new STATISTICS right.

Of course you can easily encapsulate the COLLECT STATS in a Stored Procedure using Dynamic SQL. Then developers don't need specific rights.

Dieter
N/A

Re: Colstats privileges???

Thanks Dieter
Appreciate if you have algorithm or a blueprint for such SP,,it'll be very useful so many forum members
N/A

Re: Colstats privileges???

You just have to concat a "collect stats" string and submit it using dbc.SsyExecSQL like:
REPLACE PROCEDURE collectstats (IN DatabaseName VARCHAR(30), IN TableName VARCHAR(30), IN ColumnList VARCHAR(300))
BEGIN
DECLARE CollStatsStmt VARCHAR(400);
SET CollStatsStmt = 'COLLECT STATISTICS ON ' || TRIM(DatabaseName) || '.' || TRIM(TableName) || ' column('|| TRIM(columnlist) || ');';
CALL dbc.SysExecSQL(CollStatsStmt);
END;

Of course this is very basic, no error handling, no checking.

Btw, JK Wight posted a nice script for recollecting stats on a database level to the TeradataForum at:
http://www.teradataforum.com/attachments/a101126a.txt

Dieter

Re: Colstats privileges???

Dieter, I need a little help to understand.
How can we run a collect stats in a stored procedure without the user running it having the statistics permission?

Re: Colstats privileges???

OK, I tried it by submitting it using dbc.SsyExecSQL and it works, but I am not sure I understand why it works that way.