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;