Oracle VPD バーチャル・プライベート・データベース 入門

SET VERIFY OFF

-- データベースの名前
def db_name = ORCL

def dba_user = system
def dba_pass = manager

-- テーブルスペースの設定
def ts_name = users

-- (1) システム管理ユーザーの作成

-- 　DBA管理者でログイン
@@connect &dba_user/&dba_pass@&db_name

-- 　VPD用ユーザーの作成
CREATE USER vpd
IDENTIFIED BY vpd
DEFAULT TABLESPACE &ts_name
TEMPORARY TABLESPACE temp;

-- 　作成したユーザーに権限を付与
GRANT CONNECT, RESOURCE, DBA TO vpd;


-- (2) テーブル、シノニム、順序、トリガーの作成

-- 　デモシステム用ユーザーで接続
@@connect vpd/vpd@&db_name

-- 　テーブルの作成
CREATE TABLE vpd.addrbook
(id      NUMBER(8) PRIMARY KEY,
 owner   VARCHAR2(8) NOT NULL,
 name    VARCHAR2(32) NOT NULL,
 mail    VARCHAR2(32),
 phone   VARCHAR2(32),
 company VARCHAR2(32),
 title   VARCHAR2(32),
 address VARCHAR2(128),
 zip     VARCHAR2(16));

-- 　作成したテーブルにパブリックシノニムを作成
CREATE PUBLIC SYNONYM addrbook FOR vpd.addrbook;

-- 　すべてのユーザーが表にアクセスできるように権限を付与
GRANT DELETE, INSERT, SELECT, UPDATE ON vpd.addrbook TO public;

-- 　ID列用順序の作成
CREATE SEQUENCE vpd.addrbook_id_seq;

-- 　トリガーの作成
-- 　　このトリガーでID列とOWNER列を自動的に挿入する
CREATE OR REPLACE TRIGGER vpd.addrbook_insert
BEFORE INSERT ON vpd.addrbook
FOR EACH ROW
BEGIN
  SELECT vpd.addrbook_id_seq.NEXTVAL, SYS_CONTEXT('USERENV', 'session_user')
  INTO   :NEW.id, :NEW.owner
  FROM   dual;
END;
/


-- (3) Virtual Private Databaseの設定

-- 　セキュリティポリシーの設定
-- 　　VPDユーザーの場合はアクセス制御なし、その他のユーザーの場合は自分が追加
-- 　　したレコードのみアクセス可能
CREATE OR REPLACE PACKAGE vpd.addrbook_security IS
  FUNCTION owner_sec(d1 VARCHAR2, d2 VARCHAR2) RETURN VARCHAR2;
END addrbook_security;
/
CREATE OR REPLACE PACKAGE BODY vpd.addrbook_security IS
  FUNCTION owner_sec(d1 VARCHAR2, d2 VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    IF SYS_CONTEXT('USERENV', 'session_user') = 'VPD' THEN
      RETURN NULL;
    ELSE
      RETURN 'owner = ''' || sys_context('USERENV', 'session_user') || '''';
    END IF;
  END owner_sec;
END addrbook_security;
/

-- 　セキュリティポリシーの表への割り当て
EXECUTE DBMS_RLS.ADD_POLICY('vpd', 'addrbook', 'addrbook_policy', 'vpd', 'addrbook_security.owner_sec', 'SELECT, INSERT, UPDATE, DELETE')

SET VERIFY OFF

-- データベースの名前
def db_name = ORCL

def dba_user = system
def dba_pass = manager

-- ###########################################################################
@@connect &dba_user/&dba_pass@&db_name

-- (1) ユーザーの作成と権限付与
GRANT CREATE SESSION TO adams IDENTIFIED BY wood;
GRANT CREATE SESSION TO clark IDENTIFIED BY cloth;


-- (2) adamsユーザー用データの追加
@@connect adams/wood@&db_name
INSERT INTO addrbook (name, company, title, phone, mail, zip, address)
VALUES('KING', 'JOCKSPORTS', 'PRESIDENT', '598-6609', 'king@jocksports.com', '96711', '345 VIEWRIDGE BELMONT CA');
INSERT INTO addrbook (name, company, title, phone, mail, zip, address)
VALUES('CLARK', 'EVERY MOUNTAIN', 'MANAGER', '996-2323','clark@everymountain.com', '93301', '574 SURRY RD. CUREPTINO CA');
INSERT INTO addrbook (name, company, title, phone, mail, zip, address)
VALUES('BLAKE', 'TKB SPORT SHOP', 'MANAGER', '368-1223', 'blake@tkb.com', '94061', '490 BOLI RD. REDWOOD CITY CA');
INSERT INTO addrbook (name, company, title, phone, mail, zip, address)
VALUES('MARTIN', 'VOLLYRITE', 'SALESMAN', '644-3341', 'martin@vollyrite.com', '95133', '9722 HAMILTON BURLINGAME CA');
commit;


-- (3) clarkユーザー用データの追加
@@connect clark/cloth@&db_name
INSERT INTO addrbook (name, company, title, phone, mail, zip, address)
VALUES('ALLEN', 'K + T SPORTS', 'SALESMAN', '376-9966','allen@kt-sports.com', '91003', '3476 EL PASEO SANTA CLARA CA');
INSERT INTO addrbook (name, company, title, phone, mail, zip, address)
VALUES('JAMES', 'SHAPE UP', 'CLERK', '364-9777','james@shape-up.com', '94301', '908 SEQUOIA PALO ALTO CA');
INSERT INTO addrbook (name, company, title, phone, mail, zip, address)
VALUES('FORD', 'WOMANS SPORTS', 'ANALYST', '967-4398','ford@womans-sports.com', '93301', 'VALCO VILLAGE SUNNYVALE CA');
INSERT INTO addrbook (name, company, title, phone, mail, zip, address)
VALUES('MILLER', 'JUST TENNIS', 'CLERK', '677-3341', 'miller@justtennis.com', '97544', 'HILLVIEW MALL BURLINGAME CA');
commit;

-- 検証 
-- 全体で8行あるはずが、4行しかない

select count(*) from addrbook;

COUNT(*)
-----
4

-- vpdを使って、表示してみると、全体の8行が表示された

@@connect vpd/vpd

select count(*) from addrbook;

COUNT(*)
-----
8