Oracle Permission Generator

Tutorial Part 7 - Generating Permission Scripts

Now that all objects and permissions for the XYZ Online database are correctly configured in Oracle Permission Generator, scripts to create ('rollout') the privileges and synonyms can be generated. Scripts can be generated from the Script menu. Oracle Permission Generator generates separate scripts for privileges (GRANT and REVOKE statements), and synonyms. Additionally, complementary 'rollback' scripts can be generated, which can be used to completely reverse any changes made by executing the rollout scripts. The rollback scripts can be used in the case that an upgrade to a database is unsuccessful, and the changes need to be undone.

In rollout scripts, any objects set as 'Add' in the main window will have their permissions created in the script. Any objects set as 'Remove' will have their permissions removed. Conversely in rollback scripts, any objects set as 'Add' will have their permissions removed, and any objects set as 'Remove' will have their permissions created.

All 4 complete scripts for the XYZ Online database appear below...

Privilege Rollout Script
GRANT SELECT ON CUSTOMERS TO XYZON_POWER_ROLE;
GRANT SELECT ON ITEMS TO XYZON_POWER_ROLE;
GRANT SELECT ON ORDERS TO XYZON_POWER_ROLE;
GRANT SELECT ON APPLICATION_STATS TO XYZON_POWER_ROLE;
GRANT SELECT ON VW_CUSTOMERS TO XYZON_POWER_ROLE;
GRANT SELECT ON VW_ITEMS TO XYZON_POWER_ROLE;
GRANT SELECT ON VW_ORDERS TO XYZON_POWER_ROLE;
GRANT EXECUTE ON SP_CUSTOMERS_INS TO XYZON_POWER_ROLE;
GRANT EXECUTE ON SP_CUSTOMERS_UPD TO XYZON_POWER_ROLE;
GRANT EXECUTE ON SP_ITEMS_INS TO XYZON_POWER_ROLE;
GRANT EXECUTE ON SP_ITEMS_UPD TO XYZON_POWER_ROLE;
GRANT EXECUTE ON SP_ORDERS_INS TO XYZON_POWER_ROLE;
GRANT EXECUTE ON SP_ORDERS_UPD TO XYZON_POWER_ROLE;
GRANT SELECT ON CUSTOMERS TO XYZON_READ_ROLE;
GRANT SELECT ON ITEMS TO XYZON_READ_ROLE;
GRANT SELECT ON ORDERS TO XYZON_READ_ROLE;
GRANT SELECT ON APPLICATION_STATS TO XYZON_READ_ROLE;
GRANT SELECT ON VW_CUSTOMERS TO XYZON_READ_ROLE;
GRANT SELECT ON VW_ITEMS TO XYZON_READ_ROLE;
GRANT SELECT ON VW_ORDERS TO XYZON_READ_ROLE;
GRANT SELECT ON APPLICATION_STATS TO XZYON_APP_ROLE;
GRANT INSERT ON APPLICATION_STATS TO XZYON_APP_ROLE;
GRANT UPDATE ON APPLICATION_STATS TO XZYON_APP_ROLE;
GRANT DELETE ON APPLICATION_STATS TO XZYON_APP_ROLE;
GRANT SELECT ON VW_CUSTOMERS TO XZYON_APP_ROLE;
GRANT SELECT ON VW_ITEMS TO XZYON_APP_ROLE;
GRANT SELECT ON VW_ORDERS TO XZYON_APP_ROLE;
GRANT EXECUTE ON SP_CUSTOMERS_INS TO XZYON_APP_ROLE;
GRANT EXECUTE ON SP_CUSTOMERS_UPD TO XZYON_APP_ROLE;
GRANT EXECUTE ON SP_ITEMS_INS TO XZYON_APP_ROLE;
GRANT EXECUTE ON SP_ITEMS_UPD TO XZYON_APP_ROLE;
GRANT EXECUTE ON SP_ORDERS_INS TO XZYON_APP_ROLE;
GRANT EXECUTE ON SP_ORDERS_UPD TO XZYON_APP_ROLE;

