There is a problem with permissions on sp_move_file.
If sp_move_file() is not directly invoked but called from within a procedure, the READ FILE and WRITE FILE privileges of the role owning the procedure are not sufficient. You have to explicitly grant execute permissions on sp_move_file (and an internal, undocumented function sp_real_copy_file) to the procedure owner.
This fix would be ok for me if there wasn't a big problem with it: The granted execute permissions are NOT written into an unload file! IOW, after an unload/reload of the db, they are gone.
We just run into the problem in a production environment and some automatic interfaces relying on that type of code were down for a day before we found out what's going on.
To SAP support: shall we open a case for it or is this sufficient for you?
Tested with 16.0.2127.
asked 30 Nov '15, 08:18
Has that particular database been created with SYSTEM PROCEDURE AS DEFINER ON/OFF?
According to that list, sp_move_file() is impacted by that setting, so the database might follow the newer "system procedures as invoker" point of view...