FirebirdSQL logo

SQL Security

The SQL SECURITY clause specifies the security context for executing other routines or inserting into other tables from functions or procedures defined in this package.When SQL Security is not specified, the default value of the database is applied at runtime.

The SQL SECURITY clause can only be specified for the package, not for individual procedures and functions of the package.

See also SQL Security in chapter Security.

Procedure and Function Parameters

For details on stored procedure parameters, see [fblangref50-ddl-proc-params] in [fblangref50-ddl-proc-create].

For details on function parameters, see [fblangref50-ddl-func-params] in [fblangref50-ddl-func-create].

Who Can Create a Package

The CREATE PACKAGE statement can be executed by:

The user who created the package header becomes its owner.

Examples of CREATE PACKAGE

  1. Create a package header

CREATE PACKAGE APP_VAR
AS
BEGIN
  FUNCTION GET_DATEBEGIN() RETURNS DATE DETERMINISTIC;
  FUNCTION GET_DATEEND() RETURNS DATE DETERMINISTIC;
  PROCEDURE SET_DATERANGE(ADATEBEGIN DATE,
      ADATEEND DATE DEFAULT CURRENT_DATE);
END
  1. With DEFINER set for package pk, user US needs only the EXECUTE privilege on pk.If it were set for INVOKER, either the user or the package would also need the INSERT privilege on table t.

    create table t (i integer);
    set term ^;
    create package pk SQL SECURITY DEFINER
    as
    begin
        function f(i integer) returns int;
    end^
    
    create package body pk
    as
    begin
        function f(i integer) returns int
        as
        begin
          insert into t values (:i);
          return i + 1;
        end
    end^
    set term ;^
    grant execute on package pk to user us;
    
    commit;
    
    connect 'localhost:/tmp/69.fdb' user us password 'pas';
    select pk.f(3) from rdb$database;