🕺🏻 ⛓️ 🐝 MySQLに行レベルのセキュリティを実装する 💈 👨🏼‍⚕️ 🐔

こんにちはHabr！ mysqlとphpで行レベルセキュリティを含むビジネスプロセスを実装しました。

行レベルセキュリティまたは行レベルセキュリティ-データベース情報へのアクセスを制限するメカニズム。これにより、ユーザーはテーブル内の個々の行へのアクセスを制限できます。

なぜならほとんどの場合、Oracleでプログラムを作成するときに、これをデータベースに実装するのが最適であると判断しました。

MySQL 5.1.73には 、トリガー、ビュー、ストアドファンクション、およびプロシージャが共有ホスティングにあります。

アプリケーションテーブルauth_users

CREATE TABLE `auth_users`

CREATE TABLE `auth_users` ( `conn_id` bigint(20) NOT NULL, `user_id` int(11) NOT NULL, `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`conn_id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='    ';

これは、PHPでの認証中に入力されます

 REPLACE auth_users SET conn_id = CONNECTION_ID(), user_id = :user_id

phpスクリプトの完了時にクリアされます

  public static function user_logout(){ //   auth_users app()->db->query("DELETE FROM auth_users WHERE conn_id = CONNECTION_ID()"); } ... register_shutdown_function(array('Auth', 'user_logout'));

データスキーマの例：

組織のディレクトリ

CREATE TABLE `組織`

 CREATE TABLE `organizations` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `type` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''; INSERT INTO organizations (id, name, type) VALUES (1, ' ', ''), (2, ' ', '');

 SELECT * FROM organizations; +----+-----------------------------------+------------+ | id | name | type | +----+-----------------------------------+------------+ | 1 |   |  | | 2 |   |  | +----+-----------------------------------+------------+ 2 rows in set (0.00 sec)

アクセス設定：

Storekeeper No. 1 user_id = 1、モスクワ倉庫からドキュメントを表示、ノボシビルスク倉庫からドキュメントを表示および編集するアクセス権があります
Storekeeper№2user_id = 2は、ドキュメント「Warehouse Novosibirsk」を表示し、ドキュメント「Warehouse Moscow」を表示および編集するアクセス権を持ちます。
ディレクターuser_id = 3、ドキュメント「Warehouse Novosibirsk」および「Warehouse Moscow」を表示するアクセス権があります
Accountant user_id = 4、ドキュメント「Warehouse Novosibirsk」および「Warehouse Moscow」を表示および編集するためのアクセス権があります
マネージャーNo. 1 user_id = 5、ドキュメント「Warehouse Moscow」を表示するアクセス権があります
Manager No. 2 user_id = 6、ドキュメント「Warehouse Novosibirsk」を表示するアクセス権

CREATE TABLE `user_access`

 CREATE TABLE `user_access` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `warehouse_org_id` int(11) NOT NULL, `edit` tinyint(1), PRIMARY KEY (`id`), CONSTRAINT `user_access_ibfk_1` FOREIGN KEY (`warehouse_org_id`) REFERENCES `organizations` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' '; INSERT INTO user_access (user_id, warehouse_org_id, edit) VALUES (1, 1, NULL), (1, 2, 1); INSERT INTO user_access (user_id, warehouse_org_id, edit) VALUES (2, 1, 1), (2, 2, NULL); INSERT INTO user_access (user_id, warehouse_org_id, edit) VALUES (3, 1, NULL), (3, 2, NULL); INSERT INTO user_access (user_id, warehouse_org_id, edit) VALUES (4, 1, 1), (4, 2, 1); INSERT INTO user_access (user_id, warehouse_org_id, edit) VALUES (5, 1, NULL); INSERT INTO user_access (user_id, warehouse_org_id, edit) VALUES (6, 2, NULL);

 SELECT * FROM user_access; +----+---------+------------------+------+ | id | user_id | warehouse_org_id | edit | +----+---------+------------------+------+ | 1 | 1 | 1 | NULL | | 2 | 1 | 2 | 1 | | 3 | 2 | 1 | 1 | | 4 | 2 | 2 | NULL | | 5 | 3 | 1 | NULL | | 6 | 3 | 2 | NULL | | 7 | 4 | 1 | 1 | | 8 | 4 | 2 | 1 | | 9 | 5 | 1 | NULL | | 10 | 6 | 2 | NULL | +----+---------+------------------+------+ 10 rows in set (0.00 sec)

ドキュメントテーブルには、ウェアハウスフィールド（アクセスを区切る）およびその他のドキュメント属性が含まれます

CREATE TABLE `docs`

 CREATE TABLE `docs` ( `id` int(11) NOT NULL AUTO_INCREMENT, `warehouse_org_id` int(11) NOT NULL, `sum` int(11), PRIMARY KEY (`id`), CONSTRAINT `docs_ibfk_1` FOREIGN KEY (`warehouse_org_id`) REFERENCES `organizations` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' '; DELETE FROM docs; INSERT INTO docs (id, warehouse_org_id, sum) VALUES (1, 1, 10000), (2, 2, 5000);

 SELECT * FROM docs; +----+------------------+-------+ | id | warehouse_org_id | sum | +----+------------------+-------+ | 1 | 1 | 10000 | | 2 | 2 | 5000 | +----+------------------+-------+ 2 rows in set (0.00 sec)

それでは、RLSの設定を始めましょう。まず、ターゲットテーブルの名前を変更しますdocs- > t_docs

 ALTER TABLE docs RENAME t_docs;

同じ名前の編集可能なVIEWを作成します

 CREATE OR REPLACE VIEW docs AS SELECT id, warehouse_org_id, sum FROM t_docs WITH CHECK OPTION;

これで、クライアントアプリケーションからのすべてのリクエストはテーブルに直接アクセスするのではなく、VIEWにアクセスします。

重要！ システムにテーブルへのアクセスを制限する必要のない関数、プロシージャ、クエリがある場合は、テーブルを直接登録する必要があります。 t_docs たとえば、システム全体の負債/残高を計算する手順です。

次に、アクセスコントロールに従って表示を制限する簡単なことを行いましょう。

 CREATE OR REPLACE VIEW docs AS SELECT id, warehouse_org_id, sum FROM t_docs d WHERE EXISTS ( SELECT NULL FROM auth_users INNER JOIN user_access ON user_access.user_id = auth_users.user_id AND auth_users.conn_id = CONNECTION_ID() WHERE d.warehouse_org_id = user_access.warehouse_org_id ) WITH CHECK OPTION;

仕組みを見てみましょう。

 SELECT * FROM docs; Empty set (0.00 sec)

何も戻ってこなかった。実際、ログインする必要があります。マネージャー番号1の承認済みuser_id = 5

 DELETE FROM auth_users; REPLACE auth_users SET conn_id = CONNECTION_ID(), user_id = 5; SELECT * FROM docs; +----+------------------+-------+ | id | warehouse_org_id | sum | +----+------------------+-------+ | 1 | 1 | 10000 | +----+------------------+-------+ 1 row in set (0.00 sec)

彼は「倉庫モスクワ」という文書だけを見ています。ログインDirector user_id = 3

 DELETE FROM auth_users; REPLACE auth_users SET conn_id = CONNECTION_ID(), user_id = 3; SELECT * FROM docs; +----+------------------+-------+ | id | warehouse_org_id | sum | +----+------------------+-------+ | 1 | 1 | 10000 | | 2 | 2 | 5000 | +----+------------------+-------+ 2 rows in set (0.00 sec)

彼は文書「倉庫モスクワ」と「倉庫ノボシビルスク」を見ています！すべてが正常に機能しているようです。次に、より難しいタスク、つまり編集の制限に進みます。 Manager＃1 user_id = 5にログインして、ドキュメントを編集してみましょう。

 DELETE FROM auth_users; REPLACE auth_users SET conn_id = CONNECTION_ID(), user_id = 5; UPDATE docs SET sum = 20000 WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 UPDATE docs SET sum = 15000 WHERE id = 2; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0

表示されている行のみを更新しました。

しかし、表示および編集するためのさまざまな権利をどのように実現しますか？別のVIEW e_docsを追加できます

 CREATE OR REPLACE VIEW e_docs AS SELECT id, warehouse_org_id, sum FROM t_docs d WHERE EXISTS ( SELECT NULL FROM auth_users INNER JOIN user_access ON user_access.user_id = auth_users.user_id AND auth_users.conn_id = CONNECTION_ID() WHERE d.warehouse_org_id = user_access.warehouse_org_id AND user_access.edit = 1 ) WITH CHECK OPTION;

VIEWを介してすべてのDMLコマンドを許可しますが、これにはアプリケーションのすべてのDMLコマンドを書き換える必要があり、3つのオブジェクトがあります

t_docs-ソーステーブル
docs-表示するRLSテーブル
e_docs-編集用のRLSテーブル

より柔軟な別のオプションを試してみましょう。

現在のモードを表示するget_db_mode関数を作成しましょう-表示/編集

 DELIMITER $ CREATE FUNCTION get_db_mode() RETURNS VARCHAR(20) BEGIN IF @db_mode = 'edit' THEN RETURN 'edit'; ELSE RETURN 'show'; END IF; END $ DELIMITER ;

ビュー/編集モードで異なる行が表示されるようにVIEWを変更します

 CREATE OR REPLACE VIEW docs AS SELECT id, warehouse_org_id, sum FROM t_docs d WHERE EXISTS ( SELECT NULL FROM auth_users INNER JOIN user_access ON user_access.user_id = auth_users.user_id AND auth_users.conn_id = CONNECTION_ID() WHERE d.warehouse_org_id = user_access.warehouse_org_id AND (get_db_mode() = 'show' OR user_access.edit = 1 AND get_db_mode() = 'edit') ) WITH CHECK OPTION;

次に、BEFOREトリガーのDMLを使用して、 @db_mode変数を'edit'に設定し、AFTERトリガーの'show'に設定します

トリガーの作成

 DELIMITER $ CREATE TRIGGER `docs_bef_ins_trg` BEFORE INSERT ON `t_docs` FOR EACH ROW BEGIN SET @db_mode = 'edit'; END $ CREATE TRIGGER `docs_bef_upd_trg` BEFORE UPDATE ON `t_docs` FOR EACH ROW BEGIN SET @db_mode = 'edit'; END $ CREATE TRIGGER `docs_bef_del_trg` BEFORE DELETE ON `t_docs` FOR EACH ROW BEGIN SET @db_mode = 'edit'; END $ CREATE TRIGGER `docs_aft_ins_trg` AFTER INSERT ON `t_docs` FOR EACH ROW BEGIN SET @db_mode = 'show'; END $ CREATE TRIGGER `docs_aft_upd_trg` AFTER UPDATE ON `t_docs` FOR EACH ROW BEGIN SET @db_mode = 'show'; END $ CREATE TRIGGER `docs_aft_del_trg` AFTER DELETE ON `t_docs` FOR EACH ROW BEGIN SET @db_mode = 'show'; END $ DELIMITER ;

出来上がり、すべてがどのように機能するかを確認します。

Storekeeper No. 1として承認されていますuser_id = 1

 DELETE FROM auth_users; REPLACE auth_users SET conn_id = CONNECTION_ID(), user_id = 1; SELECT get_db_mode(); +---------------+ | get_db_mode() | +---------------+ | show | +---------------+ 1 row in set (0.00 sec) SELECT * FROM docs; +----+------------------+-------+ | id | warehouse_org_id | sum | +----+------------------+-------+ | 1 | 1 | 20000 | | 2 | 2 | 5000 | +----+------------------+-------+ 2 rows in set (0.00 sec) UPDATE docs SET sum = 105000 WHERE id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 SELECT get_db_mode(); +---------------+ | get_db_mode() | +---------------+ | show | +---------------+ 1 row in set (0.00 sec) SELECT * FROM docs; +----+------------------+--------+ | id | warehouse_org_id | sum | +----+------------------+--------+ | 1 | 1 | 20000 | | 2 | 2 | 105000 | +----+------------------+--------+ 2 rows in set (0.01 sec) UPDATE docs SET sum = 205000 WHERE id = 1; ERROR 1369 (HY000): CHECK OPTION failed '3006309-habr.docs'

素晴らしい、閲覧できますが、編集はできません。しかし、すべてがスムーズではありません。

 SELECT get_db_mode(); +---------------+ | get_db_mode() | +---------------+ | edit | +---------------+ 1 row in set (0.00 sec)

エラーの後、AFTERトリガーは機能せず、編集モードを削除しませんでした。 SELECTを実行すると、編集可能な行のみが表示されます。

 SELECT * FROM docs; +----+------------------+--------+ | id | warehouse_org_id | sum | +----+------------------+--------+ | 2 | 2 | 105000 | +----+------------------+--------+ 1 row in set (0.00 sec)

1つの解決策は試してみてください... PHPでPDOをキャッチし、任意のエラーに対してSET @db_mode = 'show'を強制します

テストオブジェクトを削除するためのスクリプト

 DROP TABLE IF EXISTS auth_users; DROP TABLE IF EXISTS organizations; DROP TABLE IF EXISTS user_access; DROP TABLE IF EXISTS docs; DROP TABLE IF EXISTS t_docs; DROP VIEW IF EXISTS docs; DROP FUNCTION IF EXISTS get_db_mode;

現在、すべてのアクセス制御ロジックは、1つのVIEWで非常に簡単に記述できます。同じスキームを使用すると、INSERT / UPDATE / DELETE操作へのさまざまなアクセスを簡単に実装できます

MySQLに行レベルのセキュリティを実装する

More articles: