How to audit table permissions as a non-DBA

Question

I'm not a DBA, but I do administer a schema called SCHEMA_1.

I'm in charge of assigning privileges to the tables that I create in my schema. Since there aren't any sensitive or permission-unique tables in the schema, the DBA has instructed me to assign the same privileges to all tables.

Every table has these privileges:

1. ROLE_A has SELECT
2. ROLE_B has SELECT, INSERT, UPDATE, DELETE
3. ROLE_C has SELECT, INSERT, UPDATE, DELETE
4. SCHEMA_2 has SELECT

I would like to audit the tables in my schema to ensure that I haven't accidentally assigned the wrong privileges to any tables. I'm not sure how this is normally done by DBA's, but one idea I had was to tabulate the tables and privileges like this:

+------------+----------------+--------+--------+--------+--------+----------------+
| TABLE_NAME | ROLE_OR_SCHEMA | SELECT | INSERT | UPDATE | DELETE | OTHER (ERRORS) |
+------------+----------------+--------+--------+--------+--------+----------------+
| TABLE_1    | ROLE_A         | y      |        |        |        |                |
| TABLE_1    | ROLE_B         | y      | y      | y      | y      |                |
| TABLE_1    | ROLE_C         | y      | y      | y      | y      |                |
| TABLE_1    | SCHEMA_2       | y      |        |        |        |                |
+------------+----------------+--------+--------+--------+--------+----------------+
| TABLE_2    | ROLE_A         | y      |        |        |        |                |
| TABLE_2    | ROLE_B         | y      | y      | y      | y      |                |
| TABLE_2    | ROLE_C         | y      | y      | y      | y      |                |
| TABLE_2    | SCHEMA_2       | y      |        |        |        |                |
+------------+----------------+--------+--------+--------+--------+----------------+
| TABLE_3    | ROLE_A         | y      |        |        |        |                |
| TABLE_3    | ROLE_B         | y      | y      | y      | y      |                |
| TABLE_3    | ROLE_C         | y      | y      | y      | y      | OTHERS! ERROR! |
| TABLE_3    | SCHEMA_2       | y      |        |        |        |                |
+------------+----------------+--------+--------+--------+--------+----------------+

The reason that I've suggested this sort of layout is because this is how I normally see the privileges when I set them using my GIS software:

Assuming that this is a logical approach (it might not be), how can I do this?

The only tools I have at my disposal are my GIS software and SQL Developer.

Answer 1

You need to connect to schema_1 to run this query. There is a helpful view user_tab_privs. If you are on 11g or higher, then listagg function can show the list of other privileges. Otherwise remove row with listagg form query

select table_name, grantee , 
        max(sel) sel, max(ins) ins , max(upd) upd, max(del) del,
        case when max(other_privs) is not null then 'other' end other_privs,
        listagg(other_privs,',') within group (order by other_privs) as list_of_other_privs
 from 
 (select table_name, grantee, 
        case when privilege='SELECT' then 'y' end sel, 
        case when privilege='INSERT' then 'y' end ins, 
        case when privilege='UPDATE' then 'y' end upd, 
        case when privilege='DELETE' then 'y' end del,
        case when privilege not in ('SELECT', 'INSERT','UPDATE','DELETE') then privilege end other_privs 
   from user_tab_privs
 ) 
  group by table_name, grantee 
  order by table_name, grantee