0

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?

2 Answers 2

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.

1
  • Unfortunately there is no per-table DROP privilege. If there was, you could grant that and then define a before drop on dev02.schema trigger to prevent dev02 from actually dropping anything not in its own schema. I suppose you could grant drop any table and have a table in dev08 or in a central DBA schema listing who can truncate what table, and have the trigger check that. Commented Apr 30, 2023 at 10:24
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.