2

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:

enter image description here

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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