FineGrainedAccessControl资源-CSDN文库

需积分: 9 30 浏览量 2011-08-01 19:38:14 上传评论收藏 270KB PDF 举报

细粒度访问控制（Fine Grained Access Control，简称FGAC）是数据库安全性管理中的一个重要概念。它能够确保数据的安全性和隐私性，特别是在处理敏感信息时更为关键。本文将基于Arup Nanda的文章来深入探讨这一主题，并通过一个医院数据库系统的例子来解释其应用。 ### 一、什么是细粒度访问控制？细粒度访问控制是一种高级的安全机制，它允许对数据进行更精细的控制。与传统的基于角色或用户组的访问控制不同，FGAC能够精确到每一行或每一列的数据访问权限，从而确保只有特定用户或特定条件下的用户才能访问他们有权查看的数据。 ### 二、为何需要细粒度访问控制？ #### 2.1 法规遵从性例如，在医疗行业中，HIPAA（Health Insurance Portability and Accountability Act）法规要求医疗机构必须保护患者的健康信息。这就意味着医生只能查看他们正在治疗的病人的数据，而不能查看所有病人的数据。 #### 2.2 安全性问题如果仅仅依赖于应用程序级别的过滤，那么可能会出现安全漏洞。用户可能绕过应用程序直接从数据库查询数据，从而获取未经授权的信息。 ### 三、传统的解决方案及其局限性 #### 3.1 应用程序级别的过滤 - **局限性**：修改大量SQL语句非常不实际。 - **第三方软件**：对于购买的第三方软件来说，这种改变几乎不可能实现，因为源代码不在医院的控制之下。 #### 3.2 使用视图 - **安全性**：可以创建带有过滤条件的视图来限制数据访问。 - **局限性**： - 视图数量的增加可能导致维护变得复杂。 - 当需要限制表的所有者访问时，该方案无效。 - 视图中的过滤条件通常是静态的，无法根据运行时的条件动态调整。 ### 四、细粒度访问控制的实现 #### 4.1 Oracle FGAC的特点 - **自动过滤**：无论通过何种方式访问数据，都能自动应用过滤条件。 - **动态性**：过滤条件可以根据实时情况动态生成。 - **灵活性**：适用于各种场景，包括需要限制表所有者的访问。 #### 4.2 实现方式 - **政策表达式**：定义用于决定哪些用户可以访问哪些数据的逻辑表达式。 - **政策函数**：编写自定义函数来计算政策表达式的值，通常与具体的业务逻辑相关联。 - **政策绑定**：将政策表达式与表或视图关联起来，确保在访问这些对象时自动执行相应的过滤。 ### 五、案例分析：医院数据库系统假设一家医院希望实现对患者数据的细粒度访问控制。医生需要访问他们的病人记录，但不能访问其他病人的数据。此外，医院还需要遵守HIPAA法规。 #### 5.1 设计政策表达式 - **医生ID**：每个医生都有唯一的ID。 - **患者ID**：每个患者的记录包含一个医生ID字段，标识负责该患者的医生。 #### 5.2 创建政策函数 - 函数根据当前登录用户的ID和患者记录中的医生ID进行比较，只返回与当前医生相关的患者记录。 #### 5.3 应用政策 - 将上述政策函数绑定到患者表上，确保无论医生通过何种方式访问患者数据，都只能看到自己的患者信息。 ### 六、总结细粒度访问控制作为一种强大的安全措施，为保护敏感数据提供了强有力的保障。通过对Oracle FGAC的理解和应用，我们可以更好地满足合规性需求，同时提高数据安全性。在医疗行业等敏感领域，采用FGAC不仅有助于遵守法律法规，还能有效地防止数据泄露的风险。

资源推荐

资源详情

资源评论

Fine Grained Access Control

Arup Nanda, Proligence, Inc.

Introduction

Just as a picture is worth thousand words, it’s my belief that even the most complex of concepts or features can be easily

