郑州冰雕展:Oracle VPD实现数据细粒度访问(更好的权限控制)
Oracle VPD实现数据细粒度访问(更好的权限控制)
分类: Oracle 2010-01-19 21:28 480人阅读 评论(0) 收藏 举报概述
虚拟专用数据库 (VPD) 提供了角色和视图无法提供的行级访问控制。对于互联网访问,虚拟专用数据库可以确保在线银行的客户只能看到他们自己的帐户。Web 托管公司可以在同一 Oracle 数据库中维护多个公司的数据,但只允许每个公司查看其自身数据。
在企业内部,虚拟数据库可在应用程序部署方面降低拥有成本。可以在数据库服务器一次实现安全性,而不用在访问数据的每个应用程序中分别实现安全性。因为是在数据库中实施安全性,所以不管用户访问数据的方式如何,安全性较以前更高。访问即席查询工具或新报表生成程序的用户不再能绕过安全环节。虚拟专用数据库是一项重要技术,使企业能够构建托管的、基于 Web 的应用程序。实际上,许多 Oracle 应用程序本身使用 VPD 实施数据分隔,包括 Oracle SalesOnline.com 和 Oracle Portal 等程序。
虚拟专用数据库如何工作
将一个或多个安全策略与表或视图关联后,就可以实现虚拟专用数据库。对带安全策略的表进行直接或间接访问时,数据库将调用一个实施该策略的函数。策略函数返回一个访问条件(WHERE 子句),即谓词。应用程序将它附加到用户的 SQL 语句,从而动态修改用户的数据访问权限。
你可以通过编写一个存储过程将 SQL 谓词附加到每个 SQL 语句(用于控制该语句的行级别访问权限)来实施 VPD。例如,如果 John Doe(他属于 Department 10)输入 SELECT * FROM emp 语句,则可以使用 VPD 添加 WHERE DEPT = 10 子句。这样,您便可以通过对查询进行修改来限制访问某些行的数据。
虚拟专用数据库确保无论用户以何种方式访问数据(通过应用程序、报表编写工具或 SQL*Plus),都将强制实施同一强大的访问权限控制策略。这样,使用 VPD ,银行便可以确保客户只看到他们自己的帐户,电信公司可以安全地隔离客户记录,人力资源应用程序可以支持复杂的员工记录数据访问原则。
案例说明
1. 搭建环境--创建模式拥有者和相应的用户,赋予权限
[c-sharp] view plaincopy?
- 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
2. 创建上下文和上下文的包(Context)
[c-sharp] view plaincopy?
- 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;
- /
包的具体代码:
[c-sharp] view plaincopy?
- 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
- GRANT EXECUTE ON SCHEMAOWNER.Context_Package TO PUBLIC;
- CREATE PUBLIC SYNONYM Context_Package FOR SCHEMAOWNER.Context_Package;
解释: 获取当前登陆用户名,并设置上下文中的变量USER_ID为当前用户的ID,同时进行相应授权
3. 创建登陆触发器
[c-sharp] view plaincopy?
- 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
4. 建立安全策略
[c-sharp] view plaincopy?
- CONNECT schemaowner/schemaowner@service;
- 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;
- /
- 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'')';
- END IF;
- 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
- GRANT EXECUTE ON SCHEMAOWNER.Security_Package TO PUBLIC;
- CREATE PUBLIC SYNONYM Security_Package FOR SCHEMAOWNER.Security_Package;
5. 应用策略到相应的表,使用RDBMS_RLS
[c-sharp] view plaincopy?
- 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;
- /
5. VPD测试
[c-sharp] view plaincopy?
- 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;
测试结果:
(1) user1用户登陆的: 只用第一条insert成功
(2) user2用户登陆的: 只用第二条insert成功
(3) 关于查询:
user1和user2都只能看到自己的资料,而schemaowner可以看到所有的资料。这里重点是所添加的谓词不同,在安全策略中的
Predicate := 'USER_ID = SYS_CONTEXT(''SCHEMAOWNER'',''USER_ID'')';