Retirement

Cloud computing is the rage these days. As we migrate old systems to the cloud, we need to retire the old system that we left behind. This is usually a several step process: we start with the easiest step to implement and undo, and we finish with the most difficult step to undo.

Assuming that the retired application has its own schema, the easiest first step is to simply revoke all the privileges on the underlying database schema. We leave the data and the programs in place, and we simply deny all users other than the owner access to the database objects. If we're retiring a large system, there will be hundreds of grants to revoke. We would like an easy way to find all the grants and revoke them. Also, we would like an easy way to undo these changes!

I work in an Oracle shop, so we'll use the Oracle system catalog as an example. Using two two views in the Oracle catalog, we can find all the information we need to revoke every grant on the retired schema's objects.  The USER_TAB_PRIVS view shows all the objects where the schema is object owner, grantor, or grantee.  We want to revoke every grant where the schema is the grantor and owner of the object.   From USER_TAB_PRIVS we get the object name and the privilege.  The  USER_OBJECTS view shows us all the objects that belong to the schema.  From USER_OBJECTS we get the object type, and we use the object type to skip PACKAGE BODYs and TYPE BODYs, because the grants belong to the PACKAGE or TYPE, not the PACKAGE BODY nor TYPE BODY.

Here's our script.  We open a cursor from the join of the two views, we concatenate a string to revoke the privilege, and then we execute the string.   The SPOOL command writes the results in an output file. SQL/Developer does not support the spool command, so login to the retired schema and run this script using sqlplus.   If you're a bit nervous about running the script under the wrong user or schema, change the USER function to the quoted name of the schema instead.

set serveroutput on
spool 'Revoked_Grants.txt' 
begin

    for c in (

        select 'REVOKE ' || a.privilege ||
                  ' ON ' || a.table_name ||
                  ' FROM ' || a.grantee as revoke_command
        from user_tab_privs a
        inner join user_objects b
          on a.table_name = b.object_name
        where b.object_type not in ('PACKAGE BODY', 'TYPE BODY')
          and a.owner  =  user
          and a.grantor = user) loop
    
        dbms_output.put_line(c.revoke_command);

        begin
           execute immediate c.revoke_command;
        exception
           when others then dbms_output.put_line(' *** ' || sqlerrm);
        end;
   
    end loop;
   
end;
/
spool off

When we run this script, the spool command writes a file listing every grant that we revoked. In addition to logging the results for audit purposes, we can use this file to reverse the changes. Simply edit the file, change the REVOKEs to GRANTs, change the FROMs to TOs, save the file, and process it through  sqlplus. Now we have an easy way to revoke all the privileges and an easy way to restore them if necessary.