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.