Skip to main content

Search

Welcome to Tibero GTS!

Grant TRUNCATE Privilege on All Tables (TRUNCATE ANY TABLE)

Document Type | Technical Information

Category | Administration

Document Number | TADTI088

 

Overview

USE_TRUNCATE_PRIVILEGE parameter, when enabled, allows users to be granted the TRUNCATE privilege on all tables. Users granted the TRUNCATE ANY TABLE privilege can directly delete (TRUNCATE) data from all tables.
Note
The USE_TRUNCATE_PRIVILEGE parameter setting is available starting from Tibero 5 r73542 version and above. 
 
 

Method

Setting and Verifying TRUNCATE ANY TABLE Privilege

SQL> conn sys
Enter Password: ****
Connected to Tibero.

SQL> create table testtable_a (a number);
Table 'TESTTABLE_A' created.
SQL> insert into testtable_a values (10);
1 row inserted.
SQL> create table testtable_b (b number);
Table 'TESTTABLE_B' created.
SQL> insert into testtable_b values (20);
1 row inserted.
SQL> commit;
Commit completed.
SQL> grant select on testtable_a to testuser;
Granted.
SQL> grant select on testtable_b to testuser;
Granted.

Verify that the test user can query sys.testtable_a and sys.testtable_b.
SQL> conn testuser
Enter Password: ****
Connected to Tibero.

SQL> select * from sys.testtable_a;
A
----------
10
1 row selected.

SQL> select * from sys.testtable_b;
B 
----------
20
1 row selected.

Attempting to truncate the tables in this state results in the following error:
SQL> truncate table sys.testtable_a;
TBR-17004: Permission denied.
SQL> truncate table sys.testtable_b;
TBR-17004: Permission denied.

 

Retry After Granting TRUNCATE ANY TABLE Privilege and Verify Proper Operation

Below, you can confirm that truncate operations work correctly.
SQL> conn sys
Enter Password: ******
Connected to Tibero.

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

SQL> grant truncate any table to testuser; 
Granted.

SQL> conn testuser
Enter Password: ****
Connected to Tibero.

SQL> select * from sys.testtable_a;
A
----------
10
1 row selected.

SQL> select * from sys.testtable_b;
B
----------
20

1 row selected.

SQL> truncate table sys.testtable_a; 
Table 'SYS.TESTTABLE_A' truncated. 
SQL> select * from sys.testtable_a; 
0 row selected.
SQL> truncate table sys.testtable_b;
Table 'SYS.TESTTABLE_B' truncated. 
SQL> select * from sys.testtable_b; 
0 row selected.

 

 

 

Related to