6.6 Users, Roles & Permissions

Level: 6 | Version: v1.1 | Author: Meptrasoft

Overview

Database access is controlled using users, roles, and permissions.

User → Account that can connect to the database.
Role → Defines a set of permissions.
Permission → Specifies what a user or role can do.

USER
  ↓
ROLE
  ↓
PERMISSIONS
  ↓
DATABASE OBJECTS

Users identify who connects; roles and permissions control what they can do.

User

A database user is an account that can authenticate and connect to a database.

For example, in PostgreSQL:

CREATE USER naaga WITH PASSWORD 'StrongPass123!';

A user can then be given specific permissions directly or assigned to roles.

Roles

A role is a database identity that can own objects and/or receive permissions. In PostgreSQL, a role can also have the ability to log in.

Roles are useful when multiple users need the same access.

For example:

HR Role
   ↓
SELECT + INSERT
   ↓
HR Users

This is easier to manage than assigning the same permissions separately to every individual user.

Permissions

Permissions determine what a user or role is allowed to do.

Common permissions include:

SELECT  → Read
INSERT  → Add
UPDATE  → Change
DELETE  → Remove

Example:

GRANT SELECT, INSERT
ON employees
TO hr_role;

This allows the hr_role to perform the granted operations on the employees table, subject to the database's permission model.

PostgreSQL Example

A simple access setup can look like:

CREATE ROLE hr_role;

GRANT SELECT, INSERT
ON employees
TO hr_role;

GRANT hr_role TO naaga;

Conceptually:

naaga
  ↓
hr_role
  ↓
SELECT + INSERT
  ↓
employees

Common Role Types

Access	Meaning
Superuser	Broad administrative control
Read-Write	Can read and modify permitted data
Read-Only	Can only read permitted data
Application Role	Access used by an application

Note: Exact roles and privileges vary between database systems.

Principle of Least Privilege

Users should receive only the permissions they need.

For example:

Student
→ SELECT

Teacher
→ SELECT + UPDATE

Admin
→ Broader administrative permissions

This reduces unnecessary access to sensitive data and operations.

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
User and role are always completely different objects	In PostgreSQL, both are roles; `LOGIN` distinguishes roles that can connect
Giving everyone full access is easier	Users should receive only required permissions
`SELECT` allows modification	`SELECT` provides read access
A role is the same as a database	A role controls identity/access; a database stores data
Permissions automatically apply everywhere	Permissions must be granted on the relevant objects or inherited through roles

Placement Quick Points

USER
→ ACCOUNT / IDENTITY

ROLE
→ GROUP OF ACCESS RIGHTS

PERMISSION
→ ALLOWED ACTION

Users/roles identify database identities.
Permissions control access to database objects.
Common permissions include SELECT, INSERT, UPDATE, and DELETE.
Roles make permission management easier for groups of users.
Least privilege means giving only the access required.

Interview Questions

What is a database user?

A user is an account/role that can authenticate and connect to the database.

What is a role?

A role is a database identity that can own objects and/or receive permissions. Roles can also be granted to other roles or users.

What is a permission?

A permission defines what operations a user or role can perform on a database object.

Why are roles useful?

Roles allow common permissions to be grouped and assigned to multiple users more easily.

What is the principle of least privilege?

Give users only the permissions they actually need to perform their required tasks.

What is the difference between SELECT and UPDATE?

SELECT → Read data
UPDATE → Change data

Practice & Hands-On Exercises

Explain the difference between a user, role, and permission.
Identify suitable permissions for a student, teacher, and administrator.
Explain why roles are useful when many users need the same access.
Explain the principle of least privilege.
Interpret this command:

GRANT SELECT
ON employees
TO hr_role;

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

USER
  ↓
ROLE
  ↓
PERMISSIONS
  ↓
DATABASE OBJECT

Users provide identity, roles group access, and permissions control what actions can be performed on database objects.

End of Users, Roles & Permissions