Andrew Fraser DBA

Oracle grants generate commands

Script to generate commands for object grants:

SQL:

SELECT 'GRANT ' || privilege || ' ON ' || LOWER ( owner || '.' || table_name ) || ' TO ' || LOWER ( grantee ) || CASE WHEN grantable = 'YES' THEN ' WITH GRANT OPTION' END || ' ;' AS cmd
  FROM dba_tab_privs
 WHERE owner = 'MYSCHEMA'
 ORDER BY table_name , privilege , grantee
;

PL/SQL:

BEGIN
   FOR r1 IN (
      SELECT privilege , owner , table_name , grantee , grantable
        FROM dba_tab_privs
       WHERE owner = 'MYSCHEMA'
       ORDER BY table_name , privilege , grantee
      )
   LOOP
      DBMS_OUTPUT.PUT_LINE ( 'GRANT ' || r1.privilege || ' ON ' || LOWER ( r1.owner || '.' || r1.table_name ) || ' TO ' || LOWER ( r1.grantee ) || CASE WHEN r1.grantable = 'YES' THEN ' WITH GRANT OPTION' END ) ;
      EXECUTE IMMEDIATE 'GRANT ' || r1.privilege || ' ON ' || LOWER ( r1.owner || '.' || r1.table_name ) || ' TO ' || LOWER ( r1.grantee ) || CASE WHEN r1.grantable = 'YES' THEN ' WITH GRANT OPTION' END ;
   END LOOP ;
END ;
/