Simple SQL*Plus script to grant select to public

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 PUBLIC

BEGIN
FOR INDEx_RECORD IN (
SELECT 'grant select on '||TABLE_NAME||' to PUBLIC' as set_grants
FROM (
SELECT OBJECT_NAME AS TABLE_NAME
FROM ALL_OBJECTS WHERE OWNER='...your_schema...' AND OBJECT_TYPE = 'TABLE' AND OBJECT_NAME LIKE '...mask_of_object_names...%'
MINUS
select TABLE_NAME from TABLE_PRIVILEGES
where owner = '...your_schema....' AND TABLE_NAME LIKE '...mask_of_object_names...%' and SELECT_PRIV = 'Y'
AND GRANTEE = 'PUBLIC' )
)
LOOP
dbms_output.put_line(INDEx_RECORD.set_grants);
execute immediate INDEx_RECORD.set_grants;
END LOOP;
END;
/

Leave a Reply

Your email address will not be published. Required fields are marked *