Virtual Private Databases (VPD)

Virtual Private Databases (VPD)
Virtual Private Databases (VPD) allow multiple users to access a single schema whilst preventing them from accessing data that is not relevant to them. Although this type of access can be controlled by the application, access via other methods (SQL*Plus) would leave the data open to abuse. Setting up a VPD involves the following steps:

Setup Test Environment
Create an Application Context
Create Login Trigger
Create Security Policies
Apply Security Policies to Tables
Test VPD
What Next
Setup Test Environment
First we must create a user to act as the schema owner for this example. Obviously, you will perform the following tasks using your current schema owner:
代码:

CONNECT sys/password@service AS SYSDBA;
CREATE USER schemaowner IDENTIFIED BY schemaowner
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO schemaowner;

CREATE USER user1 IDENTIFIED BY user1
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO user1;

CREATE USER user2 IDENTIFIED BY user2
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO user2;

GRANT EXECUTE ON DBMS_RLS TO PUBLIC;

CONN schemaowner/schemaowner@service

CREATE TABLE users
(id          NUMBER(10)   NOT NULL,
ouser       VARCHAR2(30) NOT NULL,
first_name  VARCHAR2(50) NOT NULL,
last_name   VARCHAR2(50) NOT NULL);

CREATE TABLE user_data
(column1     VARCHAR2(50) NOT NULL,
user_id     NUMBER(10)   NOT NULL);

INSERT INTO users VALUES (1,'USER1','User','One');
INSERT INTO users VALUES (2,'USER2','User','Two');
COMMIT;

GRANT SELECT, INSERT ON user_data TO user1, user2;

Create an Application Context
Grant CREATE ANY CONTEXT to the schema owner then create the context and context package:
代码:

CONNECT sys/password@service AS SYSDBA;
GRANT create any context, create public synonym TO schemaowner;

CONNECT schemaowner/schemaowner@service;

CREATE CONTEXT SCHEMAOWNER USING SCHEMAOWNER.Context_Package;

CREATE OR REPLACE PACKAGE Context_Package AS
  PROCEDURE Set_Context;
END;
/

Next we create the Context_Package body which will actually set the user context:
代码:

CREATE OR REPLACE PACKAGE BODY Context_Package IS
  PROCEDURE Set_Context IS
    v_ouser  VARCHAR2(30);
    v_id     NUMBER;
  BEGIN
    DBMS_Session.Set_Context('SCHEMAOWNER','SETUP','TRUE');
    v_ouser := SYS_CONTEXT('USERENV','SESSION_USER');
   
    BEGIN
      SELECT id
      INTO   v_id
      FROM   users
      WHERE  ouser = v_ouser;
     
      DBMS_Session.Set_Context('SCHEMAOWNER','USER_ID', v_id);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_Session.Set_Context('SCHEMAOWNER','USER_ID', 0);
    END;
   
    DBMS_Session.Set_Context('SCHEMAOWNER','SETUP','FALSE');
  END Set_Context;
END Context_Package;
/
SHOW ERRORS

Next we make sure that all users have access to the Context_Package:
代码:

GRANT EXECUTE ON SCHEMAOWNER.Context_Package TO PUBLIC;
CREATE PUBLIC SYNONYM Context_Package FOR SCHEMAOWNER.Context_Package;

 

to be continue

_________________
If God had gifted me with some beauty and much wealth, I should have made it as hard for you to leave me, as it is now for me to leave you.

返回页首

yikaikai       
精灵使

 
 
注册时间: 2002-11-09
最后登录: 2004-09-23
帖子总数: 2654
精华帖子: 8
原创精华: 1
来自: 中国

在线状态: ...离线...

 

 发表于: 2003-02-24 11:02    发表主题:
 

 
Create Login Trigger

Next we must create a trigger to fire after the user logs onto the database:
代码:

CONNECT sys/password@service AS SYSDBA;
CREATE OR REPLACE TRIGGER SCHEMAOWNER.Set_Security_Context
AFTER LOGON ON DATABASE
BEGIN
  SCHEMAOWNER.Context_Package.Set_Context;
END;
/
SHOW ERRORS

Create Security Policies:

In order for the context package to have any effect on the users interaction with the database, we need to define a Security_package for use with the security policy. This package will tell database how to treat any interactions with the specified table:
代码:

