表中特权查询之间的区别 [英] Difference between query of privileges in tables

问题描述

阅读此答案 r我找到一个查询来查找表的特权:

Reading this answer I found a query to find the privileges of a table:

SELECT * FROM ALL_TAB_PRIVS WHERE TABLE_NAME = 'MY_TABLE'

但是有朋友给我这个查询:

But a friend give me to me this query:

select a.USERNAME,a.GRANTED_ROLE,b.ROLE,b.owner,b.TABLE_NAME,b.privilege
from user_role_privs a,role_tab_privs b
where b.ROLE=a.GRANTED_ROLE and b.TABLE_NAME = 'MY_TABLE';

每个查询之间有实质性的区别吗?

There is some substantial difference between each query?

推荐答案

它们是完全不同的.

在Oracle中，可以将表上的特权直接授予用户(在这种情况下，它们将出现在ALL_TAB_PRIVS中)，也可以将特权授予一个角色(在ROLE_TAB_PRIVS中可见)，并且可以将该角色授予授予用户(在USER_ROLE_PRIVS中可见).第一个查询将向您显示在表上具有直接授权的用户.第二个查询将向您显示已被授予角色访问该表权限的用户(请注意，在两种情况下，除了表名之外，您还应该指定OWNER).都不会向您显示有关通过多个嵌套级别的角色进行的授予的信息(即，已向用户A授予角色1，向角色1授予角色2，向角色2授予对表的访问权限).通过角色进行的授予也会变得有些棘手，因为存在默认和非默认角色，并且受密码保护的角色以及可以在会话中启用和禁用角色.

In Oracle, privileges on a table can be granted either directly to a user (in which case they would appear in ALL_TAB_PRIVS) or privileges can be granted to a role (visible in ROLE_TAB_PRIVS) and that role can be granted to a user (visible in USER_ROLE_PRIVS). The first query will show you the users that have direct grants on a table. The second query will show you the users that have been granted a role that has been granted access to the table (note that in both cases you really ought to specify an OWNER in addition to the table name). Neither will show you information about grants that have been made through multiple nested levels of roles (i.e. User A has been granted Role 1, Role 1 has been granted Role 2, Role 2 has been granted access to a table). Grants made via roles can also get a bit tricky because there are default and non-default roles and password protected roles and roles can be enabled and disabled in a session.

通常，我建议您看看Pete Finnigan网站上提供的脚本如果您想拥有一种涵盖所有可能情况的东西.在这种情况下，您可能想使用他的 who_can_access脚本确定哪些用户可以访问特定表

In general, I'd suggest taking a look at the scripts available on Pete Finnigan's site if you want to have something that covers all the possible cases. In this case, you probably want to use his who_can_access script to determine what users can access a particular table.

这篇关于表中特权查询之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！