一、背景
在某项目现场，开发商想实现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;以上就是需求的实现过程
