一次撤销多个用户的Oracle权限 [英] Revoking Oracle Permissions to multiple users at once

问题描述

我们正在合理化数据库用户权限，为此，我们希望撤销授予所有用户(但不是特定角色)的架构中所有表的所有选择权限.

We're rationalising our database user permissions and to that end we'd like to revoke all select permissions across all tables in a schema granted to all users (but not a specific role).

我尝试使用一些正则表达式为每个表创建一个通用的revoke，其内容如下:

With some regular expressions I've tried creating a universal revoke for each table giving something like:

从USER1，USER2，USER3 ...撤消TABLE1上的选择;

revoke select on TABLE1 from USER1,USER2,USER3...;

从USER1，USER2，USER3 ...撤消TABLE2上的选择;

revoke select on TABLE2 from USER1,USER2,USER3...;

但是，并非所有用户都被授予了对所有表的权限，这会导致oracle错误:

However as not all users were granted permissions to all tables, this results in the oracle error:

01927. 00000 - "cannot REVOKE privileges you did not grant"

有意义但无济于事.

我还尝试使用All_Tab_Privs来获取所有表的列表，这些表具有要撤销的必需授予，并且可以，但是我看不出如何轻松地从中获得实际撤销权限.

I also tried using All_Tab_Privs to get a list of all tables with the requisite grants to be revoked, and this works, but I can't see how to easily get from this to actually revoking the permissions.

SELECT * From All_Tab_Privs where grantor = 'GRANTING_USER' and grantee != 'READROLE' and grantee != 'PUBLIC' and grantee != 'SYS';

是否有一些建议，而又不用花几个小时在电子表格上呢?我在猜一些PL/SQL?谢谢.

Any suggestions for how to do this without spending hours in a spreadsheet? I'm guessing some PL/SQL? Thanks.

推荐答案

这种工作的一般模式是使用隐式游标返回所需的数据，然后构建DDL并执行它们.

A general pattern for this kind of job is to use an implicit cursor to return the data you want and then to build the DDL and execute them.

begin
  for x in (select attribute_1, attribute_2 ... from ... where ...)
  loop
    execute immediate 'REVOKE ...'||x.attribute1||' etc';
  end loop;
end;
/

一个不错的选择是构建要在SELECT中执行的SQL，然后直接执行.测试起来更容易，但是SQL看上去有点笨拙.

A nice alternative is to build the SQL you want to execute in the SELECT and then just execute that. It's easier to test but the SQL then looks a bit clunky.

这篇关于一次撤销多个用户的Oracle权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！