0

In Oracle, i need list which schemas a particular database user (or the current database user) has read permission.

Based on this information, also need to get all the tables and views are in each schema, provided that the particular database user has read permission.

3
  • Users do not have privileges on schemas. Users have privileges on objects (directly or (potentially recursively) through roles). Are you asking for the set of schemas that a particular user has access to at least one object in that schema? Or to all the objects in that schema? What roles, if any, do you want to include in this analysis (i.e. non-default roles, password-protected roles, etc.)? Do you need to include the various ANY system privileges (i.e. SELECT ANY TABLE)? Jan 2, 2015 at 18:24
  • @JustinCave I'm studying ways to get information about schemas and permissions, with the objective of create a patch to resolve the problem of multiple database schema on Django ORM. code.djangoproject.com/ticket/6148 So I do not know exactly what to look for. Jan 22, 2015 at 2:08
  • Unfortunately, if you're not sure what you're looking for, it's hard for anyone else to guess what you're looking for. My wild guess is that you want to convert any code that is looking at user_tables (or any user_* data dictionary tables) to use the equivalent all_tables (or all_* views) and then prefix the schema name when building queries. But that's a pretty wild guess based on skimming the description of the bug you're trying to fix. Jan 22, 2015 at 3:22

2 Answers 2

1

you can use the following command if you want to see the users that has X privilege for example SELECT ANY TABLE privileges

SELECT * FROM USER_ROLE_PRIVS WHERE GRANTED_ROLE like '%SELECT ANY TABLE%'; 
0

Would this work?

select owner,table_name 
from dba_tab_privs 
where privilege = 'SELECT' 
and grantee = UPPER('&SCHEMA');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.