Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » Stored procedures, triggers, batches, and user-defined functions » Procedures » Set procedures and functions to run with owner or invoker privileges

 

Running pre-16.0 system procedures as invoker or definer

Some system procedures present in the software before version 16.0 that perform privileged tasks in the database, such as altering tables, can be run either with the privileges of the invoker, or of the definer (owner). When you create or initialize a database, you can specify whether you want these special system procedures to execute with the privileges of their owner (definer), or with the privileges of the invoker.

If the database is configured to run these system procedures in definer mode, the invoker does not need any additional privileges because the procedure runs with the privileges of the definer (typically the dbo or SYS role), who already has the required privileges.

If the database is configured to run in invoker mode, the invoker needs to have the privileges noted in the documentation for the procedure. The invoker also needs EXECUTE privilege on the procedure, but they inherit this by being a member of PUBLIC.

Note

The default behavior for user-defined procedures is not impacted by the invoker/definer mode. That is, if the definition of the user-defined procedure does not specify invoker or definer, then the procedure runs with the privileges of the definer.

You control how these system procedures are run at database creation or upgrade time using one of the following methods:

  • CREATE DATABASE...SYSTEM PROCEDURE AS DEFINER statement   Specifying CREATE DATABASE...SYSTEM PROCEDURE AS DEFINER OFF means that the database server enforces the privileges of the invoker. This is the default behavior for new databases.

    Specifying CREATE DATABASE...SYSTEM PROCEDURE AS DEFINER ON means that the database server enforces the privileges of the definer (owner). This was the default behavior in pre-16.0 databases.

  • ALTER DATABASE UPGRADE...SYSTEM PROCEDURE AS DEFINER statement   This clause behaves the same way as for the CREATE DATABASE statement. If the clause is not specified, the existing behavior of the database being upgraded is maintained. For example, when upgrading a pre-16.0 database, the default is to execute with the privileges of the definer.

  • -pd option, Initialization utility (dbinit)   Specifying the -pd option when creating a database causes the database server to enforce the privileges of the definer when running these system procedures. If you do not specify -pd, the default behavior is to enforce the privileges of the invoker.

  • -pd option, Upgrade utility (dbupgrad)   Specifying -pd Y when upgrading a database causes the database server to enforce the privileges of the definer when running these system procedures.

    Specifying -pd N causes the database server to enforce the privileges of the invoker when running these system procedures.

    If this option is not specified, the existing behavior of the database being upgraded is maintained.

Note

The PUBLIC system role is granted EXECUTE privilege for all system procedures. Newly created users are granted the PUBLIC role by default, so users already have EXECUTE privilege for system procedures.

The default for user-defined functions and procedures is unaffected by the invoker/definer decision. That is, even if you choose to run these system procedures as invoker, the default for user-defined procedures remains as definer.

 List of procedures that are impacted by the invoker/definer setting
 List of procedures that run with invoker privileges regardless of the invoker/definer setting
 See also

Determining the security model used by a database (SQL)

Copyright © 2013, SAP AG ou société affiliée SAP - SAP Sybase SQL Anywhere 16.0