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:
ROLE_A
hasSELECT
ROLE_B
hasSELECT, INSERT, UPDATE, DELETE
ROLE_C
hasSELECT, INSERT, UPDATE, DELETE
SCHEMA_2
hasSELECT
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.