oracle给seq赋权限,ORACLE FGAC(细粒度权限控制)（转）

--主要完成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;