方法1：

新版本有一個數據庫參數

?GRANT_SCHEMA，表示是否開啟授予和回收模式權限功能。0：否；1：是

此參數為靜態參數，默認是0，將改參數修改為1后，重啟數據庫生效。

將參數修改為1

SP_SET_PARA_VALUE(2,'GRANT_SCHEMA',1);

參數生效后，可直接執行SQL進行授權，例如將模式 B 下所有對象的查詢權限授予用戶 A

GRANT SELECT ON SCHEMA B TO A;

方法2：

可以通過創建一個存儲過程來實現：

CREATE OR REPLACE PROCEDURE GRANT_ALL_SEL(GRANTOR IN VARCHAR2, GRANTEE IN VARCHAR2) ASSQLSTMT VARCHAR2(4000);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 = GRANTOR);TYPE MYREC IS CUR%ROWTYPE;REC_V MYREC;TYPE R_PRIV IS RECORD (CUR_PRIV_NAME VARCHAR2(128), CUR_BE_GRANT CHAR(1), CUR_CAN_GRANT CHAR(1), CUR_IS_GRANT CHAR(1), ORG_PRIV_NAME VARCHAR2(128), ORG_CAN_GRANT CHAR(1)); TYPE T_PRIV IS TABLE OF R_PRIV;t_p T_PRIV;OBJ_PRIV CURSOR;
BEGINFOR REC_V IN CUR LOOPOPEN OBJ_PRIV FORWITH CUR_PRIVS(PRIV_NAME, BE_GRANT, CAN_GRANT, ISGRANT) AS(SELECT 'SELECT', 'Y', 'N', 'Y' FROM DUAL )SELECT CUR_PRIVS.*, SF_GET_SYS_PRIV(ORG_PRIV.PRIVID), ORG_PRIV.GRANTABLEFROM CUR_PRIVSLEFT JOIN (SELECT * FROM SYSGRANTS WHERE OBJID = REC_V.ID AND COLID = -1 AND URID = 50331751) ORG_PRIV ON CUR_PRIVS.PRIV_NAME = SF_GET_SYS_PRIV(ORG_PRIV.PRIVID);FETCH OBJ_PRIV BULK COLLECT INTO t_p;CLOSE OBJ_PRIV;FOR I IN 1..t_p.COUNT LOOPSQLSTMT := NULL;IF (t_p(I).CUR_IS_GRANT = 'Y') THENIF (t_p(I).CUR_CAN_GRANT = 'Y') THENSQLSTMT := 'GRANT ' || t_p(I).CUR_PRIV_NAME || ' ON "' || GRANTOR || '"."' || REC_V.NAME || '" TO "' || GRANTEE || '" WITH GRANT OPTION';ELSEIF (t_p(I).ORG_PRIV_NAME IS NULL) THENSQLSTMT := 'GRANT ' || t_p(I).CUR_PRIV_NAME || ' ON "' || GRANTOR || '"."' || REC_V.NAME || '" TO "' || GRANTEE || '"';ELSEIF (t_p(I).ORG_CAN_GRANT = 'Y') THENSQLSTMT := 'REVOKE GRANT OPTION FOR ' || t_p(I).CUR_PRIV_NAME || ' ON "' || GRANTOR || '"."' || REC_V.NAME || '" FROM "' || GRANTEE || '" CASCADE';END IF;END IF;END IF;ELSEIF (t_p(I).CUR_BE_GRANT = 'Y' AND t_p(I).ORG_PRIV_NAME IS NOT NULL) THENSQLSTMT := 'REVOKE ' || t_p(I).CUR_PRIV_NAME || ' ON "' || GRANTOR || '"."' || REC_V.NAME || '" FROM "' || GRANTEE || '" CASCADE';ELSEIF (t_p(I).CUR_CAN_GRANT = 'Y' AND t_p(I).ORG_CAN_GRANT = 'Y') THENSQLSTMT := 'REVOKE GRANT OPTION FOR ' || t_p(I).CUR_PRIV_NAME || ' ON "' || GRANTOR || '"."' || REC_V.NAME || '" FROM "' || GRANTEE || '" CASCADE';END IF;END IF;END IF;IF (SQLSTMT IS NOT NULL) THENEXECUTE IMMEDIATE SQLSTMT;END IF;END LOOP;END LOOP;COMMIT;
END GRANT_ALL_SEL;

然后調用此存儲過程即可，例如將模式 B 下所有對象的查詢權限授予用戶 A

CALL GRANT_ALL_SEL('B','A');