Featured Database Articles

Secure Truncate Table Procedure

In applications, some users need privileges to truncate tables
in others' schemas.

Only the schema owner can truncate his/her owned tables.
Any user with DBA role granted can truncate tables in others' schemas.
Any user with "drop any table" system privilege can truncate tables in others' schemas.

Without granting DBA role or DROP ANY TABLE privilege to users, any user
can not truncate third party tables.

It is not recommended in Production environments to grant
DBA role or "drop any table" privilege to users so that the users can
truncate tables in others schemas.

The work around for this impasse is creating a procedure in
sys or system schema and grant execute privileges on the procedure to users.

Again, users having execute permission on the truncate procedure can
truncate any table in the database.

With added checks and privileges, this modified truncate table procedure is
more secure and sage in mission critical production environments.

This procedure need to be created in each schema.
The schema owner need to grant execute permission on the procedure to the user,
and the delete permission on specified tables also to the user.

Then the user can execute the procedure to truncate tables.

During the execution of the procedure, the procedure check whether the user
is having "DELETE" privilege on the specified table, the user is trying to truncate.
If the "DELETE" privilege is granted to the user, then the procedure truncates the table
under the schema. As the procedure is SCHEMA specific, only tables under a specific schema
can only be truncated. Also, any user trying to truncate tables, need to have execute
privilege on the specific schema's truncate table procedure. This way, the users are
calling a specific schema's truncate table procedure.

Disclaimer: We hope that the information on these script pages is
valuable to you. Your use of the information contained in these pages,
however, is at your sole risk. All information on these pages is provided
"as -is", without any warranty, whether express or implied, of its accuracy,
completeness, or fitness for a particular purpose...
Disclaimer Continued