Granting developers truncate on Oracle tables

One of those occasional grants that you really wish you could grant. You have a large temporary table that the Devs are using for testing developments, but it’s not in a schema they control. They need to clean it down regularly, but you don’t want them blowing up loads of redo logging by using deletes. So letting them truncate the table sounds like the best idea.

Except in Oracle that’s a grant you can’t grant. As far as Oracle’s concerned truncating a table is the same as dropping a table. So you either need to grant the Developer ‘DROP ANY TABLE’ or ‘DBA’, neither of which are what you want to do as they grant them the ability to drop anything in the instance, or do even more damage.

So, the workaround? Well, create a stored procedure owned by the table owner which truncates the table, and grant

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

No comments yet.

Leave a Reply