delete

Show objects without necessary grants

Simple query to check objects which do not have some specified grants: SELECT DISTINCT O.OBJECT_NAME FROM ALL_OBJECTS O WHERE O.OWNER = '...your_schema...' AND O.OBJECT_NAME LIKE '...some_mask...%' AND O.OBJECT_TYPE IN ('TABLE', 'VIEW', 'MATERIALIZED VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'PACKAGE BODY', 'TYPE') MINUS SELECT DISTINCT T.TABLE_NAME FROM ALL_TAB_PRIVS T WHERE T.TABLE_SCHEMA = '...your_schema...' AND T.TABLE_NAME LIKE '...some_mask...%' AND (T.GRANTEE NOT IN ( '...role_name...', '...role_name....')...
delete

Create grant command for table / view ba...

It is just small stupid hint but sometimes it can help. This query gives you grant commands for some table / view based on other object: SELECT ‘ GRANT ‘ || PRIVILEGE || ‘ ON ‘ || ‘…your_target_object_with_schema_name….’ || ‘ TO ‘ || GRANTEE || DECODE (GRANTABLE, ‘YES’ , ‘ WITH GRANT OPTION’, ”) || ‘;’ FROM ALL_TAB_PRIVS WHERE TABLE_SCHEMA = ‘…source_object_schema…’ AND TABLE_NAME = ‘…source_object….’...
delete

Objects without grants

Sometimes (usualy during tests 🙂 ) it is useful to check if there are some objects which do not have any grants – you can avoid many problems this way. SELECT DISTINCT O.OBJECT_NAME FROM ALL_OBJECTS O WHERE O.OWNER = '...schema_name...' AND O.OBJECT_TYPE IN ('TABLE', 'VIEW', 'MATERIALIZED VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'PACKAGE BODY', 'TYPE') MINUS SELECT DISTINCT T.TABLE_NAME FROM ALL_TAB_PRIVS T WHERE T.TABLE_SCHEMA = '...schema_name...' --AND T.GRANTEE <> 'PUBLIC'  --uncomment this if you want to ignore grants for...
delete

Simple SQL*Plus script to grant select t...

If you for example have some “date warehouse”-like schema which everyone should read you usualy give the object select to public grant. Beleive it or not in our old testing Oracle 9 database sometime happend that grants dissappered and no developer was willing to admit his “guilt”… 🙂 Therefore I used from time to time this script check / repair if all tables have grant for select to public. But you can easily modify it for other user. set define off set serveroutput on spool Doplneni_prav.log prompt Doplneni prav select pro tabulky IRS pro...
delete

Check if objects have / don’t have...

Shows not only tables: select distinct t.TABLE_NAME from all_tab_privs t where t.TABLE_SCHEMA = '...your_schema...' and t.table_name like '...your_mask...' and not exists --or just exists -depends on what you want to check (select * from all_tab_privs x where x.GRANTEE='PUBLIC' and x.TABLE_SCHEMA= t.TABLE_SCHEMA and x.TABLE_NAME=t.table_name)