Granting Permissions for update statistics, delete statistics, and truncate table

The SAP ASE server allows you to grant permissions to users, roles, and groups for the update statistics, delete statistics, and truncate table commands. Table owners can also provide permissions through an implicit grant by adding update statistics, delete statistics, and truncate table to a stored procedure and then granting execute permissions on that procedure to a user or role.

You cannot grant permissions for update statistics at the column level. You must have the sso_role to run update statistics or delete statistics on sysroles, syssrvroles, and sysloginroles security tables.

By default, users with the sa_role have permission to run update statistics and delete statistics on system tables other than sysroles, syssrvroles, and sysloginroles, and can transfer this privilege to other users.

You can also issue grant all to grant permissions on update statistics, delete statistics, and truncate table.

Note: Once you grant permission to execute update statistics to a user, he or she also has permission to execute variations of this command, such as update all statistics, update partition statistics, update index statistics, update table statistics, and so on. For example, the following grants Billy permission to run all variations of update statistics on the authors table:

grant update statistics on authors to billy

If you revoke a user’s permission to execute update statistics, you also revoke his or her ability to execute the variations of this command.

You cannot grant variants of update statistics (for example, update index statistics) separately. That is, you cannot issue:

grant update all statistics to harry

You can, however, write stored procedures that control who executes these commands. For example, the following grants Billy execute permission for update index statistics on the authors table:

create proc sp_ups as
update index statistics on authors
go
revoke update statistics on authors from billy
go
grant execute on sp_ups to billy

You cannot grant and revoke delete statistics permissions at the column level.

Although the SAP ASE server audits truncate table as a global, miscellaneous audit, it does not audit update statistics. To retain clear audit trails for both truncate table and update statistics, you should include both commands in a stored procedure to which you grant users execute permission, as described above.