2.2.5. Roles and Privileges for Users

Now we need to create roles and grant the corresponding privileges. We will create two roles: MANAGER and SUPERUSER. MANAGER will have a limited set of privileges while SUPERUSER will have access to practically everything in the database that is used by the project application.

  1. CREATE ROLE MANAGER;
  2. CREATE ROLE SUPERUSER;
  3. -- The MANAGER role can read any table and use the corresponding procedures
  4. -- to manage invoices:
  5. GRANT SELECT ON CUSTOMER TO MANAGER;
  6. GRANT SELECT ON INVOICE TO MANAGER;
  7. GRANT SELECT ON INVOICE_LINE TO MANAGER;
  8. GRANT SELECT ON PRODUCT TO MANAGER;
  9. GRANT EXECUTE ON PROCEDURE SP_ADD_INVOICE TO MANAGER;
  10. GRANT EXECUTE ON PROCEDURE SP_ADD_INVOICE_LINE TO MANAGER;
  11. GRANT EXECUTE ON PROCEDURE SP_DELETE_INVOICE TO MANAGER;
  12. GRANT EXECUTE ON PROCEDURE SP_DELETE_INVOICE_LINE TO MANAGER;
  13. GRANT EXECUTE ON PROCEDURE SP_EDIT_INVOICE TO MANAGER;
  14. GRANT EXECUTE ON PROCEDURE SP_EDIT_INVOICE_LINE TO MANAGER;
  15. GRANT EXECUTE ON PROCEDURE SP_PAY_FOR_INVOICE TO MANAGER;
  16. GRANT USAGE ON SEQUENCE GEN_INVOICE_ID TO MANAGER;
  17. -- The SUPERUSER role can read any table, edit the primary tables directly
  18. -- and use the procedures to manage invoices:
  19. GRANT SELECT, INSERT, UPDATE, DELETE ON CUSTOMER TO SUPERUSER;
  20. GRANT SELECT ON INVOICE TO SUPERUSER;
  21. GRANT SELECT ON INVOICE_LINE TO SUPERUSER;
  22. GRANT SELECT, INSERT, UPDATE, DELETE ON PRODUCT TO SUPERUSER;
  23. GRANT EXECUTE ON PROCEDURE SP_ADD_INVOICE TO SUPERUSER;
  24. GRANT EXECUTE ON PROCEDURE SP_ADD_INVOICE_LINE TO SUPERUSER;
  25. GRANT EXECUTE ON PROCEDURE SP_DELETE_INVOICE TO SUPERUSER;
  26. GRANT EXECUTE ON PROCEDURE SP_DELETE_INVOICE_LINE TO SUPERUSER;
  27. GRANT EXECUTE ON PROCEDURE SP_EDIT_INVOICE TO SUPERUSER;
  28. GRANT EXECUTE ON PROCEDURE SP_EDIT_INVOICE_LINE TO SUPERUSER;
  29. GRANT EXECUTE ON PROCEDURE SP_PAY_FOR_INVICE TO SUPERUSER;
  30. GRANT USAGE ON SEQUENCE GEN_CUSTOMER_ID TO SUPERUSER;
  31. GRANT USAGE ON SEQUENCE GEN_INVOICE_ID TO SUPERUSER;
  32. GRANT USAGE ON SEQUENCE GEN_PRODUCT_ID TO SUPERUSER;

These statements create some users and assign roles to them:

  1. CREATE USER IVAN PASSWORD 'z12a';
  2. CREATE USER ANNA PASSWORD 'lh67';
  4. GRANT MANAGER TO ANNA;
  5. GRANT MANAGER TO IVAN WITH ADMIN OPTION;
  6. GRANT SUPERUSER TO IVAN;

The user IVAN can assign the MANAGER role to other users.