CA 33 - Users, Roles, Groups

Task 1: Create a read-only user for film table

Here I created a user called report_user. This user should only read data from the film table.

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

Task 2: Fix permission error for customer table

When I tried to access the customer table using report_user, it showed permission denied.

So I fixed it by giving SELECT permission.

GRANT SELECT ON customer TO report_user;

Task 3: Restrict columns in customer table

Now I don’t want full access. I only want customer_id, first_name, and last_name.

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

Task 4: Create support_user with limited update

Here I created another user called support_user.

This user:

Can read customer table
Can update only email column
Cannot delete anything

CREATE ROLE support_user LOGIN PASSWORD 'password123';
GRANT CONNECT ON DATABASE dvdrental TO support_user;
GRANT USAGE ON SCHEMA public TO support_user;
GRANT SELECT ON customer TO support_user;
GRANT UPDATE (email) ON customer TO support_user;

Task 5: Remove film access from report_user

Now I removed access from film table.

REVOKE SELECT ON film FROM report_user;

Task 6: Create readonly_group

This group will have SELECT access on all tables.

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

Task 7: Add users to readonly_group

Now I created two users and added them to the group.

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