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

Question

Hi, 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

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?

Answer 1

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

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.

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')
/

Answer 2

Firstly create sample users "user_a" and "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:

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:

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:

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:

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:

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

Answer 3

Hi Kalamro,

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

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

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

Lastly, check DBA_SYS_PRIVS to see if some global privilege is granted to a user/role. Most global privileges have ANY in 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.

---

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:

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))