SA16 Permission problem with sp_move_file and missing permissions in unload

[Michael_Fischer]

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.

-- Beginning of sample code
create or replace role NewRole;

grant read file to NewRole;
grant write file to NewRole;

create or replace procedure NewRole.MoveFile(@fileName varchar(512))
sql security definer
begin
    declare sourcePath  varchar(1024);
    declare destPath    varchar(1024);
    declare result      int;

    set sourcePath = 'c:\\temp\\source\\' + @fileName;
    set destPath = 'c:\\temp\\destination\\' + @fileName;

    select dbo.sp_move_file(sourcePath, destPath) into result;
    message String('Move file result: ', result) type info to client;
end;

grant connect to NewUser;
grant execute on NewRole.MoveFile to NewUser;

-- The following call fails due to missing execute permissions on sp_move_file and then on sp_real_copy_file?
setuser NewUser;
call NewRole.MoveFile('test.txt');

-- Ok, fix it
setuser;
grant execute on sp_move_file to NewRole;
grant execute on sp_real_copy_file to NewRole;

-- Now it works
setuser NewUser;
call NewRole.MoveFile('test.txt');

[Former Member]

Not that this is a solution to the rebuild (unload) problem but the issue can maybe be address by specifing SQL SECURITY INVOKER might be another way to address this. The change of the security model away from SQL SECURITY DEFINER (aka OWNER) is part of this issue and so it may make sense to go that way (for this one procedure) instead; depending upon your purpose for this procedure. Of course your original choice is probably correct in this case.

As to what sounds like a bug, thanks for letting us know. If you want or need to get notification about the determination and any possible fix you should probably open up an incident for easier tracking for you.

[Michael_Fischer]

Nick,

thanks for the quick response. SQL Security Invoker is not an option for us here. We intensively use procs and views to restrict access to underlying objects and roles to group access to this procs an views.

Besides the unload issue, I think that if 1) a role has got the appropriate permissions to do something and 2) a proc owned by that role is defined with SQL Security Definer then the code inside the proc should run own the same permissions as the role.

Just my five cents, Michael

[VolkerBarth]

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...

[Former Member]

It appears that Volker has identified the main focus for this question.

As Volker has pointed out, the system procedure sp_real_copy_file( ) will always follow the create SQL SECURITY database settings (also see dbinit -pd and sp_proc_priv() ). This also explains the unload issue. System objects are not unloaded as SQL statments but created anew and that is also the reason the explicit grant execute permission is not preserved. That much explains the loss of the second grant execute (grant execute on sp_real_copy_file to NewRole;).

This part is a normal part of the unload process and something that would need to be addressed by the default SQL SECURITY setting for system procedures or explicitly as an extra step during rebuilds.

FWIW That is the only grant execute missing. In my test the first one is preserved:

GRANT EXECUTE ON "NewRole"."MoveFile" TO "NewUser" go