Synonym Rollout Script
CREATE SYNONYM XYZON_POWER_USER.CUSTOMERS FOR XYZON.CUSTOMERS;
CREATE SYNONYM XYZON_POWER_USER.ITEMS FOR XYZON.ITEMS;
CREATE SYNONYM XYZON_POWER_USER.ORDERS FOR XYZON.ORDERS;
CREATE SYNONYM XYZON_POWER_USER.APPLICATION_STATS FOR XYZON.APPLICATION_STATS;
CREATE SYNONYM XYZON_POWER_USER.VW_CUSTOMERS FOR XYZON.VW_CUSTOMERS;
CREATE SYNONYM XYZON_POWER_USER.VW_ITEMS FOR XYZON.VW_ITEMS;
CREATE SYNONYM XYZON_POWER_USER.VW_ORDERS FOR XYZON.VW_ORDERS;
CREATE SYNONYM XYZON_POWER_USER.SP_CUSTOMERS_INS FOR XYZON.SP_CUSTOMERS_INS;
CREATE SYNONYM XYZON_POWER_USER.SP_CUSTOMERS_UPD FOR XYZON.SP_CUSTOMERS_UPD;
CREATE SYNONYM XYZON_POWER_USER.SP_ITEMS_INS FOR XYZON.SP_ITEMS_INS;
CREATE SYNONYM XYZON_POWER_USER.SP_ITEMS_UPD FOR XYZON.SP_ITEMS_UPD;
CREATE SYNONYM XYZON_POWER_USER.SP_ORDERS_INS FOR XYZON.SP_ORDERS_INS;
CREATE SYNONYM XYZON_POWER_USER.SP_ORDERS_UPD FOR XYZON.SP_ORDERS_UPD;
CREATE SYNONYM JONES_SAM.CUSTOMERS FOR XYZON.CUSTOMERS;
CREATE SYNONYM SMITH_JOHN.CUSTOMERS FOR XYZON.CUSTOMERS;
CREATE SYNONYM JONES_SAM.ITEMS FOR XYZON.ITEMS;
CREATE SYNONYM SMITH_JOHN.ITEMS FOR XYZON.ITEMS;
CREATE SYNONYM JONES_SAM.ORDERS FOR XYZON.ORDERS;
CREATE SYNONYM SMITH_JOHN.ORDERS FOR XYZON.ORDERS;
CREATE SYNONYM JONES_SAM.APPLICATION_STATS FOR XYZON.APPLICATION_STATS;
CREATE SYNONYM SMITH_JOHN.APPLICATION_STATS FOR XYZON.APPLICATION_STATS;
CREATE SYNONYM JONES_SAM.VW_CUSTOMERS FOR XYZON.VW_CUSTOMERS;
CREATE SYNONYM SMITH_JOHN.VW_CUSTOMERS FOR XYZON.VW_CUSTOMERS;
CREATE SYNONYM JONES_SAM.VW_ITEMS FOR XYZON.VW_ITEMS;
CREATE SYNONYM SMITH_JOHN.VW_ITEMS FOR XYZON.VW_ITEMS;
CREATE SYNONYM JONES_SAM.VW_ORDERS FOR XYZON.VW_ORDERS;
CREATE SYNONYM SMITH_JOHN.VW_ORDERS FOR XYZON.VW_ORDERS;
CREATE SYNONYM XZYON_APP_USER.APPLICATION_STATS FOR XYZON.APPLICATION_STATS;
CREATE SYNONYM XZYON_APP_USER.VW_CUSTOMERS FOR XYZON.VW_CUSTOMERS;
CREATE SYNONYM XZYON_APP_USER.VW_ITEMS FOR XYZON.VW_ITEMS;
CREATE SYNONYM XZYON_APP_USER.VW_ORDERS FOR XYZON.VW_ORDERS;
CREATE SYNONYM XZYON_APP_USER.SP_CUSTOMERS_INS FOR XYZON.SP_CUSTOMERS_INS;
CREATE SYNONYM XZYON_APP_USER.SP_CUSTOMERS_UPD FOR XYZON.SP_CUSTOMERS_UPD;
CREATE SYNONYM XZYON_APP_USER.SP_ITEMS_INS FOR XYZON.SP_ITEMS_INS;
CREATE SYNONYM XZYON_APP_USER.SP_ITEMS_UPD FOR XYZON.SP_ITEMS_UPD;
CREATE SYNONYM XZYON_APP_USER.SP_ORDERS_INS FOR XYZON.SP_ORDERS_INS;
CREATE SYNONYM XZYON_APP_USER.SP_ORDERS_UPD FOR XYZON.SP_ORDERS_UPD;

