表の一部の列を編集不可能にする方法
表のデータ操作を制限する方法としては GRANT ステートメントによる表単位の制限、
CREATE VIEW WITH CHECK OPTION による行集合の Window ベースによる更新制限があるが、、どちらを使用しても列ベースの編集を制限できない。(※)
(※) エンタープライズ・エディションには仮想プライベート・データベース（Virtual Private Database) による行および列単位のアクセス制御がある。
列マスクポリシーは Oracle 10g R2 EE からの機能 で SELECT のみ有効。SELECT でマスクされたものは UPDATE できない。
更新不可能なビューを使い更新と挿入を制限する
実現する方法には BEFORE トリガーを使う方法もあるが、ここではスタンダード・エディションでもできる 更新可能なビュー に関する制限事項を利用して編集の制限をする一風変わった方法を行ってみる（ハッシュしたパスワード項目などに有効かもしれませんが、あまりお勧めはできない）。VPD のように ファイングレイン監査 レベルの柔軟な DML 制御はできない ＆ SQLの条件述語解析のように ビュー解析 のオプティマイザが賢くなって無意味な加工項目が編集できるようになっても知りません。 
以下のテーブルレイアウトでやってみる。
行番号
編集可 | 永続会員番号
編集不可 | 会員名
編集可 | 入会日時
編集不可 |
| 1 | XXX-0001 | 永久 太郎 | 2000-01-01 |
| 2 | XXX-0003 | 三日 寝太郎 | 2000-03-03 |
SQL> CREATE TABLE CUSTOMER (
SNO NUMBER,
ID VARCHAR2(8),
NAME VARCHAR2(16),
CREATED_ON DATE
);
表が作成されました。
SQL> INSERT INTO CUSTOMER VALUES(1,'XXX-0001', '永久 太郎', DATE '2000-01-01');
1行が作成されました。
SQL> CREATE OR REPLACE VIEW RIVUS.REST_CUSTOMER AS
SELECT
SNO, ID||'' AS ID, NAME, CREATED_ON+0 AS CREATED_ON
-- ↑この２箇所で仮想列に変換 ↑
FROM CUSTOMER;
ビューが作成されました。
列の UPDATE が制限されたか確認
SQL> UPDATE REST_CUSTOMER SET SNO=SNO+1;
1行が更新されました。
-- SNO 列の更新は可能
SQL> UPDATE REST_CUSTOMER SET ID='DUMMY';
UPDATE REST_CUSTOMER SET ID='DUMMY'
*
行1でエラーが発生しました。:
ORA-01733: ここでは仮想列は使用できません。
-- ID 列の更新は制限された
SQL> SELECT * FROM REST_CUSTOMER;
SNO ID NAME CREATED_ON
---------- ---------------- -------------------------------- -------------------
2 XXX-0001 永久 太郎 2000-01-01 00:00:00
INSERT を可能にする INSTEAD OF トリガー
この方法を用いると仮想項目への INSERT も制限されるので登録処理を許可したい場合には
元表 に対してデータを登録するか、
INSTEAD OF トリガーを設置して制限を迂回するという回避策を行なう必要がある。
当然であるが、トリガーの種類を変更すると更新はできるが登録はできないようにすることも可能である。
INSERT できないことを確認
SQL> INSERT INTO REST_CUSTOMER VALUES(3,'XXX-0003', '三日 寝太郎', DATE '2000-03-03');
INSERT INTO REST_CUSTOMER VALUES(3,'XXX-0003', '三日 寝太郎', DATE '2000-03-03')
*
行1でエラーが発生しました。:
ORA-01733: ここでは仮想列は使用できません。
SQL> CREATE OR REPLACE TRIGGER RIVUS.REST_CUSTOMER#TR
INSTEAD OF INSERT ON REST_CUSTOMER
FOR EACH ROW
BEGIN
INSERT INTO CUSTOMER (SNO, ID, NAME, CREATED_ON)
VALUES (:NEW.SNO, :NEW.ID, :NEW.NAME,:NEW.CREATED_ON);
END;
/
トリガーが作成されました。
SQL> INSERT INTO REST_CUSTOMER VALUES(3,'XXX-0003', '三日 寝太郎', DATE '2000-03-03');
1行が作成されました。
SQL> SELECT * FROM REST_CUSTOMER;
SNO ID NAME CREATED_ON
---------- ---------------- -------------------------------- -------------------
2 XXX-0001 永久 太郎 2000-01-01 00:00:00
3 XXX-0003 三日 寝太郎 2000-03-03 00:00:00
関連事項
- 仮想プライベート・データベース (Virtual Private Database)
- Oracle Label Security
印刷表示