ORACLE FGAC(细粒度权限控制)（转）

ORACLE FGAC(细粒度权限控制)
转自：http://www.itpub.net/thread-1037232-1-1.html

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

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
 
   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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28612416/viewspace-772841/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28612416/viewspace-772841/