Oracle Permission Generator

Tutorial Part 11 - Generating Permission Scripts

Now that all the changes to the data model are correctly configured in Oracle Permission Generator, scripts to rollout the necessary privilege and synonym changes can be generated. These scripts can be generated from the Script menu. Again, rollback scripts can be generated if the changes need to be undone.

All 4 complete scripts to update the XYZ Online database appear below...

Privilege Rollout Script
REVOKE SELECT ON APPLICATION_STATS FROM XYZON_POWER_ROLE;
GRANT SELECT ON ORDER_STATUSES TO XYZON_POWER_ROLE;
GRANT SELECT ON VW_ORDER_STATUSES TO XYZON_POWER_ROLE;
GRANT EXECUTE ON SP_ORDER_STATUSES_INS TO XYZON_POWER_ROLE;
GRANT EXECUTE ON SP_ORDER_STATUSES_UPD TO XYZON_POWER_ROLE;
REVOKE SELECT ON APPLICATION_STATS FROM XYZON_READ_ROLE;
GRANT SELECT ON ORDER_STATUSES TO XYZON_READ_ROLE;
GRANT SELECT ON VW_ORDER_STATUSES TO 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;
GRANT SELECT ON VW_ORDER_STATUSES TO XZYON_APP_ROLE;
GRANT EXECUTE ON SP_ORDER_STATUSES_INS TO XZYON_APP_ROLE;
GRANT EXECUTE ON SP_ORDER_STATUSES_UPD TO XZYON_APP_ROLE;

Synonym Rollout Script
DROP SYNONYM XYZON_POWER_USER.APPLICATION_STATS;
CREATE SYNONYM XYZON_POWER_USER.ORDER_STATUSES FOR XYZON.ORDER_STATUSES;
CREATE SYNONYM XYZON_POWER_USER.VW_ORDER_STATUSES FOR XYZON.VW_ORDER_STATUSES;
CREATE SYNONYM XYZON_POWER_USER.SP_ORDER_STATUSES_INS FOR XYZON.SP_ORDER_STATUSES_INS;
CREATE SYNONYM XYZON_POWER_USER.SP_ORDER_STATUSES_UPD FOR XYZON.SP_ORDER_STATUSES_UPD;
DROP SYNONYM JONES_SAM.APPLICATION_STATS;
DROP SYNONYM SMITH_JOHN.APPLICATION_STATS;
CREATE SYNONYM JONES_SAM.ORDER_STATUSES FOR XYZON.ORDER_STATUSES;
CREATE SYNONYM SMITH_JOHN.ORDER_STATUSES FOR XYZON.ORDER_STATUSES;
CREATE SYNONYM JONES_SAM.VW_ORDER_STATUSES FOR XYZON.VW_ORDER_STATUSES;
CREATE SYNONYM SMITH_JOHN.VW_ORDER_STATUSES FOR XYZON.VW_ORDER_STATUSES;
DROP SYNONYM XZYON_APP_USER.APPLICATION_STATS;
CREATE SYNONYM XZYON_APP_USER.VW_ORDER_STATUSES FOR XYZON.VW_ORDER_STATUSES;
CREATE SYNONYM XZYON_APP_USER.SP_ORDER_STATUSES_INS FOR XYZON.SP_ORDER_STATUSES_INS;
CREATE SYNONYM XZYON_APP_USER.SP_ORDER_STATUSES_UPD FOR XYZON.SP_ORDER_STATUSES_UPD;

Privilege Rollback Script
GRANT SELECT ON APPLICATION_STATS TO XYZON_POWER_ROLE;
REVOKE SELECT ON ORDER_STATUSES FROM XYZON_POWER_ROLE;
REVOKE SELECT ON VW_ORDER_STATUSES FROM XYZON_POWER_ROLE;
REVOKE EXECUTE ON SP_ORDER_STATUSES_INS FROM XYZON_POWER_ROLE;
REVOKE EXECUTE ON SP_ORDER_STATUSES_UPD FROM XYZON_POWER_ROLE;
GRANT SELECT ON APPLICATION_STATS TO XYZON_READ_ROLE;
REVOKE SELECT ON ORDER_STATUSES FROM XYZON_READ_ROLE;
REVOKE SELECT ON VW_ORDER_STATUSES FROM 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;
REVOKE SELECT ON VW_ORDER_STATUSES FROM XZYON_APP_ROLE;
REVOKE EXECUTE ON SP_ORDER_STATUSES_INS FROM XZYON_APP_ROLE;
REVOKE EXECUTE ON SP_ORDER_STATUSES_UPD FROM XZYON_APP_ROLE;

Synonym Rollback Script
CREATE SYNONYM XYZON_POWER_USER.APPLICATION_STATS FOR XYZON.APPLICATION_STATS;
DROP SYNONYM XYZON_POWER_USER.ORDER_STATUSES;
DROP SYNONYM XYZON_POWER_USER.VW_ORDER_STATUSES;
DROP SYNONYM XYZON_POWER_USER.SP_ORDER_STATUSES_INS;
DROP SYNONYM XYZON_POWER_USER.SP_ORDER_STATUSES_UPD;
CREATE SYNONYM JONES_SAM.APPLICATION_STATS FOR XYZON.APPLICATION_STATS;
CREATE SYNONYM SMITH_JOHN.APPLICATION_STATS FOR XYZON.APPLICATION_STATS;
DROP SYNONYM JONES_SAM.ORDER_STATUSES;
DROP SYNONYM SMITH_JOHN.ORDER_STATUSES;
DROP SYNONYM JONES_SAM.VW_ORDER_STATUSES;
DROP SYNONYM SMITH_JOHN.VW_ORDER_STATUSES;
CREATE SYNONYM XZYON_APP_USER.APPLICATION_STATS FOR XYZON.APPLICATION_STATS;
DROP SYNONYM XZYON_APP_USER.VW_ORDER_STATUSES;
DROP SYNONYM XZYON_APP_USER.SP_ORDER_STATUSES_INS;
DROP SYNONYM XZYON_APP_USER.SP_ORDER_STATUSES_UPD;

Continue to Part 12...