Truncate control tables

Posted: February 26, 2014 in Database Development

The statement is often made that no individual should have rights to truncate a table. However, many data warehouse processes run updates by doing just that. The process truncates the table and re-populates it with updated data.

So, one solution is to create a truncate control table and populate it with schema_name, table_name, user, sysdate, and comments. You then can create a “TRUNCATE_TABLE” procedure that only truncates the tables that an individual user has permissions to truncate in the TRUNCATE_CONTROL table.

I ran into a snag when I went to do this on one of my procedures. I inserted a line into the TRUNCATE_CONTROL table and gave the user execute privileges on the procedure. When I compiled the procedure, I got “insufficient privileges.” What I discovered is that, yes the user had the necessary privileges to truncate the table but the schema did not. So I needed to grant execute on the TRUNCATE_CONTROL procedure to the schema owner…

grant execute on truncate_table to schema_name;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s