Privilege Rollback Script
REVOKE SELECT ON CUSTOMERS FROM XYZON_POWER_ROLE;
REVOKE SELECT ON ITEMS FROM XYZON_POWER_ROLE;
REVOKE SELECT ON ORDERS FROM XYZON_POWER_ROLE;
REVOKE SELECT ON APPLICATION_STATS FROM XYZON_POWER_ROLE;
REVOKE SELECT ON VW_CUSTOMERS FROM XYZON_POWER_ROLE;
REVOKE SELECT ON VW_ITEMS FROM XYZON_POWER_ROLE;
REVOKE SELECT ON VW_ORDERS FROM XYZON_POWER_ROLE;
REVOKE EXECUTE ON SP_CUSTOMERS_INS FROM XYZON_POWER_ROLE;
REVOKE EXECUTE ON SP_CUSTOMERS_UPD FROM XYZON_POWER_ROLE;
REVOKE EXECUTE ON SP_ITEMS_INS FROM XYZON_POWER_ROLE;
REVOKE EXECUTE ON SP_ITEMS_UPD FROM XYZON_POWER_ROLE;
REVOKE EXECUTE ON SP_ORDERS_INS FROM XYZON_POWER_ROLE;
REVOKE EXECUTE ON SP_ORDERS_UPD FROM XYZON_POWER_ROLE;
REVOKE SELECT ON CUSTOMERS FROM XYZON_READ_ROLE;
REVOKE SELECT ON ITEMS FROM XYZON_READ_ROLE;
REVOKE SELECT ON ORDERS FROM XYZON_READ_ROLE;
REVOKE SELECT ON APPLICATION_STATS FROM XYZON_READ_ROLE;
REVOKE SELECT ON VW_CUSTOMERS FROM XYZON_READ_ROLE;
REVOKE SELECT ON VW_ITEMS FROM XYZON_READ_ROLE;
REVOKE SELECT ON VW_ORDERS FROM XYZON_READ_ROLE;
REVOKE SELECT ON APPLICATION_STATS FROM XZYON_APP_ROLE;
REVOKE INSERT ON APPLICATION_STATS FROM XZYON_APP_ROLE;
REVOKE UPDATE ON APPLICATION_STATS FROM XZYON_APP_ROLE;
REVOKE DELETE ON APPLICATION_STATS FROM XZYON_APP_ROLE;
REVOKE SELECT ON VW_CUSTOMERS FROM XZYON_APP_ROLE;
REVOKE SELECT ON VW_ITEMS FROM XZYON_APP_ROLE;
REVOKE SELECT ON VW_ORDERS FROM XZYON_APP_ROLE;
REVOKE EXECUTE ON SP_CUSTOMERS_INS FROM XZYON_APP_ROLE;
REVOKE EXECUTE ON SP_CUSTOMERS_UPD FROM XZYON_APP_ROLE;
REVOKE EXECUTE ON SP_ITEMS_INS FROM XZYON_APP_ROLE;
REVOKE EXECUTE ON SP_ITEMS_UPD FROM XZYON_APP_ROLE;
REVOKE EXECUTE ON SP_ORDERS_INS FROM XZYON_APP_ROLE;
REVOKE EXECUTE ON SP_ORDERS_UPD FROM XZYON_APP_ROLE;

Synonym Rollback Script
DROP SYNONYM XYZON_POWER_USER.CUSTOMERS;
DROP SYNONYM XYZON_POWER_USER.ITEMS;
DROP SYNONYM XYZON_POWER_USER.ORDERS;
DROP SYNONYM XYZON_POWER_USER.APPLICATION_STATS;
DROP SYNONYM XYZON_POWER_USER.VW_CUSTOMERS;
DROP SYNONYM XYZON_POWER_USER.VW_ITEMS;
DROP SYNONYM XYZON_POWER_USER.VW_ORDERS;
DROP SYNONYM XYZON_POWER_USER.SP_CUSTOMERS_INS;
DROP SYNONYM XYZON_POWER_USER.SP_CUSTOMERS_UPD;
DROP SYNONYM XYZON_POWER_USER.SP_ITEMS_INS;
DROP SYNONYM XYZON_POWER_USER.SP_ITEMS_UPD;
DROP SYNONYM XYZON_POWER_USER.SP_ORDERS_INS;
DROP SYNONYM XYZON_POWER_USER.SP_ORDERS_UPD;
DROP SYNONYM JONES_SAM.CUSTOMERS;
DROP SYNONYM SMITH_JOHN.CUSTOMERS;
DROP SYNONYM JONES_SAM.ITEMS;
DROP SYNONYM SMITH_JOHN.ITEMS;
DROP SYNONYM JONES_SAM.ORDERS;
DROP SYNONYM SMITH_JOHN.ORDERS;
DROP SYNONYM JONES_SAM.APPLICATION_STATS;
DROP SYNONYM SMITH_JOHN.APPLICATION_STATS;
DROP SYNONYM JONES_SAM.VW_CUSTOMERS;
DROP SYNONYM SMITH_JOHN.VW_CUSTOMERS;
DROP SYNONYM JONES_SAM.VW_ITEMS;
DROP SYNONYM SMITH_JOHN.VW_ITEMS;
DROP SYNONYM JONES_SAM.VW_ORDERS;
DROP SYNONYM SMITH_JOHN.VW_ORDERS;
DROP SYNONYM XZYON_APP_USER.APPLICATION_STATS;
DROP SYNONYM XZYON_APP_USER.VW_CUSTOMERS;
DROP SYNONYM XZYON_APP_USER.VW_ITEMS;
DROP SYNONYM XZYON_APP_USER.VW_ORDERS;
DROP SYNONYM XZYON_APP_USER.SP_CUSTOMERS_INS;
DROP SYNONYM XZYON_APP_USER.SP_CUSTOMERS_UPD;
DROP SYNONYM XZYON_APP_USER.SP_ITEMS_INS;
DROP SYNONYM XZYON_APP_USER.SP_ITEMS_UPD;
DROP SYNONYM XZYON_APP_USER.SP_ORDERS_INS;
DROP SYNONYM XZYON_APP_USER.SP_ORDERS_UPD;

Continue to Part 8...