users,roles,groups queries

Task 2 Fix Permission Denied on Customer Table

When report_user tries to access the customer table, it will show a permission denied error. To fix this, give access.

GRANT SELECT ON customer TO report_user;

Now the user can read both film and customer tables.

Task 3 Restrict Access to Specific Columns

Sometimes you do not want users to see all columns. Here, allow report_user to see only selected columns.

REVOKE SELECT ON customer FROM report_user;

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

Now the user can only view limited information from the customer table.

Task 4 Create Support User with Limited Update Access

Create another user who can read customer data and update only the email column.

CREATE ROLE support_user LOGIN PASSWORD 'password123';

GRANT SELECT ON customer TO support_user;

GRANT UPDATE (email) ON customer TO support_user;

REVOKE DELETE ON customer FROM support_user;

This ensures the user cannot delete records and can only update email.

Task 5 Remove Access from Film Table

If you want to remove access from a table, use REVOKE.

REVOKE SELECT ON film FROM report_user;

Now report_user cannot read from the film table.

Task 6 Create a Read Only Group

Instead of assigning permissions to each user, create a group role.

CREATE ROLE readonly_group;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;

This group can read all tables in the public schema.

Task 7 Add Users to the Group

Create users and assign 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;