公司网站市场价,anylink wordpress,佛山新网站制作特色,网站扫码怎么做方法1#xff1a;
新版本有一个数据库参数 GRANT_SCHEMA#xff0c;表示是否开启授予和回收模式权限功能。0#xff1a;否#xff1b;1#xff1a;是
此参数为静态参数#xff0c;默认是0#xff0c;将改参数修改为1后#xff0c;重启数据库生效。
将参数修改为1
S…方法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);
