oracle三种权限级别,ORACLE FGAC(细粒度权限控制)

--主要完成ORACLE fine-grained access control ， 以及使用存储过程进行权限封装

/*Oracle fine-grained access control 可以实现数据库对象行级别的权限控制 以下测试表为例： manager 只可以更改自己的 employee的行， 而不可以更改其它mananger 所拥有的employee 的行 */

CONNECT system/yJdg2U1v AS sysdba

--授予HR权限

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE,CREATE ANY CONTEXT, CREATE PROCEDURE,

CREATE ANY TRIGGER TO HR IDENTIFIED BY HR;

CONNECT hr/hr;

--创建两张测试表

CREATE TABLE Directory (Empno NUMBER(4) NOT NULL,

Mgrno NUMBER(4) NOT NULL,

Rank NUMBER(7,2) NOT NULL);

CREATE TABLE Payroll (Empno NUMBER(4) NOT NULL,

Name VARCHAR(30) NOT NULL );

--插入测试数据

INSERT INTO Directory VALUES (1, 1, 1.0);

INSERT INTO Payroll VALUES (1, 'KING');

INSERT INTO Directory VALUES (2, 1, 5);

INSERT INTO Payroll VALUES (2, 'CLARK');

--创建序列

CREATE SEQUENCE Empno_seq START WITH 5;

CREATE SEQUENCE Rank_seq START WITH 100;

--创建上下文

CREATE OR REPLACE CONTEXT Hr_app USING Hr.Hr0_pck;

CREATE OR REPLACE CONTEXT Hr_sec USING Hr.Hr1_pck;

--创建上下文用包

CREATE OR REPLACE PACKAGE Hr0_pck IS

PROCEDURE adjustrankby1(Empno NUMBER);

END;

-- 这一步仅仅进行了简单的测试， 并没有做过多的是设置， 可根据具体情况来设置

CREATE OR REPLACE PACKAGE BODY Hr0_pck IS

PROCEDURE Adjustrankby1(Empno NUMBER) IS

Stmt VARCHAR2(100);

BEGIN

DBMS_SESSION.SET_CONTEXT('hr_app', 'adjstate', 1);

END;

END;

---

CREATE OR REPLACE PACKAGE hr1_pck IS

PROCEDURE setid;

END;

CREATE or REPLACE PACKAGE BODY Hr1_pck IS

PROCEDURE setid IS

id NUMBER;

BEGIN

SELECT Empno

INTO id

FROM Payroll

WHERE Name = SYS_CONTEXT('userenv', 'session_user');

DBMS_SESSION.SET_CONTEXT('hr_sec', 'empno', id);

DBMS_SESSION.SET_CONTEXT('hr_sec', 'appid', id);

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO Payroll

(Empno, Name)

VALUES

(Empno_seq.NEXTVAL, SYS_CONTEXT('userenv', 'session_user'));

INSERT INTO Directory

(Empno, Mgrno, Rank)

VALUES

(Empno_seq.CURRVAL, 2, Rank_seq.NEXTVAL);

SELECT Empno

INTO id

FROM Payroll

WHERE Name = sys_context('userenv', 'session_user');

DBMS_SESSION.SET_CONTEXT('hr_sec', 'empno', id);

DBMS_SESSION.SET_CONTEXT('hr_sec', 'appid', id);

WHEN OTHERS THEN

NULL;

END;

END;

GRANT EXECUTE ON Hr1_pck TO public;

CONNECT system/yJdg2U1v AS sysdba

---创建logon 触发器

CREATE OR REPLACE TRIGGER Databasetrigger

AFTER LOGON ON DATABASE

BEGIN

--- 当用户登陆时会执行 hr.Hr1_pck.Setid这个过程来完成设置上下文

hr.Hr1_pck.Setid;

END;

CONNECT hr/hr

-- 这个过程为测试过程， 并非关键步骤

CREATE OR REPLACE PACKAGE hr2_pck IS

FUNCTION Findmgr(Empno NUMBER) RETURN NUMBER;

END;

CREATE OR REPLACE PACKAGE BODY hr2_pck IS

/* insert a new employee record: */

FUNCTION findmgr(empno number) RETURN NUMBER IS

Mgrid NUMBER;

BEGIN

SELECT mgrno INTO mgrid FROM directory WHERE mgrid = empno;

RETURN mgrid;

END;

END;

-- 创建细粒度访问策略用函数

CREATE OR REPLACE FUNCTION secure_updates(ns varchar2,na varchar2)

RETURN VARCHAR2 IS

Results VARCHAR2(100);

BEGIN

IF (sys_context('hr_app','adjstate') = 1)

