CODEWITHSUNDEEP
sqloracle-database
Rahul Vijay Dawda
Rahul Vijay Dawda

Reputation: 1203

Listing all tables a user has query rights on in Oracle

I'm trying to query the Oracle database to retrieve a list of tables in all available schemas that the connected user has query rights on. The following query gives me a list of all tables in all schemas but I need to filter these results based on the level of privilege.

select OWNER || '.' || TABLE_NAME from SYS.ALL_TABLES where SECONDARY='N'

It's fine if the user has higher privileges than just select on other schemas/tables but select is a minimum requirement. What complicates this further for me is that a user may only have permissions on certain tables of a schema. I'm not sure how I can filter out such tables as well in my result.

Upvotes: 1

Views: 3487

Answers (1)

Aramillo
Aramillo

Reputation: 3226

Try this:

SELECT OWNER || '.' || TABLE_NAME
  FROM SYS.ALL_TABLES
 WHERE SECONDARY = 'N' AND OWNER = 'USER1'
UNION ALL
SELECT OWNER || '.' || TABLE_NAME
  FROM dba_tab_privs
 WHERE GRANTEE = 'USER1' AND privilege = 'SELECT'

This give you all tables which user1 is owner and tables which user1 can select. I hope this helps

Upvotes: 2

Related Questions