CONNECT schemaowner/schemaowner@serivce;
CREATE OR REPLACE PACKAGE Security_Package AS
   FUNCTION User_Data_Insert_Security(Owner VARCHAR2, Objname VARCHAR2)
return varchar2;
    FUNCTION User_Data_Select_Security(Owner VARCHAR2, Objname VARCHAR2)
    RETURN VARCHAR2;
END Security_Package;
/

Next we create the Security_Package body:
代码:

CREATE OR REPLACE PACKAGE BODY Security_Package IS
  FUNCTION User_Data_Select_Security(Owner VARCHAR2, Objname VARCHAR2) RETURN VARCHAR2 IS
Predicate  VARCHAR2(2000);
BEGIN
Predicate:= '1=2';
IF (SYS_CONTEXT('USERENV', 'SESSION_USER');='SCHEMAOWNER')
THEN
    Predicate:= NULL;
ELSE
  Predicate:='USER_id=SYS_CONTEXT(''SCHEMAOWNER'', ''USER_ID'')';
  RETURN Predicate;
  END User_Data_Select_Security;

  FUNCTION User_Data_Insert_Security(Owner VARCHAR2, Objname VARCHAR2) RETURN VARCHAR2 IS
    Predicate VARCHAR2(2000);
  BEGIN
    Predicate := '1=2';
    IF (SYS_CONTEXT('USERENV','SESSION_USER') = 'SCHEMAOWNER') THEN
      Predicate := NULL;
    ELSE
      Predicate := 'USER_ID = SYS_CONTEXT(''SCHEMAOWNER'',''USER_ID'')';
    END IF;
    RETURN Predicate;
  END User_Data_Insert_Security;
END Security_Package;
/
SHOW ERRORS

Next we make sure that all users have access to the Security_Package:
代码:

GRANT EXECUTE ON SCHEMAOWNER.Security_Package TO PUBLIC;
CREATE PUBLIC SYNONYM Security_Package FOR SCHEMAOWNER.Security_Package;

Apply Security Policies to Tables
The DBMS_Rls package is used to apply the security policay, implemented by Security_Package, to the the relevant tables:
代码:

BEGIN
  DBMS_Rls.Add_Policy('SCHEMAOWNER', 'USER_DATA', 'USER_DATA_INSERT_POLICY',
                      'SCHEMAOWNER', 'SECURITY_PACKAGE.USER_DATA_INSERT_SECURITY',
                      'INSERT', TRUE);
  DBMS_Rls.Add_Policy('SCHEMAOWNER', 'USER_DATA', 'USER_DATA_SELECT_POLICY',
                      'SCHEMAOWNER', 'SECURITY_PACKAGE.USER_DATA_SELECT_SECURITY',
                      'SELECT');
END;
/

Test VPD
Finally, test that the VPD is working correctly:
代码:

CONNECT user1/user1@service;
INSERT INTO schemaowner.user_data (column1, user_id) VALUES ('User 1', 1);
INSERT INTO schemaowner.user_data (column1, user_id) VALUES ('User 2', 2);
COMMIT;

CONNECT user2/user2@service
INSERT INTO schemaowner.user_data (column1, user_id) VALUES ('User 1', 1);
INSERT INTO schemaowner.user_data (column1, user_id) VALUES ('User 2', 2);
COMMIT;

CONNECT schemaowner/schemaowner@service
SELECT * FROM schemaowner.user_data;
CONNECT user1/user1@Service;
SELECT * FROM schemaowner.user_data;
CONNECT user2/user2@Service
SELECT * FROM schemaowner.user_data;

When connected to USER1, only the first insert will work.
When connected to USER2, only the second insert will work.
The failing inserts produce the error:

ORA-28115: policy with check option violation
Once the inserts are finished, there will be two rows in the table, as seen when connected as SCHEMAOWNER. When connected as USER1 or USER2, only the single row they inserted will be visible.

What Next
Once you're happy with the basic mechanism you can extend the Security_Package to cover all tables where restricted access is neccessary, remembering to apply all security policies to the relevant tables.
(END)

_________________
If God had gifted me with some beauty and much wealth, I should have made it as hard for you to leave me, as it is now for me to leave you.