CakePHP 4部署至共享MySQL托管环境出现1142权限错误相关问题咨询

基础场景 #

• 本地完成CakePHP 4应用开发后，部署至服务商提供的共享MySQL托管运行环境
• 对自身归属的数据库具备完全操作权限，无MySQL主库访问权限；同环境内并行运行其他客户不同域名对应的独立数据库，无权限查看这些数据库的内容
• 服务商已禁用phpMyAdmin的用户权限管理功能，仅能通过服务商管理面板创建仅被授予INSERT、UPDATE、SELECT、DELETE权限的数据库用户
• 在app_local.php中完成对应数据源参数配置后，通过系统users/add页面执行新增用户操作时触发报错，报错及配置中的敏感账号信息已用星号掩码标注

核心疑问 #

1. 是否需要更明确地指定当前执行操作所针对的目标数据库？
2. 是否可以配置框架在查询数据表时自动附带数据库名前缀，例如使用SELECT * FROM database_name.users替代SELECT * FROM users？当前报错未明确提示异常触发时所访问的数据库，仅展示执行指令、关联用户、目标表及对应报错的框架源码文件路径与行号
3. 在共享环境下开展操作时，是否需要额外指定schema配置项？

相关报错信息 #

2021-01-07 10:39:00 Error: [PDOException] SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user '******'@'localhost' for table 'users' in /home/multigra/domains/multi-grade.nl/public_html/content/vendor/cakephp/cakephp/src/Database/Statement/MysqlStatement.php on line 39

现有数据库配置 #

'default' => [
        'host' => 'localhost',
        /*
         * CakePHP will use the default DB port based on the driver selected
         * MySQL on MAMP uses port 8889, MAMP users will want to uncomment
         * the following line and set the port accordingly
         */
        'port' => '3306',

        'username' => '******',
        'password' => '******',

        'database' => '******',
        /**
         * If not using the default 'public' schema with the PostgreSQL driver
         * set it here.
         */
        //'schema' => 'myapp',

        /**
         * You can use a DSN string to set the entire configuration
         */
        'url' => env('DATABASE_URL', null),
    ],

问题解答 #

针对核心疑问的明确结论 #

1. 目标数据库指定要求
   • 必须明确、完整填写服务商分配给你的目标数据库全量名称。
   • 共享托管环境下，服务商通常会给每个用户的数据库名称加统一账号前缀（例如服务商域名前缀+自定义库名），如果仅填写自定义的短库名，框架无法正确路由到目标库，会回退连接到用户权限范围内的默认库，触发跨库访问的权限报错。
2. 数据库名前缀配置能力
   • *框架支持查询时自动附带数据库名前缀。*CakePHP的MySQL驱动原生支持全限定表名格式，可在对应Table类的初始化逻辑中，将表名设置为完整库名.表名结构，框架生成的所有查询都会自动附带该前缀，彻底避免未指定库名导致的跨库匹配问题。
   • 该方式可用于快速排查报错：如果配置全限定表名后查询正常，即可确认之前的默认库路由存在问题。
3. schema配置项要求
   • 共享MySQL环境下无需配置schema项。
   • schema为PostgreSQL驱动的专属配置项，MySQL驱动下该配置不会生效，无论独立环境还是共享托管环境，使用MySQL时都不需要设置该参数。

1142权限报错的排查方向 #

• 报错本质为MySQL执行查询时，未从用户拥有权限的目标库中定位users表，而是在无权限的其他库（MySQL默认系统库、同环境其他客户的独立库）中匹配到同名表，因此抛出权限拒绝提示。
• 排查步骤如下：
  • 逐字符核对服务商管理面板显示的目标数据库完整名称，与配置文件中的database参数值完全匹配，避免存在缺前缀、拼写错误的问题。
  • 确认配置使用的数据库用户，为服务商为该目标数据库单独创建的绑定用户，而非全局公共用户。
  • 若参数匹配仍报错，可在数据库配置中新增初始化命令参数，在连接建立后第一时间切换到目标库，避免默认库路由异常：

    'options' => [
        \PDO::MYSQL_ATTR_INIT_COMMAND => 'USE 你的目标数据库完整名称'
    ]
    

内容的提问来源于stack exchange，提问作者Philip Lafeber