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.NoteTheUSE_TRUNCATE_PRIVILEGEparameter 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.