Skip to content
  • Dmitry Shulga's avatar
    b495301f
    Bug #25185947 -- ISSUES WITH RENAMING ROOT USER · b495301f
    Dmitry Shulga authored
    When a user root@localhost is renamed to any other name an attempt
    to run the statement SHOW DATABASE results in error:
    ERROR 1449 (HY000): The user specified as a definer ('root'@'localhost') does
    not exist
    
    The reason for the bug is that as of version 8.0 handling of SHOW DATABASE
    goes to INFORMATION_SCHEMA.SCHEMATA view, that is the real view.
    
    The view INFORMATION_SCHEMA.SCHEMATA is defined as SUID view with root@localhost as definer.
    Therefore, every time a view is accessed and the view is not already in the cache an
    attempt to compile the view is made. Since the view has an invalid definer (the definer
    user was renamed) opening the view fails.
    
    In order to fix the error a new system user 'mysql.infoschema'@localhost is introduced.
    This user specified as definer for every view in information_schema. The user
    'mysql.infoschema'@localhost is locked and have only grant to select from tables/views
    in information_schema. His sole role is just to be owner of views in information_schema.
    Note that we can't use name longer than 16 characters for this user since it will complicate
    privilege tables upgrade from 5.7 versions.
    
    The side effect of introducing a new user who is owner of system view in information_schema
    is that direct upgrade of privilege tables from 5.1 to 8.0 is no more possible.
    
    The approach this patch takes is a mid-term workaround, long-term solution should involve
    tracking view dependencies on users and prohibiting user renames if there are views
    dependent on them (or updating view definitions accordingly, see also WL6359
    'Implement dependency tracking for SQL objects').
    b495301f
    Bug #25185947 -- ISSUES WITH RENAMING ROOT USER
    Dmitry Shulga authored
    When a user root@localhost is renamed to any other name an attempt
    to run the statement SHOW DATABASE results in error:
    ERROR 1449 (HY000): The user specified as a definer ('root'@'localhost') does
    not exist
    
    The reason for the bug is that as of version 8.0 handling of SHOW DATABASE
    goes to INFORMATION_SCHEMA.SCHEMATA view, that is the real view.
    
    The view INFORMATION_SCHEMA.SCHEMATA is defined as SUID view with root@localhost as definer.
    Therefore, every time a view is accessed and the view is not already in the cache an
    attempt to compile the view is made. Since the view has an invalid definer (the definer
    user was renamed) opening the view fails.
    
    In order to fix the error a new system user 'mysql.infoschema'@localhost is introduced.
    This user specified as definer for every view in information_schema. The user
    'mysql.infoschema'@localhost is locked and have only grant to select from tables/views
    in information_schema. His sole role is just to be owner of views in information_schema.
    Note that we can't use name longer than 16 characters for this user since it will complicate
    privilege tables upgrade from 5.7 versions.
    
    The side effect of introducing a new user who is owner of system view in information_schema
    is that direct upgrade of privilege tables from 5.1 to 8.0 is no more possible.
    
    The approach this patch takes is a mid-term workaround, long-term solution should involve
    tracking view dependencies on users and prohibiting user renames if there are views
    dependent on them (or updating view definitions accordingly, see also WL6359
    'Implement dependency tracking for SQL objects').
Loading