database Oracle DB - 如何跨架构/所有者显示权限？

Oracle DB - how to display privs across schemas/owners?

提问by Kalamro

I have a owner A and owner B in the DB. Each of them has its own schemas, etc. If I grant rights to an user from B (so he has e.g. access to some view under A), is there any way how to display privs like this? I mean, one user has some rights under each DB owner and its schematas. The reason for that is that I would need to be sure that no user under B has access to any object under A. Thank you very much

我在数据库中有一个所有者 A 和所有者 B。他们每个人都有自己的模式等。如果我从 B 向用户授予权限（这样他就可以访问 A 下的某些视图），有什么办法可以显示这样的 privs 吗？我的意思是，一个用户在每个数据库所有者及其模式下都有一些权限。这样做的原因是我需要确保 B 下的任何用户都不能访问 A 下的任何对象。非常感谢

EDIT: For more clear question:

编辑：对于更明确的问题：

Sceham owner A               Schema owner B
Table X                      Table CC
Table Y                      Table DD
View Z                       View EE

Now say user John has access to schemas under owner A but also to one view under schema owned by Oracle owner B. How could I list his rights across schemas?

现在假设用户 John 可以访问所有者 A 下的模式，但也可以访问 Oracle 所有者 B 拥有的模式下的一个视图。我如何列出他跨模式的权限？

采纳答案by Vincent Malgrat

In Oracle a user is a schema (USER=SCHEMA).

在 Oracle 中，用户是一个模式 (USER=SCHEMA)。

You can query the dictionary table DBA_TAB_PRIVSto see what users/roles have access to. Alternatively, if you're connected as user A, querying USER_TAB_PRIVSwill let you see which users/roles have access to your objects (tables, procedures, packages, etc...).

您可以查询字典表DBA_TAB_PRIVS以查看用户/角色有权访问的内容。或者，如果您以用户 A 的身份连接，查询USER_TAB_PRIVS将让您查看哪些用户/角色可以访问您的对象（表、过程、包等）。

If any privilege is given to a role, check DBA_ROLE_PRIVSto see which user is granted this role.

如果赋予角色任何权限，请检查DBA_ROLE_PRIVS哪个用户被授予此角色。

Lastly, check DBA_SYS_PRIVSto see if some global privilege is granted to a user/role. Most global privileges have ANYin there name (SELECT ANY TABLE, EXECUTE ANY PROCEDURE...). Global privileges take precedence over individual privileges and may let you access either directly or indirectly data accross all schemas.

最后，检查是否DBA_SYS_PRIVS向用户/角色授予了某些全局权限。大多数全局权限都ANY在名称中 ( SELECT ANY TABLE, EXECUTE ANY PROCEDURE...)。全局权限优先于个人权限，可以让您直接或间接访问所有模式中的数据。

---

---

This gets a little more complicated since a role can be granted to another role. This query will list all objects from schema A accessible to user JOHN:

由于可以将一个角色授予另一个角色，因此这会变得稍微复杂一些。此查询将列出用户 JOHN 可访问的模式 A 中的所有对象：

SELECT DISTINCT table_name, PRIVILEGE
  FROM dba_tab_privs
 WHERE owner = 'A'
   AND (grantee = 'JOHN' OR
       grantee IN (SELECT dr.granted_role
                      FROM dba_role_privs dr
                     START WITH grantee = 'JOHN'
                    CONNECT BY PRIOR dr.granted_role = dr.grantee))

回答by APC

You would need to use one of the DBA views to do this.

您需要使用 DBA 视图之一来执行此操作。

select privilege
       , table_name 
from dba_tab_privs
where grantee = 'B'
and owner = 'A'
/

If you use roles then you may also want to check that:

如果您使用角色，那么您可能还需要检查：

select r.granted_role
       , p.privilege
       , p.table_name
from dba_role_privs r
     join dba_tab_privs p
     on r.granted_role = p.grantee
where r.grantee = 'B'
and p.owner = 'A'
/

---

---

"How could I list his rights across schemas?"

“我怎么能列出他跨模式的权利？”

Like this:

像这样：

select owner
       , privilege
       , table_name 
from dba_tab_privs
where grantee = 'JOHN'
and owner IN ( 'A', 'B' )
order by owner, table_name
/

You might want to omit the filter on OWNER. A similar tweak on the second query will give you granted roles across schemas.

您可能希望省略 OWNER 上的过滤器。对第二个查询的类似调整将为您授予跨模式的角色。

select r.granted_role
       , p.privilege
       , p.table_name
from dba_role_privs r
     join dba_tab_privs p
     on r.granted_role = p.grantee
where r.grantee = 'JOHN'
and p.owner in ( 'A' , 'B')
/

回答by Martin Mares

Firstly create sample users "user_a" and "user_b":

首先创建示例用户“user_a”和“user_b”：

SQL> create user user_a identified by user_a default tablespace users temporary tablespace temp;
SQL> create user user_b identified by user_b default tablespace users temporary tablespace temp;
SQL> grant connect to user_a, user_b;
SQL> grant create session to user_a, user_b;
SQL> grant create table to user_a, user_b;
SQL> grant create view to user_a, user_b;
SQL> alter user user_a quota unlimited on users;
SQL> alter user user_b quota unlimited on users;

Now connect as USER_A and create sample objects:

现在以 USER_A 身份连接并创建示例对象：

SQL> conn user_a/user_a
Connected.
SQL> create table tbl_a(id number, text varchar2(200));
Table created.
SQL> create view view_a as select id, text from tbl_a;
View created.

Then connect as USER_B and create sample objects:

然后以 USER_B 身份连接并创建示例对象：

SQL> conn user_b/user_b
Connected.
SQL> create table tbl_b(id number, text varchar2(200));
Table created.
SQL> create view view_b as select id, text from tbl_b;
View created.

And finally connect back as USER_A a search grants for his table TBL_A:

最后以 USER_A 的身份连接回他的表 TBL_A 的搜索授权：

SQL> conn user_a/user_a
Connected.
SQL> select count(1) from ALL_TAB_PRIVS where grantor = 'USER_A' and PRIVILEGE = 'SELECT' and GRANTEE = 'USER_B';

Out is:

出来是：

  COUNT(1)
----------
         0

Now grant select on view VIEW_A for USER_B:

现在为 USER_B 在视图 VIEW_A 上授予选择权：

SQL> grant select on view_a to user_b;
Grant succeeded.

And again try search grants for USER_B to view object VIEW_A from USER_A:

再次尝试搜索 USER_B 以查看来自 USER_A 的对象 VIEW_A：

SQL> select count(1) from ALL_TAB_PRIVS where grantor = 'USER_A' and PRIVILEGE = 'SELECT' and GRANTEE = 'USER_B';

And now result is:

现在的结果是：

  COUNT(1)
----------
         1