Users, Roles and Groups in SQL - CA33

My Thinking and Approach

---

Introduction

In this task, I worked with roles and permissions in SQL using the dvdrental database. The goal was to control access to different tables by creating roles, assigning privileges, and managing user groups.

This helped me understand how database security works in real-world applications.

---

Problem Statement

• Create roles with limited access
• Grant and revoke permissions
• Restrict access to specific columns
• Manage users using groups

---

My Initial Thought

At first, I thought:

• Roles are just users
• Permissions are simple

But I realized:

• Roles control access at different levels
• Permissions can be very specific
• Security is very important in databases

---

Key Observation

• GRANT is used to give permissions
• REVOKE is used to remove permissions
• Roles can be grouped for easier management
• Column-level access is possible

---

Solutions

Task 1: Create report_user with access to film table

CREATE ROLE report_user WITH LOGIN PASSWORD 'password';

GRANT SELECT ON film TO report_user;

Explanation:

• Creates a login role
• Allows only read access to film table

---

Task 2: Fix access to customer table

GRANT SELECT ON customer TO report_user;

Explanation:

• Grants permission to read customer table

---

Task 3: Restrict columns in customer table

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 access only to specific columns

---

Task 4: Create support_user with limited permissions

CREATE ROLE support_user WITH LOGIN PASSWORD 'password';

GRANT SELECT ON customer TO support_user;

GRANT UPDATE (email) ON customer TO support_user;

Explanation:

• Can view customer data
• Can update only email column
• No DELETE permission given

---

Task 5: Remove film access from report_user

REVOKE SELECT ON film FROM report_user;

Explanation:

• Removes access to film table

---

Task 6: Create readonly_group

CREATE ROLE readonly_group;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;

Explanation:

• Group with read-only access
• Applies to all tables

---

Task 7: Create users and assign to group

CREATE ROLE analyst1 WITH LOGIN PASSWORD 'password';
CREATE ROLE analyst2 WITH LOGIN PASSWORD 'password';

GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;

Explanation:

• Creates two users
• Adds them to readonly group

---

Final Understanding

• Roles help in managing permissions efficiently
• Access can be controlled at table and column level
• Groups simplify permission management for multiple users

---

Conclusion

This task helped me understand how to manage users, roles, and permissions in a database. It showed how important access control is for maintaining data security and integrity.

---