一、背景

? ? ? ?在某項目現場，開發商想實現4個用戶之間能互相擁有表的查詢、刪除、插入、更新權限和存儲過程的執行權限。此過程只要在新增表之后，其他用戶的權限需要授權，如果是手動寫，一張表的授權就要寫至少3次sql語句，如果多了，容易疏漏。

????????了解到新建表屬于低頻且由人工操作，因此提供了一個存儲過程給對方，讓對方建完表之后，手動執行下對應的存儲過程即可。

二、需求

有四個用戶：user1,user2,user3,user4能互相操作表的增刪改查權限。

三、思路

使用 2 張配置表，列舉出授權的組合情況。使用存儲存儲過程中的游標遍歷列表并執行授權語句。【此方法同樣使用4個以上的用戶兩兩授權的情況】

四、步驟

4.1 初始化數據

--初始化插入用戶信息
CREATE TABLE temp_user_list (value VARCHAR(10));
INSERT INTO temp_user_list VALUES ('USER1'), ('USER2'), ('USER3'), ('USER4');
COMMIT;--初始化數據，兩兩組合，為了實現授權
--DROP if exist  TABLE "SYSDBA"."PRIV_OWNER_LIST" ;
CREATE  TABLE "SYSDBA"."PRIV_OWNER_LIST"
(	ID INT IDENTITY(1,1),"P_USER" varCHAR(20) NOT NULL ,"S_USER" varCHAR(20) NOT NULL  
);insert into "SYSDBA"."PRIV_OWNER_LIST" (P_USER,S_USER)
SELECT t1.value AS value1, t2.value AS value2
FROM  temp_user_list t1
CROSS JOIN  temp_user_list t2
WHERE  t1.value <> t2.value ;
commit;SELECT * FROM "SYSDBA"."PRIV_OWNER_LIST" ;

4.2? 創建存儲過程實現語句

create or REPLACE PROCEDURE sysdba.PROC_MAIN_PRIV (SOURCE_USER varchar(100),TARGET_USER varchar(100) )is --授予用戶表的所有權限begin DECLARESQLSTMT STRING;SQLSTMT_PROC STRING;CURSOR CUR FOR SELECT ID,NAME  FROM SYSOBJECTS  WHERE  TYPE$ = 'SCHOBJ' AND SUBTYPE$ IN ('STAB','UTAB')  AND (PID=-1 OR PID=0) AND  SCHID=(SELECT ID FROM SYSOBJECTS WHERE TYPE$='SCH' AND NAME=SOURCE_USER );TYPE MYREC IS CUR%ROWTYPE;REC_V MYREC;BEGINFOR REC_V IN CUR LOOPSQLSTMT = 'grant SELECT,INSERT,DELETE,UPDATE ON '||SOURCE_USER||'.'|| '"'||REC_V.NAME ||'"' || ' to ' || TARGET_USER ||';';EXECUTE IMMEDIATE SQLSTMT;--PRINT SQLSTMT;END LOOP;COMMIT;
END;--授予用戶存儲過程執行權限
DECLARESQLSTMT_PROC STRING;CURSOR CUR FOR SELECT  SUBTYPE$ ,ID,NAME ,*  FROM SYSOBJECTS  WHERE  TYPE$ = 'SCHOBJ'   AND SUBTYPE$ IN ('PROC')  AND   SCHID=(SELECT ID FROM SYSOBJECTS WHERE TYPE$='SCH' AND NAME=SOURCE_USER);TYPE MYREC IS CUR%ROWTYPE;REC_V MYREC;BEGINFOR REC_V IN CUR LOOPSQLSTMT_PROC = 'grant EXECUTE ON '||SOURCE_USER||'.'|| '"'||REC_V.NAME ||'"' || ' to ' || TARGET_USER ||';';EXECUTE IMMEDIATE SQLSTMT_PROC;--PRINT SQLSTMT_PROC;END LOOP;COMMIT;
END; end;

?4.3 創建存儲過程遍歷列表執行授權

--遍歷表"SYSDBA"."PRIV_OWNER_LIST"中的用戶進行授權
create or replace PROCEDURE sysdba.pro_grant_all_priv ()
iscursor user_priv_list isselect P_USER,S_USER from "SYSDBA"."PRIV_OWNER_LIST";beginfor  cur_user  in user_priv_list loopBEGINcall sysdba.proc_main_priv (cur_user.P_USER,cur_user.S_USER);end ;end loop;end;--任意具備此尋出過程執行權限的用戶調用即可完成授權 
call  sysdba.pro_grant_all_priv ();

?4.4 授權4個用戶相關表和存儲過程的相應權限

--把執行存儲過程的權限給  user1，user2，user3，user4 這4個用戶 grant EXECUTE ON  sysdba.pro_grant_all_priv to user1;
grant EXECUTE ON  sysdba.pro_grant_all_priv to user2 ;
grant EXECUTE ON  sysdba.pro_grant_all_priv to user3 ;
grant EXECUTE ON  sysdba.pro_grant_all_priv to user4;grant EXECUTE ON  sysdba.PROC_MAIN_PRIV to user1;
grant EXECUTE ON  sysdba.PROC_MAIN_PRIV to user2 ;
grant EXECUTE ON  sysdba.PROC_MAIN_PRIV to user3 ;
grant EXECUTE ON  sysdba.PROC_MAIN_PRIV to user4;--把表的增刪改查權限給4個用戶
grant select,update,delete,insert  ON  "SYSDBA"."PRIV_OWNER_LIST" to user1;
grant select,update,delete,insert  ON  "SYSDBA"."PRIV_OWNER_LIST" to user2;
grant select,update,delete,insert  ON  "SYSDBA"."PRIV_OWNER_LIST" to user3;
grant select,update,delete,insert  ON  "SYSDBA"."PRIV_OWNER_LIST" to user4;

以上就是需求的實現過程