The way privileged system procedures run has changed with the enhanced role-based security.
In pre-16.0 versions of SAP Sybase IQ, a privileged system procedures run with the privileges of its owner, typically the dbo system role; this is called the SYSTEM PROCEDURE DEFINER model. In 16.0, privileged system procedures run with the privileges of the person executing it; this is called the SYSTEM PROCEDURE INVOKER model.
In the SYSTEM PROCEDURE DEFINER model, when you grant a user explicit EXECUTE object-level 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).
In the SYSTEM PROCEDURE INVOKER model, the EXECUTE object-level privilege for each system procedure is granted to the PUBLIC role. Since, by default, every user is a member of the PUBLIC role, every user automatically inherits the required EXECUTE object-level privilege. Any additional system privileges needed to run the system procedure are not inherited; you must now directly or indirectly grant them to the user.
This behavior change might create a loss of functionality on custom stored procedures and applications that explicitly grant EXECUTE object-level privilege on system procedures. For this reason, the default upgrade process for pre-16.0 database uses a combination of the two models. Privileged system procedures that existed in IQ versions earlier than 16.0 continue to run using the SYSTEM PROCEDURE DEFINER model, while any privileged system procedures introduced in 16.0 or later use the SYSTEM PROCEDURE INVOKER model.
You can override the default upgrade behavior so that all privileged system procedures for all versions use only the SYSTEM PROCEDURE INVOKER model if the potential loss of functionality is not a concern. If you are unsure of potential loss of functionality, upgrade you database to use the default behavior, then investigate. If you determine that it is not an issue, and you want all system procedures to run using the SYSTEM PROCEDURE INVOKER model, use the ALTER DATABASE statement to change the default security model. When you override the default upgrade behavior, the EXECUTE object-level privilege to run privileged system procedures is revoked from all users and roles and granted to the PUBLIC role.
To specify a security model, use the CREATE DATABASE statement, ALTER DATABASE UPGRADE statement, or the Initialization utility (iqinit).
Once a database is upgraded to SAP Sybase IQ 16.0, the default upgrade behavior for any subsequent upgrades (for example to 16.0 SP2, SP3, and so on) is to continue to use the current 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, rather than the owner of the procedure. In addition to EXECUTE object-level privilege on the system procedure, you must be granted additional system privileges that are specific to the system procedure.
When running privileged system procedures using the SYSTEM PROCEDURE DEFINER model, the DBO system role is typically the owner of the procedures. By default, the dbo system role is granted the SYS_AUTH_DBA_ROLE compatibility role. This ensures that the role is indirectly granted all privileges necessary to execute system procedures. Migrating the SYS_AUTH_DBA_ROLE compatibility role can result in the dbo system role losing the ability to execute privileged system procedures. See Implications of Migrating Compatibility Roles on System Roles for details.