FirebirdSQL logo

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;

ALTER PACKAGE

Alters a package header

Available in

DSQL

Syntax
ALTER PACKAGE package_name
[SQL SECURITY {INVOKER | DEFINER}]
AS
BEGIN
  [ <package_item> ... ]
END

!! See syntax of CREATE PACKAGE for further rules!!

The ALTER PACKAGE statement modifies the package header.It can be used to change the number and definition of procedures and functions, including their input and output parameters.However, the source and compiled form of the package body is retained, though the body might be incompatible after the change to the package header.The validity of a package body for the defined header is stored in the column RDB$PACKAGES.RDB$VALID_BODY_FLAG.

Altering a package without specifying the SQL SECURITY clause will remove the SQL Security property if currently set for this package.This means the behaviour will revert to the database default.

Who Can Alter a Package

The ALTER PACKAGE statement can be executed by:

  • Administrators

  • The owner of the package

  • Users with the ALTER ANY PACKAGE privilege