Skip to main content

Search

Welcome to Tibero GTS!

TRUNCATE Privilege Setting Guide

Document Type | Technical Information

Category | Administration

Applicable Product Version | 7FS02PS

Document Number | TADTI093

Overview

The TRUNCATE privilege is divided into privileges for all tables and privileges for specific tables, and separate parameter settings are required according to each privilege.
If the DROP ANY TABLE privilege is granted, TRUNCATE can be performed on all tables belonging to any schema regardless of the TRUNCATE parameter settings.
 

Method

Note

  • The USE_TRUNCATE_PRIVILEGE parameter can be set starting from Tibero5 r73542.
  • The USE_TRUNCATE_OBJ_PRIVILEGE parameter can be set starting from Tibero7.2.3.
  • In Tibero7.2.2 and earlier versions, all TRUNCATE privileges can be controlled with the USE_TRUNCATE_PRIVILEGE parameter.

 

1. TRUNCATE Privileges

  • TRUNCATE ANY TABLE: Privilege to perform TRUNCATE on all tables belonging to any schema.
  • TRUNCATE: Privilege to perform TRUNCATE on specific tables not belonging to one's own schema.

 

2. USE_TRUNCATE_PRIVILEGE Parameter Setting

This parameter must be enabled to grant or revoke the TRUNCATE ANY TABLE privilege.
The default setting is USE_TRUNCATE_PRIVILEGE = NO.

 

Checking the USE_TRUNCATE_PRIVILEGE Parameter 

    SQL> show parameter USE_TRUNCATE_PRIVILEGE
    NAME                           TYPE     VALUE
    ------------------------------ -------- ------
    USE_TRUNCATE_PRIVILEGE         Y_N      NO

In this state, the truncate any table privilege cannot be granted.
    SQL> grant truncate any table to testuser;
    TBR-7002: Unsupported DDL.

Also, the truncate any table privilege cannot be revoked.
    SQL> revoke truncate any table from testuser;
    TBR-7002: Unsupported DDL.

 

Setting the USE_TRUNCATE_PRIVILEGE Parameter to "YES" 

USE_TRUNCATE_PRIVILEGE is a dynamically applicable parameter and can be changed during operation.
    SQL> alter system set USE_TRUNCATE_PRIVILEGE = Y;
    System altered.

    SQL> show parameter USE_TRUNCATE_PRIVILEGE
    NAME                           TYPE     VALUE
    ------------------------------ -------- ------
    USE_TRUNCATE_PRIVILEGE         Y_N      YES

The truncate any table privilege is granted successfully to the testuser.
    SQL> grant truncate any table to testuser;
    Granted.

The truncate any table privilege is revoked successfully from the testuser.
    SQL> revoke truncate any table from testuser;
    Revoked.

 

3. USE_TRUNCATE_OBJ_PRIVILEGE Parameter Setting 

This parameter must be enabled to grant or revoke the truncate privilege on specific tables not belonging to one's own schema. 
The default setting is USE_TRUNCATE_OBJ_PRIVILEGE = NO.

 

Checking the USE_TRUNCATE_OBJ_PRIVILEGE Parameter 

    SQL> show parameter USE_TRUNCATE_OBJ_PRIVILEGE
    NAME                           TYPE     VALUE
    ------------------------------ -------- ------
    USE_TRUNCATE_OBJ_PRIVILEGE     Y_N      NO

In this state, the truncate privilege cannot be granted.
    SQL> grant truncate on testtable to testuser;
    TBR-7002: Unsupported DDL.

Also, the truncate privilege cannot be revoked.
    SQL> revoke truncate on testtable from testuser;
    TBR-7002: Unsupported DDL.

 

Setting the USE_TRUNCATE_OBJ_PRIVILEGE Parameter to "YES" 

USE_TRUNCATE_OBJ_PRIVILEGE is a dynamically applicable parameter and can be changed during operation.
    SQL> alter system set USE_TRUNCATE_OBJ_PRIVILEGE = Y;
    System altered.

    SQL> show parameter USE_TRUNCATE_OBJ_PRIVILEGE
    NAME                           TYPE     VALUE
    ------------------------------ -------- ------
    USE_TRUNCATE_OBJ_PRIVILEGE     Y_N      YES

The truncate privilege on the testtable is granted successfully to the testuser.
    SQL> grant truncate on testtable to testuser;
    Granted.

The truncate privilege on the testtable is revoked successfully from the testuser.
    SQL> revoke truncate on testtable from testuser;
    Revoked.

 

4. DROP ANY TABLE Privilege

  • DROP ANY TABLE: Privilege to drop all tables belonging to any schema.

If this privilege is granted to a schema, TRUNCATE can be performed on all tables belonging to any schema regardless of the USE_TRUNCATE_PRIVILEGE / USE_TRUNCATE_OBJ_PRIVILEGE parameter settings.

    SQL> show parameter TRUNCATE
    NAME                           TYPE     VALUE
    ------------------------------ -------- ------
    USE_TRUNCATE_PRIVILEGE         Y_N      NO
    USE_TRUNCATE_OBJ_PRIVILEGE     Y_N      NO

    SQL> truncate table tibero.t1;
    TBR-7071: Schema object 'TIBERO.T1' was not found or is invalid.

    SQL> conn sys/tibero
    Connected to Tibero.

    SQL> grant drop any table to testuser;
    Granted.

    SQL> conn testuser/testuser
    Connected to Tibero.

The truncate on tibero.t1 table is performed successfully.
    SQL> truncate table tibero.t1;
    Table 'TIBERO.T1' truncated.

 

 

 

Related to