Users, Roles, Groups

Task 1: Create a login role report_user that can only read from the film table
CREATE ROLE report_user WITH LOGIN PASSWORD 'report123';

GRANT SELECT ON film TO report_user;

This creates a login user and gives only SELECT permission on film table.

Task 2: report_user cannot access customer table – Fix it
GRANT SELECT ON customer TO report_user;

This gives read access to customer table.

Task 3: Allow report_user to see only customer_id, first_name, last_name

First remove full access:

REVOKE SELECT ON customer FROM report_user;

Then give column level access:

GRANT SELECT (customer_id, first_name, last_name)
ON customer TO report_user;
Task 4: Create support_user who can SELECT from customer, UPDATE only email column, Cannot DELETE
CREATE ROLE support_user WITH LOGIN PASSWORD 'support123';

GRANT SELECT ON customer TO support_user;

GRANT UPDATE (email) ON customer TO support_user;

We did not give DELETE permission, so they cannot delete.

Task 5: Remove SELECT access on film from report_user
REVOKE SELECT ON film FROM report_user;
Task 6: Create readonly_group that has SELECT on all tables
CREATE ROLE readonly_group;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;
Task 7: Create analyst1 and analyst2 and add them to readonly_group
CREATE ROLE analyst1 WITH LOGIN PASSWORD 'analyst123';
CREATE ROLE analyst2 WITH LOGIN PASSWORD 'analyst123';

GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;