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

show us what you tried so far. Check `dba_role_privs` and `dba_tab_privs`. If you don't have dba privileges, replace dba_ by all_ — Roberto Hernandez, Oct 11 '21 at 16:26

score 0 · Accepted Answer · answered Oct 11 '21 at 18:42

0

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.

answered Oct 11 '21 at 18:42

MplsDBA

86
4

Thanks a lot!! I was trying some query like this buy didn't work... I think because i waS trying with dba_tab_privs and all_tab_privs without dba_tables :( Forgotten about this – Jelonek512 Oct 12 '21 at 06:37

How to find missing grant on all tables for one role

1 Answers1