VPD
--手順1: ユーザー・アカウントとサンプル表の作成
conn / as sysdba
GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE TRIGGER, ADMINISTER DATABASE TRIGGER
TO sysadmin_vpd IDENTIFIED BY oracle;
GRANT EXECUTE ON DBMS_SESSION TO sysadmin_vpd;
GRANT EXECUTE ON DBMS_RLS TO sysadmin_vpd;
GRANT CREATE SESSION TO tbrooke IDENTIFIED BY oracle;
GRANT CREATE SESSION TO owoods IDENTIFIED BY oracle;
conn test/test
CREATE TABLE customers (
cust_no NUMBER(4),
cust_email VARCHAR2(20),
cust_name VARCHAR2(20));
INSERT INTO customers VALUES (1234, 'TBROOKE', 'Thadeus Brooke');
INSERT INTO customers VALUES (5678, 'OWOODS', 'Oberon Woods');
GRANT SELECT ON customers TO sysadmin_vpd;
CREATE TABLE orders_tab (
cust_no NUMBER(4),
order_no NUMBER(4));
INSERT INTO orders_tab VALUES (1234, 9876);
INSERT INTO orders_tab VALUES (5678, 5432);
INSERT INTO orders_tab VALUES (5678, 4592);
GRANT SELECT ON orders_tab TO tbrooke;
GRANT SELECT ON orders_tab TO owoods;
--手順2: データベース・セッション・ベースのアプリケーション・コンテキストの作成
conn sysadmin_vpd/oracle
CREATE OR REPLACE CONTEXT orders_ctx USING orders_ctx_pkg;
--手順3: アプリケーション・コンテキストを設定するPL/SQLパッケージの作成
CREATE OR REPLACE PACKAGE orders_ctx_pkg IS
PROCEDURE set_custnum;
END;
/
CREATE OR REPLACE PACKAGE BODY orders_ctx_pkg IS
PROCEDURE set_custnum
AS
custnum NUMBER;
BEGIN
SELECT cust_no INTO custnum FROM TEST.CUSTOMERS
WHERE cust_email = SYS_CONTEXT('USERENV', 'SESSION_USER');
DBMS_SESSION.SET_CONTEXT('orders_ctx', 'cust_no', custnum);
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END set_custnum;
END;
/
--手順4: アプリケーション・コンテキストのPL/SQLパッケージを実行するログイン・トリガーの作成
CREATE TRIGGER set_custno_ctx_trig AFTER LOGON ON DATABASE
BEGIN
sysadmin_vpd.orders_ctx_pkg.set_custnum;
END;
/
--手順5: ログオン・トリガーのテスト
conn tbrooke/oracle
SELECT SYS_CONTEXT('orders_ctx', 'cust_no') custnum FROM DUAL;
--手順6: ユーザー・アクセスを自分の注文に制限するPL/SQLポリシー関数の作成
conn sysadmin_vpd/oracle
CREATE OR REPLACE FUNCTION get_user_orders(
schema_p IN VARCHAR2,
table_p IN VARCHAR2)
RETURN VARCHAR2
AS
orders_pred VARCHAR2 (400);
BEGIN
orders_pred := 'cust_no = SYS_CONTEXT(''orders_ctx'', ''cust_no'')';
RETURN orders_pred;
END;
/
--手順7: 新しいセキュリティ・ポリシーの作成
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'test',
object_name => 'orders_tab',
policy_name => 'orders_policy',
function_schema => 'sysadmin_vpd',
policy_function => 'get_user_orders',
statement_types => 'select');
END;
/
--手順8: 新しいポリシーのテスト
conn tbrooke/oracle
SELECT * FROM test.orders_tab;
conn owoods/oracle
SELECT * FROM test.orders_tab;
ネイティブ実装はない模様
行セキュリティポリシー
(9.6)
--postgresユーザ
drop user user1;
drop user user2;
create user user1 with password 'user1';
create user user2 with password 'user2';
grant connect on database test to public;
grant usage on schema public to public;
create table tab1(col1 text,col2 text);
insert into tab1 values('gold','user1');
insert into tab1 values('gold','user2');
insert into tab1 values('silver','user3');
insert into tab1 values(NULL,'user4');
grant all on tab1 to public;
alter table tab1 enable row level security;
--user1ユーザ
select * from tab1;
--postgresユーザ
create policy pol1
on tab1
for select to user1
using (col1 = 'gold');
--user1ユーザ
select * from tab1;
--postgresユーザ
create policy pol2
on tab1
for insert to user2
with check ( col1 in ('gold','bronze'));
--user2ユーザ
insert into tab1 values ('gold','user25');
insert into tab1 values ('silver','user26');
insert into tab1 values ('bronze','user27');
(2016)
CREATE USER Manager WITHOUT LOGIN;
CREATE USER Sales1 WITHOUT LOGIN;
CREATE USER Sales2 WITHOUT LOGIN;
CREATE TABLE Sales
(
OrderID int,
SalesRep sysname,
Product varchar(10),
Qty int
);
INSERT INTO Sales VALUES (1, 'Sales1', 'Valve', 5);
INSERT INTO Sales VALUES (2, 'Sales1', 'Wheel', 2);
INSERT INTO Sales VALUES (3, 'Sales1', 'Valve', 4);
INSERT INTO Sales VALUES (4, 'Sales2', 'Bracket', 2);
INSERT INTO Sales VALUES (5, 'Sales2', 'Wheel', 5);
INSERT INTO Sales VALUES (6, 'Sales2', 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sales;
GRANT SELECT ON Sales TO Manager;
GRANT SELECT ON Sales TO Sales1;
GRANT SELECT ON Sales TO Sales2;
CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales
WITH (STATE = ON);
EXECUTE AS USER = 'Sales1';
SELECT * FROM Sales;
REVERT;
EXECUTE AS USER = 'Sales2';
SELECT * FROM Sales;
REVERT;
EXECUTE AS USER = 'Manager';
SELECT * FROM Sales;
REVERT;
