How to find missing grant on all tables for one role

Question

i have some problem on my student Database schema. I want to find with query which Tables don't have: for example 'SELECT' grant to role XXX. Second example is that in Tables i have like Grants for delete,alter but now i want to check all Tables with one query to find which Tables don't have Select grant to role 'STUDENT_DBA' or where this role don't have grant for Select... Please help 😅😅😅

Answer 1

SELECT table_name
  FROM dba_tables
 WHERE owner = 'STUDENT'
 AND table_name NOT IN
     (SELECT table_name
        FROM dba_tab_privs
       WHERE owner = 'STUDENT'
         AND privilege = 'SELECT'
         AND grantee = 'STUDENT_DBA');

This will return all tables in the STUDENT schema that do not have select permissions directly granted to the STUDENT_DBA role.