一、需求背景

开发商希望提供的4个用户，它们之间有彼此表的查询，删除，修改等权限、存储过程的执行权限。因为用户较多，手动赋予权限，存在繁琐且容易疏漏的问题，因此根据描述的需求，编写存储过程解决，此方法不仅限于4个用户之前的授权，可按自己的需求进行权限配置。

二、相关脚本

1.创建配置表

 --DROP if exist  TABLE "SYSDBA"."PRIV_OWNER_LIST" ;
CREATE  TABLE "SYSDBA"."PRIV_OWNER_LIST"
(	ID INT IDENTITY(1,1),
	"S_USER" varCHAR(20) NOT NULL ,
	"T_USER" varCHAR(20) NOT NULL  
);
 

2.初始化数据：用户间权限的映射关系

--配置数据说明：将T_USER的对象的相关的权限，给S_USER
 INSERT INTO "SYSDBA"."PRIV_OWNER_LIST" (S_USER,T_USER)VALUES('USER1','USER2');
 INSERT INTO "SYSDBA"."PRIV_OWNER_LIST" (S_USER,T_USER)VALUES('USER1','USER3');
 INSERT INTO "SYSDBA"."PRIV_OWNER_LIST" (S_USER,T_USER)VALUES('USER1','USER4');
 
 INSERT INTO "SYSDBA"."PRIV_OWNER_LIST" (S_USER,T_USER)VALUES('USER2','USER1'); 
 INSERT INTO "SYSDBA"."PRIV_OWNER_LIST" (S_USER,T_USER)VALUES('USER2','USER3');
 INSERT INTO "SYSDBA"."PRIV_OWNER_LIST" (S_USER,T_USER)VALUES('USER2','USER4');
 
 INSERT INTO "SYSDBA"."PRIV_OWNER_LIST" (S_USER,T_USER)VALUES('USER3','USER1');
 INSERT INTO "SYSDBA"."PRIV_OWNER_LIST" (S_USER,T_USER)VALUES('USER3','USER2');
 INSERT INTO "SYSDBA"."PRIV_OWNER_LIST" (S_USER,T_USER)VALUES('USER3','USER4');
 
 INSERT INTO "SYSDBA"."PRIV_OWNER_LIST" (S_USER,T_USER)VALUES('USER4','USER1');
 INSERT INTO "SYSDBA"."PRIV_OWNER_LIST" (S_USER,T_USER)VALUES('USER4','USER2');
 INSERT INTO "SYSDBA"."PRIV_OWNER_LIST" (S_USER,T_USER)VALUES('USER4','USER3');
 COMMIT;
 
 SELECT * FROM "SYSDBA"."PRIV_OWNER_LIST" ;

3.创建授权的存储过程

create or REPLACE PROCEDURE sysdba.PROC_MAIN_PRIV (SOURCE_USER varchar(100),TARGET_USER varchar(100) )
 is 
 --授予用户表的所有权限
 begin 
 DECLARE
    SQLSTMT 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;
    BEGIN
    FOR REC_V IN CUR LOOP
    SQLSTMT = 'grant SELECT,INSERT,DELETE,UPDATE,REFERENCES,SELECT FOR DUMP,ALTER,INDEX ON '||SOURCE_USER||'.'|| '"'||REC_V.NAME ||'"' || ' to ' || TARGET_USER ||';';
    EXECUTE IMMEDIATE SQLSTMT;
    --PRINT SQLSTMT;
    END LOOP;
    COMMIT;
END;

--授予用户存储过程执行权限
DECLARE
    SQLSTMT_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;
    BEGIN
    FOR REC_V IN CUR LOOP
    SQLSTMT_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.创建遍历配置表进行授权的存储过程

create or replace PROCEDURE sysdba.pro_grant_all_priv ()
is
    cursor user_priv_list is
        select P_USER,S_USER from "SYSDBA"."PRIV_OWNER_LIST";
 
begin
    for  cur_user  in user_priv_list loop
            BEGIN
                call sysdba.proc_main_priv (cur_user.S_USER,cur_user.T_USER);
            end ;
             end loop;
        end;

5.使用sysdba授权存储过程执行权限和表的查看权限 给USER1，USER2，USER3，USER4 这4个用户

grant EXECUTE ON  sysdba.proc_grant_all_priv to USER1;
grant EXECUTE ON  sysdba.proc_grant_all_priv to USER2 ;
grant EXECUTE ON  sysdba.proc_grant_all_priv to USER3 ;
grant EXECUTE ON  sysdba.proc_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;

 
grant select ON  "SYSDBA"."PRIV_OWNER_LIST" to USER1;
grant select ON  "SYSDBA"."PRIV_OWNER_LIST" to USER2;
grant select ON  "SYSDBA"."PRIV_OWNER_LIST" to USER3;
grant select ON  "SYSDBA"."PRIV_OWNER_LIST" to USER4;

三、使用说明

创建表后，使用任意用户执行这个存储过程即可实现需求。

call sysdba.proc_grant_all_priv ()；