数据库中批量授权与回收权限

来源：互联网发布：太极越狱9.32mac 编辑：程序博客网时间：2024/05/08 20:19

--授予查询插入权限
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;

0 0