Truncate tables in other schema

Question

I have logged into a dev02 schema with a user and password and another schema dev08 with a different user/password in the Oracle database. Now from dev02, I run the command: truncate table dev08.table_name I get ORA-01031: insufficient privileges error. What are the options I have here?

My thoughts:
I came across an article, from which I can create a procedure in dev08 to truncate the tables which I want which can be run from dev02.
Is there any alternative to this like granting permission to dev02 to truncate a list of tables? or creating a procedure is a good approach?

• Is there a way to grant truncate privileges on a single or a set of tables?

Answer 1

Is there a way to grant truncate privileges on a single or a set of tables?

Not directly, no.
The ability to truncate a table is not conferred by a separate privilege called "truncate". Because of the way truncate is implemented, it's actually a by-product of being able to DROP that table!

So you could do ...

GRANT DROP ON dev08.table_name TO dev02 ; 

... but don't!

If you could "make do" with deleting the rows rather than truncating the table, then you can use a sensible GRANT statement to confer this:

GRANT DELETE ON dev08.table_name TO dev02 ; 

Writing a stored procedure is probably your best option, but the code inside that procedure has to take charge of what it is and isn't allowed to truncate - being owned by the owner of the schema, it has full control over anything and everything in that schema.

Do not use [any of] the "ANY" privileges unless you really, really can't find a better alternative. They really do mean any schema, including all the built-in, System, ones that you don't want to be mucking about with.

Answer 2

To truncate tables directly in another schema, you must have the "ALTER ANY TABLE" privilege. Assuming you don't want to grant an "ANY" privilege because that would expose a lot more than what you want, then indirectly truncating them through a stored procedure is probably the best way to go:

Put the specific truncate commands you want to use into the procedure in dev08 (the schema that owns the tables) and grant execute on the procedure to the dev02 user. That limits the scope of the privilege to the reach of the API procedure within the dev08 schema.