Skip to main content

Search

Welcome to Tibero GTS!

Granting TRUNCATE Privilege on a Specific Table (TRUNCATE ON TABLE)

Document Type | Technical Information

Category | Administration

Document Number | TADTI088

 

Overview

USE_TRUNCATE_PRIVILEGE parameter can be enabled to grant users TRUNCATE privileges on specific tables. Users who have been granted the truncate on table privilege can directly delete (TRUNCATE) data from the respective table.
Note
The USE_TRUNCATE_PRIVILEGE parameter is configurable starting from Tibero 5 version r73542 and later.
 
 

Method

Setting and Verifying TRUNCATE ON TABLE Privilege

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

SQL> create table testtable (a number);
Table 'TESTTABLE' created.
SQL> insert into testtable values (10);
1 row inserted.
SQL> commit;
Commit completed.
SQL> grant select on testtable to testuser; 
Granted.

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

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

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

 

Retry After Granting TRUNCATE Privilege and Confirm Normal Operation

You can confirm that the truncate operation proceeds successfully as shown below.
Enter Password: ******
Connected to Tibero.

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

SQL> grant truncate on testtable to testuser; 
Granted.

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

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

SQL> truncate table sys.testtable; 
Table 'SYS.TESTTABLE' truncated. 
SQL> select * from sys.testtable;
0 row selected.

 

 

Related to