CA 33 - Users, Roles, Groups

Task 1: Create report_user with read access only to film:-

CREATE ROLE report_user WITH LOGIN PASSWORD 'password123';
GRANT CONNECT ON DATABASE dvdrental TO report_user;
GRANT USAGE ON SCHEMA public TO report_user;
GRANT SELECT ON TABLE film TO report_user;

Task 2: Fix permission denied for customer table:-

GRANT SELECT ON TABLE customer TO report_user;

Task 3: Allow only specific columns from customer:-

REVOKE SELECT ON TABLE customer FROM report_user;
GRANT SELECT (customer_id, first_name, last_name) ON customer TO report_user;

Task 4: Create support_user with restricted permissions:-

CREATE ROLE support_user WITH LOGIN PASSWORD 'password123';
GRANT CONNECT ON DATABASE dvdrental TO support_user;
GRANT USAGE ON SCHEMA public TO support_user;
Allow SELECT GRANT SELECT ON TABLE customer TO support_user;
Allow UPDATE only on email column GRANT UPDATE (email) ON customer TO support_user;
Ensure DELETE is not allowed REVOKE DELETE ON customer FROM support_user;

Task 5: Remove SELECT access on film from report_user:-

REVOKE SELECT ON TABLE film FROM report_user;

Task 6: Create readonly_group with SELECT on all tables:-

CREATE ROLE readonly_group;
GRANT USAGE ON SCHEMA public TO readonly_group;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;

Task 7: Create users and add to group:-

CREATE ROLE analyst1 WITH LOGIN PASSWORD 'password123';
CREATE ROLE analyst2 WITH LOGIN PASSWORD 'password123';
GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;