ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE FGAC(细粒度权限控制)(转)

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

原创 Linux操作系统 作者:流浪的野狼 时间:2013-09-17 15:58:17 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Oracle资源管理器
请登录后发表评论 登录
全部评论

注册时间:2013-04-10

  • 博文量
    151
  • 访问量
    1507427