How to see what privileges are granted to schema of another user

Question

Consider the case   In a database   I have two users A and B and their corresponding schema   I want to know   How can I get the information   what permissions are there for USER A in Schema B    Consider the case   We have two users and their associated scehmas  We have user A and user B  In A  say we have TB1 TB2   in B say we have TBa  TBb   Now I want to know how can I find what privileges User A has on Schema B    For example   User A is writing   select   from B TBb   This means USER A is accessing User B s table so   it shows he has SELECT Privilege  I want to know what all privileges User A has on Schema B    Which query shall be executed to get the list of privileges that User A has on Schema B

User · Answer

Use example with from the post of Szil  gyi Don  t   I use two querys  one to know what roles I have  excluding connect grant   SELECT   FROM USER ROLE PRIVS WHERE GRANTED ROLE     CONNECT   -- Roles of the actual Oracle Schema   Know I like to find what privileges roles my schema user have  examples of my roles ROLE VIEW PAYMENTS  amp  ROLE OPS CUSTOMERS  But to find the tables objecst of an specific role I used   SELECT   FROM ALL TAB PRIVS WHERE GRANTEE  ROLE OPS CUSTOMERS   -- Objects granted at role   The owner schema for this example could be PRD CUSTOMERS OWNER  or the role schema inself    Regards

User · Answer

Login into the database  then run the below query   select   from dba role privs where grantee    SCHEMA NAME    All the role granted to the schema will be listed    Thanks Szilagyi Donat for the answer  This one is taken from same and just where clause added

User · Answer

You can use these queries   select   from all tab privs  select   from dba sys privs  select   from dba role privs    Each of these tables have a grantee column  you can filter on that in the where criteria   where grantee    A    To query privileges on objects  e g  tables  in other schema I propose first of all all tab privs  it also has a table schema column    If you are logged in with the same user whose privileges you want to query  you can use user tab privs  user sys privs  user role privs  They can be queried by a normal non-dba user

[oracle] How to see what privileges are granted to schema of another user

Examples related to oracle