explained through an example, where the reader has the ability to test out the concepts while reading them online.

Following my own convictions, I will explain the concepts and uses of this powerful feature of the database using an

example of a hospital database.

The application that the hospital uses must adhere to the HIPAA regulations and one of the mandates is to allow viewing

of patient data only to the people who need them and have authorization for. This means the doctors can access the data

on just those patients they are treating, not all. In the old days, this could have been done using a simple filtering predicate

in the application queries. This is not practical for a variety of reasons – the primary of which is the modification of a

large number of SQL statements inside the application. In case of third party canned applications, this is even more

difficult, with the source code controlled in a different place outside the control of the hospital.

The other problem is security – what if the user just bypasses the applications and queries the data directly from the

database? This sidesteps the filtering predicate inside the code, allowing full access to the data. Obviously the solution

calls for the filtering predicate to be applied automatically, regardless of how the access is made.

One of the approaches is to use a view on the table with a filtering predicate built in, and allowing users to access the view

instead of the base table. This accomplishes the security requirement, but with the proliferation of the views, it might be

impractical to maintain this setup. In addition, if the need ever comes to restrict the access by the owner itself, this

solution will not work, since the owner can select from the base table. Using views also forces us to use a predicate that is

static, not generated at the runtime.

The perfect solution comes from Oracle’s implementation of Fine Grained Access Control (FGAC). It is also known as

Row Level Security (RLS) or Virtual Private Database (VPD). In this article we will explore the use of this powerful

feature in the form of an example setup, to bolster the understanding. We will also learn how to use another advanced

feature – application contexts in a database setup.

Setup

You can download the scripts to create all the objects used in this article and populate the data from my website,

www.proligence.com/pubsupp.

In our example, we have used the case of an overly simplified hospital database. Typically hospitals have several doctors

and each patient is assigned a doctor. The tables are owned by the schema HOSPITAL. The table DOCTORS holds the

information on doctors as follows.

Name Null? Type

----------------- -------- ------------

DOCTOR_ID NOT NULL NUMBER

DOCTOR_NAME VARCHAR2(20)

GROUP_ID NUMBER

The primary key of the table is DOCTOR_ID. It’s assumed that the column DOCTOR_NAME contains the doctor’s login

id to the database. A group may consist of several doctors. The column GROUP_ID specifies the group the doctor belongs

to.

The other important table holds information on patients visiting the hospital, named PATIENTS, shown below.

Name Null? Type

----------------- -------- ------------

PATIENT_ID NOT NULL NUMBER

DOCTOR_ID NUMBER

PATIENT_NAME VARCHAR2(20)

DISEASE VARCHAR2(20)

The column PATIENT_ID is the primary key of the table. The column DOCTOR_ID is a foreign key to the table

DOCTORS shown above. For the purpose of simplicity, let’s assume that the relationship between DOCTORS to

PATIENTS is one-to-many, whereas in the real life, it is probably many-to-many.

Here is how the data looks like in the table DOCTORS.

DOCTOR_ID DOCTOR_NAME GROUP_ID

---------- -------------------- ----------

1 DRADAM 1

2 DRBARB 2

3 DRCHARLIE 2

And here is the data in table PATIENTS.

PATIENT_ID DOCTOR_ID PATIENT_NAME DISEASE

---------- ---------- -------------------- -----------

1 1 LARRY EGO

2 1 BILL CONTROL

3 2 SCOTT FICKLENESS

4 3 CRAIG LOWVISION

5 3 LOU GREED

Corresponding to all the names of the doctors, we need to have the userids created in Oracle in the same name. Therefore,

we have the users named DRADAMS, DRBARB and DRCHARLIE, all with SELECT, INSERT, UPDATE and

DELETE privileges on the tables DOCTORS and PATIENTS.

Building the FGAC Setup

In the existing application, the following statement is a call made to the database to see the patient data.

