As part of the enhanced security of role-based security, the way in which privileged system procedures run has changed. Pre-16.0, a privileged system procedure ran with the privileges of its owner, typically dbo, and is referred to as the SYSTEM PROCEDURE DEFINER model. With 16.0, privileged system procedures run with the privileges of the person executing it, and is referred to as the SYSTEM PROCEDURE INVOKER model.
In pre-16.0, with the SYSTEM PROCEDURE DEFINER model, when you grant a user explicit EXECUTE privilege on a system procedure, any privileges required to run any authorized tasks associated with the system procedure are automatically inherited from the owner (definer of the system procedure), allowing the user to successfully run the system procedure.
In 16.0, with the SYSTEM PROCEDURE INVOKER model, the EXECUTE privilege for each system procedure is now granted to the PUBLIC role. Since every user, by default, is a member of the PUBLIC role, every user automatically inherits the required EXECUTE privilege. What is not inherited with the grant of EXECUTE privilege are any associated privileges required to run system procedure. These must now be granted directly or indirectly to the user before he or she can successfully run a system procedure.
This behaviour change has the potential to cause loss of functionality on custom stored procedures and applications that explicitly grant EXECUTE privilege on system procedures. For this reason, a default upgrade of a pre-16.0 database uses a combination of the two models. In the combination model, pre-16.0 privileged system procedures continue to run using the SYSTEM PROCEDURE DEFINER model, while any privileged system procedures introduced with 16.0 (or any future release) use the SYSTEM PROCEDURE INVOKER model.
If the potential loss of functionality is not of concern to your installation, you can override the default upgrade behavior so that all privileged system procedures (pre-16.0, new, and any future releases) use the SYSTEM PROCEDURE INVOKER model only. If you are unsure whether the potential loss of functionality will impact your database, upgrade using the default behavior and investigate. If you determine after the fact that it is not an issue, and you want to run all system procedures using the SYSTEM PROCEDURE INVOKER model, you can use the ALTER DATABASE statement to change the default security model.
The CREATE DATABASE statement, ALTER DATABASE UPGRADE statement, and Initialization utility (iqinit) have been enhanced to allow specification of a security model.
There is a small subset of pre-16.0 privileged system procedures that has always run with the privileges of the user running the procedure, not the owner of the procedure. To run these system procedures, in addition to requiring EXECUTE privilege on the system procedure, the user must be granted additional system privileges specific to the system procedure. Refer to the documentation for the required system privileges. This behavior remains unchanged in 16.0, regardless of the security model setting.