Dynamic script for grants on a table

--  grants scripts

SPOOL DYNGRANT.OUT
SET HEAD OFF
SET VERIFY OFF
DEFINE TBL='&TABLE_ROLE';
SELECT 'GRANT '
        ||PRIVILEGE||
      ' ON '||
        TABLE_NAME||
      ' TO '||
        GRANTEE||
       ';'
FROM DBA_TAB_PRIVS
WHERE(TABLE_NAME='&&TBL' OR GRANTEE='&&TBL')
ORDER BY TABLE_NAME,GRANTEE
/
SPOOL OFF
SET HEAD ON
SET VERIFY ON
UNDEFINE OWNER
UNDEFINE TABLE
UNDEFINE TBL



-- Revoke script


SPOOL DYNREVOK.OUT
SET HEAD OFF
SET VERIFY OFF
DEFINE TBL='&TABLE_ROLE';
SELECT 'REVOKE '
        ||PRIVILEGE||
      ' ON '||
    OWNER||'.'||
        TABLE_NAME||
      ' FROM '||
        GRANTEE||
       ';'
FROM DBA_TAB_PRIVS
WHERE(TABLE_NAME='&&TBL' OR GRANTEE='&&TBL')
ORDER BY TABLE_NAME,GRANTEE
/
SPOOL OFF
SET HEAD ON
SET VERIFY ON
UNDEFINE OWNER
UNDEFINE TABLE
UNDEFINE TBL