Skip to content
  • Sujatha Sivakumar's avatar
    39ab6439
    Bug#20049894: AUTOMATIC_SP_PRIVILEGES ARE NOT CORRECTLY · 39ab6439
    Sujatha Sivakumar authored
    REPLICATED WHEN SETTING DEFINER
    
    Problem:
    ========
    Privileges created by automatic_sp_privileges are not
    correctly replicated when the procedure is created by a
    super user using DEFINER.
    
    Analysis:
    ========
    when automatic_sp_privileges variable is set, the server
    automatically grants the EXECUTE and ALTER ROUTINE
    privileges to the creator of a stored routine, if the user
    cannot already execute and alter or drop the routine. In
    existing code when a privileged user creates a procedure
    with DEFINER as a non privileged user on master the current
    user is considered as privileged user and mysql.procs_priv
    table will not be updated. Where as when the same statement
    is replicated to slave, on slave the non privileged DEFINER
    is considered as current user and privileges are getting
    allocated. Hence there is a difference with respect to the
    privileges that are allocated on master and slave. On slave
    the creator of the stored routine should be considered
    first.
    
    Fix:
    ===
    Binlog the creator of the stored routine along with the
    query. With this on the slave first check if the creator
    user exists on slave or not if available grant privileges to
    creator. For back ward compatibility we use definer when
    invoker is not available. We allow anonymous users to be
    replicated from master to slave. Existing code has a
    restriction of user.length > 0 which is not true in the case
    of anonymous users. That restriction is removed now.
    39ab6439
    Bug#20049894: AUTOMATIC_SP_PRIVILEGES ARE NOT CORRECTLY
    Sujatha Sivakumar authored
    REPLICATED WHEN SETTING DEFINER
    
    Problem:
    ========
    Privileges created by automatic_sp_privileges are not
    correctly replicated when the procedure is created by a
    super user using DEFINER.
    
    Analysis:
    ========
    when automatic_sp_privileges variable is set, the server
    automatically grants the EXECUTE and ALTER ROUTINE
    privileges to the creator of a stored routine, if the user
    cannot already execute and alter or drop the routine. In
    existing code when a privileged user creates a procedure
    with DEFINER as a non privileged user on master the current
    user is considered as privileged user and mysql.procs_priv
    table will not be updated. Where as when the same statement
    is replicated to slave, on slave the non privileged DEFINER
    is considered as current user and privileges are getting
    allocated. Hence there is a difference with respect to the
    privileges that are allocated on master and slave. On slave
    the creator of the stored routine should be considered
    first.
    
    Fix:
    ===
    Binlog the creator of the stored routine along with the
    query. With this on the slave first check if the creator
    user exists on slave or not if available grant privileges to
    creator. For back ward compatibility we use definer when
    invoker is not available. We allow anonymous users to be
    replicated from master to slave. Existing code has a
    restriction of user.length > 0 which is not true in the case
    of anonymous users. That restriction is removed now.
Loading