THEN results := 'mgrno = SYS_CONTEXT('||'''hr_sec'',''empno'')';

ELSE results := '1=2';

END IF;

RETURN Results;

END;

/* 此过程可理解为：当一个session 发出一条sql 命令时候 会通过此过程来改变“谓语动词”，实际上就是 增加了where 条件， 比如： update hr.directory set rank=5, 那么它会根据不同的权限来改写这个条件， 比如改写为：

update hr.directory set rank=5 where mgrno = SYS_CONTEXT('hr_sec', 'empno')

/*

CONNECT system/yJdg2U1v ASsysdba;

---设置细粒度访问权限策略

BEGIN

DBMS_RLS.ADD_POLICY('hr', 'directory', 'secure_update', 'hr', 'secure_updates', 'update',true,true);

END;

-- 此策略类型为update , 还有select ,insert 等类型

--以下为测试过程：

SQL> conn hr/hr;

已连接。

SQL> select * from payroll;

EMPNO NAME

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

25 SYSMAN

26 SYS

27 HR

1 KING

2 CLARK

SQL> select * from directory;

EMPNO      MGRNO       RANK

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

27          2        122

26          2         10

1          1         10

2          1         10

--- 登陆触发器已经触发

SQL> update hr.directory set rank=10;

已更新0行。

--- 不允许更改数据

SQL> select sys_context('hr_app','adjstate') from dual;

SYS_CONTEXT('HR_APP','ADJSTATE')

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

-- 设置hr_app 上下文：

SQL> begin

2  Hr0_pck.Adjustrankby1(100);

3  end;

4  /

PL/SQL 过程已成功完成。

SQL> select sys_context('hr_app','adjstate') from dual;

SYS_CONTEXT('HR_APP','ADJSTATE')

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

1

SQL> update directory set rank=10;

已更新0行。

-- 同样不让更新数据

-- 继续测试：

--创建clark 用户

SQL> create user clark identified by clark;

用户已创建。

SQL> grant dba ,resource,connect to clark;

授权成功。

SQL> conn clark/clark;

已连接。

SQL> select sys_context('hr_app','adjstate') from dual;

SYS_CONTEXT('HR_APP','ADJSTATE')

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

SQL> update hr.directory set rank=10;

已更新0行。

SQL> begin

2  Hr0_pck.Adjustrankby1(100);

3  end;

4  /

Hr0_pck.Adjustrankby1(100);

*

第 2 行出现错误:

ORA-06550: 第 2 行, 第 1 列:

PLS-00201: 必须声明标识符 'HR0_PCK.ADJUSTRANKBY1'

ORA-06550: 第 2 行, 第 1 列:

PL/SQL: Statement ignored

SQL> begin

2  hr.Hr0_pck.Adjustrankby1(100);

3  end;

4  /

PL/SQL 过程已成功完成。

SQL>  select sys_context('hr_app','adjstate') from dual;

SYS_CONTEXT('HR_APP','ADJSTATE')

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

1

SQL> update hr.directory set rank=10;

已更新2行。

SQL> commit;

提交完成。

SQL> select * from hr.directory;

EMPNO      MGRNO       RANK

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

27          2         10

26          2         10

1          1         10

2          1         10

SQL> update hr.directory set rank=111;

已更新2行。

SQL> commit;

提交完成。

SQL> select * from hr.directory;

EMPNO      MGRNO       RANK

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

27          2        111

26          2        111

1          1         10

2          1         10

SQL>

SQL> conn / as sysdba

已连接。

SQL> set wrap off

SQL> create user king identified by king;

用户已创建。

SQL> grant dba,connect,resource to king;

授权成功。

SQL> conn king/king;

已连接。

SQL> update hr.directory set rank=20;

已更新0行。

SQL> begin

2  hr.Hr0_pck.Adjustrankby1(100);

3  end;

4  /

PL/SQL 过程已成功完成。

SQL> update hr.directory set rank=20;

已更新2行。

SQL> commit;

提交完成。

SQL> select * from hr.directory;

EMPNO      MGRNO       RANK

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

27          2        111

26          2        111

1          1         20

2          1         20

SQL>

SQL> create user kkk identified by kkk;

用户已创建。

SQL> grant dba ,connect,resource to kkk;

授权成功。

SQL> conn kkk/kkk;

已连接。

SQL> show user;

USER 为 "KKK"

SQL> begin

2  hr.Hr0_pck.Adjustrankby1(100);

3  end;

4  /

PL/SQL 过程已成功完成。

SQL> update hr.directory set rank=1;

已更新0行。

SQL> select * from hr.directory ;

EMPNO      MGRNO       RANK

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

27          2        111

26          2        111

28          2        123

1          1         20

2          1         20

SQL>

/*ORACLE的细粒度权限访问控制在某些特殊安全要求下确实表现的很出色， 也可以节省大量的代码时间以及代码的维护量*/