--授予 查询 插入 权限
DECLARE CURSOR CUR_A
IS
SELECT TABLE_NAME FROM USER_TABLES;
V_VAR VARCHAR2( 1000);
BEGIN
OPEN CUR_A;
LOOP
FETCH CUR_A INTO V_VAR;
EXIT WHEN CUR_A%NOTFOUND;
EXECUTE IMMEDIATE 'GRANT SELECT,INSERT ON ' ||V_VAR|| ' TO WXZJ1'; -- SELECT ,INSERT,DELETE ,UPDATE,
END LOOP;
CLOSE CUR_A;
END;
--收回所有权限
DECLARE CURSOR CUR_A
IS
SELECT TABLE_NAME FROM USER_TABLES;
V_VAR VARCHAR2( 1000);
BEGIN
OPEN CUR_A;
LOOP
FETCH CUR_A INTO V_VAR;
EXIT WHEN CUR_A%NOTFOUND;
EXECUTE IMMEDIATE 'REVOKE ALL ON ' ||V_VAR|| ' FROM WXZJ1 ';
END LOOP;
CLOSE CUR_A;
END;
--单个授予
GRANT SELECT ON 表名 TO 用户名
--收回
REVOKE SELECT ON 表名 FROM 用户名
--另外一种写法------------------------------------------------------------------------------------------
DECLARE CURSOR CUR_A
IS SELECT TABLE_NAME FROM USER_TABLES;
V_SQL VARCHAR2( 2000);
BEGIN
FOR TABLENAME IN CUR_A LOOP
V_SQL:= 'GRANT SELECT,INSERT ON ' ||TABLENAME.TABLE_NAME|| ' TO DBO_PROPERTYMANAGEMENTCENTERIM';
EXECUTE IMMEDIATE V_SQL;
END LOOP;
END;
DECLARE CURSOR CUR_A
IS SELECT TABLE_NAME FROM USER_TABLES;
V_SQL VARCHAR2( 2000);
BEGIN
FOR TABLENAME IN CUR_A LOOP
V_SQL:= 'REVOKE SELECT,INSERT ON ' ||TABLENAME.TABLE_NAME|| ' FROM DBO_PROPERTYMANAGEMENTCENTERIM';
EXECUTE IMMEDIATE V_SQL;
END LOOP;
END;
DECLARE CURSOR CUR_A
IS
SELECT TABLE_NAME FROM USER_TABLES;
V_VAR VARCHAR2( 1000);
BEGIN
OPEN CUR_A;
LOOP
FETCH CUR_A INTO V_VAR;
EXIT WHEN CUR_A%NOTFOUND;
EXECUTE IMMEDIATE 'GRANT SELECT,INSERT ON ' ||V_VAR|| ' TO WXZJ1'; -- SELECT ,INSERT,DELETE ,UPDATE,
END LOOP;
CLOSE CUR_A;
END;
--收回所有权限
DECLARE CURSOR CUR_A
IS
SELECT TABLE_NAME FROM USER_TABLES;
V_VAR VARCHAR2( 1000);
BEGIN
OPEN CUR_A;
LOOP
FETCH CUR_A INTO V_VAR;
EXIT WHEN CUR_A%NOTFOUND;
EXECUTE IMMEDIATE 'REVOKE ALL ON ' ||V_VAR|| ' FROM WXZJ1 ';
END LOOP;
CLOSE CUR_A;
END;
--单个授予
GRANT SELECT ON 表名 TO 用户名
--收回
REVOKE SELECT ON 表名 FROM 用户名
--另外一种写法------------------------------------------------------------------------------------------
DECLARE CURSOR CUR_A
IS SELECT TABLE_NAME FROM USER_TABLES;
V_SQL VARCHAR2( 2000);
BEGIN
FOR TABLENAME IN CUR_A LOOP
V_SQL:= 'GRANT SELECT,INSERT ON ' ||TABLENAME.TABLE_NAME|| ' TO DBO_PROPERTYMANAGEMENTCENTERIM';
EXECUTE IMMEDIATE V_SQL;
END LOOP;
END;
DECLARE CURSOR CUR_A
IS SELECT TABLE_NAME FROM USER_TABLES;
V_SQL VARCHAR2( 2000);
BEGIN
FOR TABLENAME IN CUR_A LOOP
V_SQL:= 'REVOKE SELECT,INSERT ON ' ||TABLENAME.TABLE_NAME|| ' FROM DBO_PROPERTYMANAGEMENTCENTERIM';
EXECUTE IMMEDIATE V_SQL;
END LOOP;
END;