Today I asked from the development team needs a user that already exists can do delete from other tables schemes. Until here all right. Suffice to give you a grant to delete the table you need to delete. the issue is that the tables where they need to delete are created and deleted dynamically, so if today we give permission to delete a table this table morning probably do not exist. And if a new table is created tomorrow, we will have to give the grant of delete to this new entity.
There are several solutions to this problem. So would be the most interesting;
- i) patch the code that creates the table so dynamic and that of the “grant on new_tab to delete user”.
- ii) create a job / scheduler every so often to check for new tables that give no permission to delete this user.
- iii) give permission “delete any table”, however this does not meet the ISO 9075 / ANSI SQL standard, and can be a risk of high security. Since you can delete any schema.
Finally, although I am not quite agree with the solution, has chosen the “Delete Any Table”.
HTH – Antonio NAVARRO