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?
每个查询之间有一些实质性的区别吗?
1 个解决方案
#1
2
They are quite different, yes.
他们是完全不同的,是的。
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.
在Oracle中,可以直接向用户授予对表的权限(在这种情况下,它们将显示在ALL_TAB_PRIVS中),或者可以将权限授予角色(在ROLE_TAB_PRIVS中可见),并且可以将该角色授予用户(在USER_ROLE_PRIVS)。第一个查询将显示在表上具有直接授权的用户。第二个查询将向您显示已被授予已被授予对表的访问权限的角色的用户(请注意,在这两种情况下,除了表名之外,您确实应该指定一个OWNER)。两者都不会显示有关通过多个嵌套级别的角色进行的授权的信息(即,用户A已被授予角色1,角色1已被授予角色2,角色2已被授予对表的访问权限)。通过角色进行的授权也会变得有点棘手,因为可以在会话中启用和禁用默认角色和非默认角色以及受密码保护的角色和角色。
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.
一般来说,我建议你看一下Pete Finnigan网站上提供的脚本,如果你想拥有涵盖所有可能情况的东西。在这种情况下,您可能希望使用他的who_can_access脚本来确定哪些用户可以访问特定表。
#1
2
They are quite different, yes.
他们是完全不同的,是的。
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.
在Oracle中,可以直接向用户授予对表的权限(在这种情况下,它们将显示在ALL_TAB_PRIVS中),或者可以将权限授予角色(在ROLE_TAB_PRIVS中可见),并且可以将该角色授予用户(在USER_ROLE_PRIVS)。第一个查询将显示在表上具有直接授权的用户。第二个查询将向您显示已被授予已被授予对表的访问权限的角色的用户(请注意,在这两种情况下,除了表名之外,您确实应该指定一个OWNER)。两者都不会显示有关通过多个嵌套级别的角色进行的授权的信息(即,用户A已被授予角色1,角色1已被授予角色2,角色2已被授予对表的访问权限)。通过角色进行的授权也会变得有点棘手,因为可以在会话中启用和禁用默认角色和非默认角色以及受密码保护的角色和角色。
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.
一般来说,我建议你看一下Pete Finnigan网站上提供的脚本,如果你想拥有涵盖所有可能情况的东西。在这种情况下,您可能希望使用他的who_can_access脚本来确定哪些用户可以访问特定表。