ASSIGNMENT 33

*Restore the DVD Rental Database and perform role-based access control operations by creating users with specific permissions. Create a report_user role with limited access, modify permissions to allow or restrict access to certain tables and columns, and handle permission errors. Then create a support_user with controlled update rights, revoke specific privileges, and implement a readonly_group role with read access to all tables. Finally, create multiple users and assign them to this group to manage permissions efficiently.
*

Introduction

In database systems, role-based access control (RBAC) is used to manage who can access or modify data. Instead of giving permissions directly to every user, roles are created and privileges are assigned efficiently.

In this exercise, we:

Create users with restricted access
Grant and revoke permissions
Control access at both table-level and column-level
Use groups to simplify permission management

This ensures security, data privacy, and controlled operations.

Task 1: Create report_user with read access only to film

sql CREATE ROLE report_user LOGIN PASSWORD 'password123';

GRANT SELECT ON film TO report_user;

Explanation

• Creates a login role
• Grants read-only (SELECT) access to film table

Task 2: Fix permission denied for customer

sql GRANT SELECT ON customer TO report_user;

Explanation

Initially, access is denied
Granting SELECT allows reading the table

Task 3: Restrict access to specific columns
sql REVOKE SELECT ON customer FROM report_user;

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

Explanation

• Removes full access
• Grants column-level access only
• Improves data privacy

Task 4: Create support_user with limited permissions

sql
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;

Explanation

Can read all customer data
Can update only email
Cannot delete records --> prevents data loss

Task 5: Remove SELECT access on film

sql REVOKE SELECT ON film FROM report_user;

Explanation

Removes previously granted permission
Demonstrates privilege revocation

Task 6: Create readonly_group with SELECT on all tables

sql CREATE ROLE readonly_group;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;

Explanation

• Creates a group role
• Grants read-only access to all tables
• Useful for analysts and reporting users

Task 7: Create users and assign to group

sql
CREATE ROLE analyst1 LOGIN PASSWORD 'password123';
CREATE ROLE analyst2 LOGIN PASSWORD 'password123';

GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;

Explanation

Creates multiple users
Assigns them to the group
Inherits permissions automatically

Conclusion

This exercise demonstrates how PostgreSQL enforces secure and structured access control:

Roles simplify permission management
GRANT and REVOKE control access precisely
Column-level security enhances privacy
Group roles improve scalability