SELECT ANY TABLE 权限如何在 Oracle 中工作？

How SELECT ANY TABLE privilege work in Oracle?

提问by LostReality

I would like to know how the privilege SELECT ANY TABLEworks internally in Oracle.

我想知道该权限SELECT ANY TABLE在 Oracle 内部是如何工作的。

Is it treated as a single privilege? Or is it equivalent to make a GRANT SELECT ON MyTable TO MyUserfor each table?

是否将其视为单一特权？还是相当于GRANT SELECT ON MyTable TO MyUser为每个表制作一个？

As example, I would like to know if this work :

例如，我想知道这是否有效：

GRANT SELECT ANY TABLE TO PUBLIC;
REVOKE ALL ON MY_TABLE FROM PUBLIC;

Would I still have access to MY_TABLEfrom any user after those queries?

MY_TABLE在这些查询之后，我是否仍然可以访问任何用户？

回答by Alex Poole

Yes, all users would still be able to query MY_TABLE.

是的，所有用户仍然可以查询MY_TABLE。

You are looking at different privilege types:

您正在查看不同的权限类型：

The main types of user privileges are as follows:

• System privileges—A system privilege gives a user the ability to perform a particular action, or to perform an action on any schema objects of a particular type. For example, the system privilege CREATE TABLEpermits a user to create tables in the schema associated with that user, and the system privilege CREATE USERpermits a user to create database users.
• Object privileges—An objectprivilege gives a user the ability to perform a particular action on a specific schema object. Different object privileges are available for different types of schema objects. The privilege to select rows from the EMPLOYEEStable or to delete rows from the DEPARTMENTStable are examples of object privileges.

用户权限的主要类型如下：

• 系统权限——系统权限使用户能够执行特定操作，或对特定类型的任何模式对象执行操作。例如，系统权限CREATE TABLE允许用户在与该用户关联的模式中创建表，而系统权限CREATE USER允许用户创建数据库用户。
• 对象权限——对象权限使用户能够对特定模式对象执行特定操作。不同的对象权限可用于不同类型的模式对象。从EMPLOYEES表中选择行或从表中删除行的DEPARTMENTS权限是对象权限的示例。

SELECT ANY TABLEis a system privilege that allows the grantee to:

SELECT ANY TABLE是一种系统特权，允许受让人：

Query tables, views, or materialized views in any schema except SYS. Obtain row locks using a SELECT ... FOR UPDATE.

查询任何模式中的表、视图或物化视图，除了SYS. 使用SELECT ... FOR UPDATE.

When you grant that it is a standalone single privilege, visible in dba_sys_privs. When Oracle decides if the user is allowed to access a table it can look first at system privleges, and only goes on to look for specific object privileges (visible in dba_tab_privs) if there isn't a system privilege that allows the action being performed.

当您授予它是独立的单一权限时，在dba_sys_privs. 当 Oracle 决定是否允许用户访问表时，它可以首先查看dba_tab_privs系统权限，如果没有允许执行操作的系统权限，则仅继续查找特定对象权限（在 中可见）。

System privileges are not translated into individual privileges on each object in the database - maintaining that would be horrible, as creating a new object would have to automatically figure out who should be granted privileges on it based on the system privilege; and it would mean that you couldn't tell the difference between that and individually granted privileges. So, for instance, if you explicitly granted select privs on a specific table, then the user was granted SELECT ANY TABLE, and then they had SELECT ANY TABLErevoked - what happens to the previous explicit grant?

系统权限不会转换为数据库中每个对象的单独权限 - 维护这将是可怕的，因为创建新对象必须根据系统权限自动确定应该授予谁的权限；这意味着您无法区分这与单独授予的特权之间的区别。因此，例如，如果您在特定表上明确授予 select privs，那么用户被授予SELECT ANY TABLE，然后他们已SELECT ANY TABLE撤销 - 先前的显式授予会发生什么？

Your scenario is basically the same, except you've specifed all privileges on the object to be revoked. If those are the only two commands involved then PUBLIChas no explicit privileges on MY_TABLEso revoking doesn't really do anything; but if any explicit privileges on that table had been granted then they would be revoked. That has no impact on the higher-level SELECT ANY TABLEsystem privileg though.

您的场景基本相同，只是您已指定要撤销的对象的所有权限。如果这些是唯一涉及的两个命令，则PUBLIC没有明确的权限，MY_TABLE因此撤销并没有真正做任何事情；但如果已授予对该表的任何显式特权，则它们将被撤销。不过，这对更高级别的SELECT ANY TABLE系统特权没有影响。

Privileges are cummulative; revoking a privilege on a specific object doesn't blockaccess to that object, it just removes one possible access route.

特权是累积的；撤销对特定对象的特权不会阻止对该对象的访问，它只是删除了一个可能的访问路径。

Incidentally, hopefully you've used a contrived example, as such powerful system privileges should be granted sparingly and only when really needed. Letting any user query any table in your database potentially blows a big hole in the security model. Again from the docs:

顺便提一下，希望您使用了一个人为的示例，因为应该谨慎地授予如此强大的系统特权，并且仅在真正需要时才授予。让任何用户查询数据库中的任何表可能会在安全模型中造成一个大漏洞。再次来自文档：

Oracle recommends that you only grant the ANYprivileges to trusted users

Oracle 建议您只将ANY权限授予受信任的用户

and

和

Oracle recommends against granting system privileges to PUBLIC.

Oracle 建议不要向PUBLIC.

and read more in the database security guide.

并在数据库安全指南中阅读更多内容。