データベースの権限の代わりになるロジックを考える

2024-01-23 記載
概要 : データベース権限が機能制限されている環境での代替策を考える
Keyword : MySQL, 権限, REVOKE, GRANT, SQL, phpMyAdmin, Trigger

データベースでは普通、ユーザーごとに権限を設定できますが、レンタルサーバーの提供するMySQLでは、諸都合により権限設定が機能制限されている場合があります。cloudfreeがそうです。
「できなくて行き詰る」ことはあっても「できすぎて行き詰る」ことはそうないので、さほど問題視されることはありませんけど。それに権限が使えないならユーザーロジックで制限かければすむことです。さらには使えない機能はフロントエンドでdisebledになっているのが昨今の当たり前ですから、データベースの権限が働くようではすでに作りがおかしいと言われかねません。

まぁそういう背景はさておき、データベースの権限的なものをロジックでいれるとしたら、どういう方法がスマートかについて考えてみました。

まず思いついたのは、phpMyAdminでは機能制限されていますが、SQLでは権限設定できるのでは？という可能性。

GRANT
REVOKE
といった命令が権限関係です。

REVOKE ALL PRIVILEGES ON cfsid_db.tb_target 
FROM 'cfsid_databaseuser'@'localhost';
GRANT SELECT ON cfsid_db.tb_target 
FROM 'cfsid_databaseuser'@'localhost';

一度すべての権限を取り消してから許可するものを与える、という手順を踏むようですが、localでできた設定をcloudfreeで試すと

エラー
静的解析:
解析中に 1 個のエラーが見つかりました。
 認識されないステートメント形式です。 (near "REVOKE" at position 0)
SQL クエリ: コピー

というエラーでダメでした。
LaravelならミドルウェアやModelの protected $guarded でなんとかならないかと考えるところですが、できればデータベース寄りなところで、と考えたのがトリガーです。

たとえば、あるテーブル tb_target に対して
特定ユーザー cfsid_cannotdeleteuser の
DELETEを禁止させる場合、その設定をインポートできるSQLで記述すると以下のようになります。

DELIMITER $$
 CREATE TRIGGER `reject_delete` BEFORE DELETE 
 ON `tb_target` 
 FOR EACH ROW 
 IF (SELECT USER() = 'cfsid_cannotdeleteuser@localhost') THEN
   SIGNAL SQLSTATE '45000' 
   SET MESSAGE_TEXT = 'TRIGGER-REJECT:DELETE禁止のユーザーです';
 END IF
$$
DELIMITER ;

これはcloudfreeで設定できて、phpMyAdminからの操作においても、きっちりブロックされました。SET MESSAGE_TEXT をきっちり書いておかないと、トリガーのことを自分でも忘れて、なぜSQLが通らないのか悩みそうなので、そこは注意がいるなと思いました。