SELECT * FROM PATIENTS;

With the new requirements in mind, the call must be changed to

SELECT * FROM PATIENTS WHERE DOCTOR_ID = <id of the doctor logged in>

We somehow have to make a system where the application need not be changed, and the first call will automatically select

only the records related to the doctor currently logged in, not all. In other words, we have to generate a filtering predicate,

i.e. a WHERE clause to be appended to he query automatically. Building this predicate is the first step. The following

function returns a string that can be applied to the query as a WHERE clause.

create or replace function get_doctor_id

(

p_schema_name in varchar2,

p_table_name in varchar2

)

return varchar2

l_doctor_id number;

begin

select doctor_id

into l_doctor_id

from doctors

where doctor_name = USER;

return 'doctor_id = '||l_doctor_id;

end;

Note how the function returns the string DOCTOR_ID = <id> where <id> is the numerical ID of the doctor who is

logged in now, as returned by the function call USER above.

This special function is called a policy function and is the building block of a FGAC setup. Note that this has exactly two

input arguments, for the schema and the table on which it will be applied and has exactly one return value, the string that

will be used as a WHERE clause. The structure of the policy function must be exactly this and is not flexible. The logic

can be changed inside, though.

Next step is to build a policy to be placed on a table. This policy is the one that restricts the rows accessible to the users. It

does so by applying the output of the policy function. The following code segment sets up a policy on the table

PATIENTS.

begin

dbms_rls.add_policy

(

object_schema => 'HOSPITAL',

object_name => 'PATIENTS',

policy_name => 'PATIENT_VIEW_POLICY',

policy_function => 'GET_DOCTOR_ID',

function_schema => 'HOSPITAL',

statement_types => 'SELECT, INSERT, UPDATE, DELETE',

update_check => true,

enable => true

);

end;

Here we have defined a policy named PATIENT_VIEW_POLICY on the table PATIENTS in schema HOSPITAL. The

policy calls the function GET_DOCTOR_ID as shown in the parameter policy_function. This policy is applied to

all types of DML statements on the table – SELECT, INSERT, UPDATE and DELETE. The other options will be

explained later.

Once the policy is in place, logon to the database as the user DRADAM and select from the table

SQL> select * from hospital.patients

剩余16页未读，继续阅读

评论收藏

内容反馈

jackhan

粉丝: 1
资源: 6

Fine Grained Access Control

Predicate Fully Homomorphic Encryption: Achieving Fine-grained Access Control over Manipulable Ciphertext

30-段佳昂-2020_TPDS_Automated Fine-Grained CPU Cap Control in Serve

sybase ASE 15.0.3版各功能在所有平台上的可用性

Combined access control model embedding configurable policy for fine-grained data security

IS530-130517-1021-4762.pdf

configuring-selinux-policy-report.pdf

Oracle9i Guides.rar

AZ-900.136Q.Pdf

oracle 10g OCP试题

SMB3.0官方文档

Oracle OCP题库 1Z-053

Oracle Database 11g完全参考手册 试读

Oracle11gForDummies-英文原版.zip

OracleClient-21C Oracle客户端，包括windows和Linux

Oracle与DB2技术比较.doc

Oracle基础教程.pptx

oracle认证1Z0-042原题

sql server安全技术综述

ORACLE 10G入门与实践

Oracle9i数据库管理实务讲座(PDF)

Oracle OCP 1Z0-042考题加模拟器

Oracle技术手册。。。人人软件站

10g DBA - Admin Workshop I - D17090GC20 R2.zip

Oracle 10g数据库管理艺术

ORACLE_10g帮助文档.zip

oracle技术大会 itpub 文档

Oracle10G官方文档CHM合集-Database参考手册

ocp 11g 题库

Oracle 9i10g编程艺术_整理版

Wrox-Oracle专家高级编程-Expert_one-on-one_Oracle

最新资源

Oracle Database 11g完全参考手册试读