Feature #9089: Store Procedure: maybe it's better to use 'SECURITY=INVOKER' instead of ' 'SECURITY=DEFINER' - QFQ - Project @ iMath

Actions

Copy link

Feature #9089

closed

Store Procedure: maybe it's better to use 'SECURITY=INVOKER' instead of ' 'SECURITY=DEFINER'

Added by Carsten Rose over 4 years ago. Updated over 4 years ago.

Status:

Closed

Priority:

High

Assignee:

Carsten Rose

Target version:

19.9.1

Start date:

11.09.2019

Due date:

% Done:

100%

Estimated time:

Discuss:

Prio Planung:

Vote:

Description

Mit 'SQL SECURITY INVOKER' sollte das Problem, das die stored procedures nach einem Sync nicht ausgefuehrt werden duerfen, nicht mehr auftreten.
SECURITY=INVOKER: es werden die Berechtigungen des aktuellen Users genommen.
SECURITY=DEFINER: der Definer muss genuegend Berechtigungen haben. Bei einem Sync wird der User aber nicht mit kopiert.
https://mariadb.com/kb/en/library/stored-routine-privileges/#sql-security-clause

Beispiel MySQL Dump. Es geht um die Zeile 'SQL SECURITY INVOKER'

DELIMITER ;;
CREATE DEFINER=`admino`@`localhost` FUNCTION `QMORE`(input TEXT, maxlen INT) RETURNS text CHARSET utf8mb4
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
  DECLARE output TEXT;
  IF maxlen < 1 THEN
    SET maxlen = 1;
  END IF;
  IF CHAR_LENGTH(input) > maxlen THEN
    SET output = CONCAT(INSERT(input, maxlen, 0, '<span class="qfq-more-text">'), '</span>');
  ELSE
    SET output = input;
  END IF;
  RETURN output;
END ;;
DELIMITER ;

Actions

Copy link

Updated by Carsten Rose over 4 years ago

Tracker changed from Support to Feature

Actions

Copy link

Updated by Carsten Rose over 4 years ago

Subject changed from Store Procedure: maybe it's better to use 'SECURITY=DEFINER' instead of ' 'SECURITY=DEFINER' to Store Procedure: maybe it's better to use 'SECURITY=INVOKER' instead of ' 'SECURITY=DEFINER'

Actions

Copy link