SQL Users and Roles

Managing who can access your database and what they can do is one of the most critical responsibilities in database administration. Whether you are building a small application or managing an enterprise system, understanding how to SQL create user accounts, define roles, and organize permissions through role hierarchies is essential for maintaining a secure and well-organized database environment.

This guide walks you through everything you need to know about creating users and roles in SQL, assigning roles to users, and building role hierarchies. You will find practical examples with expected outputs, common mistakes to avoid, and best practices that apply across major database systems like PostgreSQL, MySQL, SQL Server, and Oracle.

What Are Users and Roles in SQL?

Before diving into syntax, it is important to understand the difference between a user and a role:

A user is a database account that represents a person or an application connecting to the database. Users authenticate with credentials (typically a username and password).
A role is a named collection of privileges (permissions). Roles act as templates that you can assign to one or more users, making permission management scalable and consistent.

Think of it this way: a user is who you are, and a role is what you are allowed to do.

Why Use Roles?

Without roles, you would need to grant each permission individually to every user. If you have 50 developers who all need the same access, that means repeating the same GRANT statements 50 times. Roles let you define permissions once and assign them to as many users as needed.

SQL CREATE USER

The CREATE USER statement creates a new database account. The exact syntax varies slightly between database systems, but the core concept remains the same.

Basic Syntax

CREATE USER username IDENTIFIED BY 'password';

This is the most fundamental form. You specify a username and a password that the user will use to authenticate.

Examples by Database System

PostgreSQL:

CREATE USER app_developer WITH PASSWORD 'Str0ng!Pass';

MySQL:

CREATE USER 'app_developer'@'localhost' IDENTIFIED BY 'Str0ng!Pass';

In MySQL, the user is always associated with a host. The 'app_developer'@'localhost' syntax means this user can only connect from the local machine. To allow connections from any host, use '%':

CREATE USER 'app_developer'@'%' IDENTIFIED BY 'Str0ng!Pass';

SQL Server:

-- First, create a login at the server level
CREATE LOGIN app_developer WITH PASSWORD = 'Str0ng!Pass';

-- Then, create a user in a specific database mapped to that login
USE my_database;
CREATE USER app_developer FOR LOGIN app_developer;

SQL Server separates the concept of a login (server-level authentication) from a user (database-level authorization).

Oracle:

CREATE USER app_developer IDENTIFIED BY "Str0ng!Pass"
  DEFAULT TABLESPACE users
  QUOTA 100M ON users;

Oracle allows you to specify storage parameters during user creation.

Common Options When Creating Users

Most database systems support additional options when creating a user:

-- PostgreSQL: Create a user with specific attributes
CREATE USER report_viewer 
  WITH PASSWORD 'R3port!2024'
  VALID UNTIL '2025-12-31'
  CONNECTION LIMIT 5;

This creates a user whose password expires on December 31, 2025, and who can have at most 5 simultaneous connections.

-- MySQL: Create a user with resource limits
CREATE USER 'report_viewer'@'%' 
  IDENTIFIED BY 'R3port!2024'
  WITH MAX_CONNECTIONS_PER_HOUR 100
  PASSWORD EXPIRE INTERVAL 180 DAY;

Verifying User Creation

After creating a user, you can verify it exists:

PostgreSQL:

SELECT usename, valuntil FROM pg_user WHERE usename = 'app_developer';

Output:

   usename      |        valuntil
----------------+------------------------
 app_developer  |

MySQL:

SELECT user, host FROM mysql.user WHERE user = 'app_developer';

Output:

+----------------+-----------+
| user           | host      |
+----------------+-----------+
| app_developer  | localhost |
+----------------+-----------+

Common Mistake: Creating a User Without Granting Any Privileges

A freshly created user typically has no permissions (or very minimal ones). A common mistake is creating the user and assuming they can immediately work with the database.

Wrong assumption:

CREATE USER 'new_dev'@'localhost' IDENTIFIED BY 'DevPass!99';

-- The user tries to query a table
-- ERROR: SELECT command denied to user 'new_dev'@'localhost' for table 'employees'

Correct approach: After creating the user, explicitly grant the necessary privileges or assign a role (covered in the next sections).

CREATE USER 'new_dev'@'localhost' IDENTIFIED BY 'DevPass!99';
GRANT SELECT ON company_db.employees TO 'new_dev'@'localhost';

warning

Never create users with overly broad permissions like GRANT ALL PRIVILEGES ON *.* TO ... in production environments. Always follow the principle of least privilege: give users only the permissions they actually need.

SQL CREATE ROLE

The CREATE ROLE statement defines a named collection of privileges. Roles themselves do not authenticate. They exist purely to group permissions together.

Basic Syntax

CREATE ROLE role_name;

Creating Roles with Privileges

Creating a role is a two-step process: first you create the role, then you grant privileges to it.

Step 1: Create the role

CREATE ROLE read_only;

Step 2: Grant privileges to the role

GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

Now, any user assigned the read_only role will automatically inherit SELECT permission on all tables in the public schema.

Practical Example: Defining Multiple Roles

Let's define three roles that represent common access levels in an application:

-- Role for users who can only read data
CREATE ROLE read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- Role for users who can read and modify data
CREATE ROLE read_write;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;

-- Role for administrators who can also manage database objects
CREATE ROLE db_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO db_admin;
GRANT CREATE ON SCHEMA public TO db_admin;

Roles in Different Database Systems

PostgreSQL:

In PostgreSQL, CREATE USER and CREATE ROLE are nearly identical. The only difference is that CREATE USER implies LOGIN by default, while CREATE ROLE does not.

-- This role CANNOT log in (it's just a permission container)
CREATE ROLE read_only;

-- This role CAN log in (equivalent to CREATE USER)
CREATE ROLE app_user WITH LOGIN PASSWORD 'UserPass!1';

MySQL:

MySQL introduced roles in version 8.0.

CREATE ROLE 'read_only', 'read_write', 'db_admin';

You can create multiple roles in a single statement.

SQL Server:

USE my_database;
CREATE ROLE read_only;

SQL Server also provides several built-in roles like db_datareader, db_datawriter, and db_owner.

Oracle:

CREATE ROLE read_only;
GRANT SELECT ANY TABLE TO read_only;

Assigning Roles to Users

Once you have created users and roles, the next step is to assign roles to users using the GRANT statement.

Basic Syntax

GRANT role_name TO username;

Examples

PostgreSQL:

-- Create the user and the role
CREATE USER alice WITH PASSWORD 'Alice!2024';
CREATE ROLE read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- Assign the role to the user
GRANT read_only TO alice;

Now alice inherits all privileges associated with read_only.

MySQL:

CREATE USER 'bob'@'localhost' IDENTIFIED BY 'Bob!2024';
CREATE ROLE 'read_write';
GRANT SELECT, INSERT, UPDATE, DELETE ON company_db.* TO 'read_write';

-- Assign the role
GRANT 'read_write' TO 'bob'@'localhost';

MySQL-Specific Behavior

In MySQL 8.0+, granting a role to a user does not automatically activate the role. The user must explicitly activate it after connecting:

SET DEFAULT ROLE 'read_write' TO 'bob'@'localhost';

Or activate it for the current session:

SET ROLE 'read_write';

Without this step, bob will still get "permission denied" errors even though the role is granted.

SQL Server:

USE my_database;

-- Add a user to a role
ALTER ROLE read_only ADD MEMBER alice;

SQL Server uses ALTER ROLE ... ADD MEMBER instead of GRANT ... TO.

Oracle:

GRANT read_only TO alice;

Assigning Multiple Roles to a Single User

A user can have multiple roles at the same time. The user's effective permissions become the union of all privileges from all assigned roles.

-- PostgreSQL example
CREATE USER charlie WITH PASSWORD 'Charlie!2024';

GRANT read_only TO charlie;
GRANT report_generator TO charlie;

Now charlie has all permissions from both read_only and report_generator.

Revoking Roles from Users

If you need to remove a role assignment:

-- PostgreSQL / Oracle
REVOKE read_write FROM alice;

-- MySQL
REVOKE 'read_write' FROM 'alice'@'localhost';

-- SQL Server
ALTER ROLE read_write DROP MEMBER alice;

Practical Example: Full Workflow

Let's walk through a complete, realistic scenario:

-- 1. Create roles
CREATE ROLE junior_dev;
CREATE ROLE senior_dev;

-- 2. Grant privileges to roles
GRANT SELECT ON ALL TABLES IN SCHEMA public TO junior_dev;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO senior_dev;

-- 3. Create users
CREATE USER emma WITH PASSWORD 'Emma!SecurePass1';
CREATE USER frank WITH PASSWORD 'Frank!SecurePass2';

-- 4. Assign roles to users
GRANT junior_dev TO emma;
GRANT senior_dev TO frank;

Testing the permissions:

When emma connects and tries to insert data:

-- Connected as emma
INSERT INTO employees (name, department) VALUES ('Test', 'Engineering');
-- ERROR: permission denied for table employees

When frank connects and runs the same query:

-- Connected as frank
INSERT INTO employees (name, department) VALUES ('Test', 'Engineering');
-- INSERT 0 1  (Success)

This is exactly the behavior we expect based on the role definitions.

Role Hierarchies

One of the most powerful features of SQL roles is the ability to build role hierarchies. A role can be granted to another role, creating a parent-child relationship where the child role inherits all privileges from the parent role.

How Role Hierarchies Work

-- Base role with minimal permissions
CREATE ROLE base_access;
GRANT CONNECT ON DATABASE company_db TO base_access;
GRANT USAGE ON SCHEMA public TO base_access;

-- Mid-level role inherits from base_access
CREATE ROLE data_reader;
GRANT base_access TO data_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO data_reader;

-- Higher-level role inherits from data_reader
CREATE ROLE data_writer;
GRANT data_reader TO data_writer;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO data_writer;

-- Top-level role inherits from data_writer
CREATE ROLE data_admin;
GRANT data_writer TO data_admin;
GRANT TRUNCATE ON ALL TABLES IN SCHEMA public TO data_admin;
GRANT CREATE ON SCHEMA public TO data_admin;

This creates the following hierarchy:

data_admin
  └── data_writer
        └── data_reader
              └── base_access

A user assigned data_admin effectively receives all permissions from every role in the chain:

CONNECT and USAGE (from base_access)
SELECT (from data_reader)
INSERT, UPDATE, DELETE (from data_writer)
TRUNCATE, CREATE (from data_admin directly)

Assigning Hierarchical Roles to Users

CREATE USER grace WITH PASSWORD 'Grace!Admin2024';
GRANT data_admin TO grace;

CREATE USER henry WITH PASSWORD 'Henry!Reader2024';
GRANT data_reader TO henry;

grace gets the full set of admin permissions, while henry only gets SELECT and basic access.

Visualizing Role Memberships

PostgreSQL:

SELECT 
  r.rolname AS role,
  m.rolname AS member
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
ORDER BY r.rolname;

Output:

     role      |  member
---------------+-----------
 base_access   | data_reader
 data_reader   | data_writer
 data_writer   | data_admin
 data_admin    | grace
 data_reader   | henry

MySQL:

SELECT * FROM mysql.role_edges;

Output:

+-----------+--------------+---------+-------------+
| FROM_HOST | FROM_USER    | TO_HOST | TO_USER     |
+-----------+--------------+---------+-------------+
| %         | data_reader  | %       | data_writer |
| %         | data_writer  | %       | data_admin  |
+-----------+--------------+---------+-------------+

Common Mistake: Circular Role Dependencies

Never create circular role grants. This happens when Role A is granted to Role B, and Role B is granted back to Role A.

Wrong:

CREATE ROLE role_a;
CREATE ROLE role_b;

GRANT role_a TO role_b;
GRANT role_b TO role_a;
-- ERROR: role "role_a" is a member of role "role_b" (circular dependency)

Most database systems will detect and reject circular dependencies, but it is important to design your hierarchy clearly to avoid confusion.

INHERIT vs. NOINHERIT (PostgreSQL)

In PostgreSQL, you can control whether a user automatically inherits the privileges of their assigned roles.

-- This user automatically gets all privileges from assigned roles
CREATE USER auto_user WITH PASSWORD 'Auto!2024' INHERIT;
GRANT data_reader TO auto_user;
-- auto_user can immediately SELECT from tables

-- This user must explicitly activate roles
CREATE USER manual_user WITH PASSWORD 'Manual!2024' NOINHERIT;
GRANT data_reader TO manual_user;
-- manual_user must run SET ROLE data_reader; before they can use the privileges

With INHERIT (default):

-- Connected as auto_user
SELECT * FROM employees;
-- Returns rows successfully

With NOINHERIT:

-- Connected as manual_user
SELECT * FROM employees;
-- ERROR: permission denied for table employees

SET ROLE data_reader;
SELECT * FROM employees;
-- Returns rows successfully

When to Use NOINHERIT

NOINHERIT is useful for administrative roles where you want to force the user to explicitly activate elevated privileges, similar to how sudo works in Linux. This prevents accidental use of powerful permissions during routine work.

Best Practices for Managing Users and Roles

1. Follow the Principle of Least Privilege

Always grant the minimum permissions required for a user to do their job. Start with fewer privileges and add more only when needed.

-- Good: Specific permissions
GRANT SELECT ON employees TO reporting_role;
GRANT SELECT ON departments TO reporting_role;

-- Bad: Overly broad permissions
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO reporting_role;

2. Use Roles Instead of Direct Grants

Avoid granting privileges directly to users. Always go through roles.

Wrong approach:

-- Granting directly to each user (hard to maintain)
GRANT SELECT ON employees TO alice;
GRANT SELECT ON employees TO bob;
GRANT SELECT ON employees TO charlie;
-- What if you need to revoke? You must remember every user.

Correct approach:

-- Grant to a role, assign role to users
GRANT SELECT ON employees TO read_only;
GRANT read_only TO alice;
GRANT read_only TO bob;
GRANT read_only TO charlie;
-- To revoke: just REVOKE read_only FROM username; or remove privileges from the role

3. Use Descriptive Role Names

Role names should clearly communicate their purpose:

-- Good
CREATE ROLE sales_read_only;
CREATE ROLE hr_data_manager;
CREATE ROLE api_service_account;

-- Bad
CREATE ROLE role1;
CREATE ROLE temp;
CREATE ROLE new_role;

4. Regularly Audit User and Role Assignments

Periodically review who has access to what.

PostgreSQL audit query:

SELECT 
  u.usename AS user_name,
  r.rolname AS role_granted,
  u.valuntil AS password_expires
FROM pg_user u
LEFT JOIN pg_auth_members am ON u.usesysid = am.member
LEFT JOIN pg_roles r ON am.roleid = r.oid
ORDER BY u.usename;

5. Remove Unused Users and Roles

When a team member leaves or an application is decommissioned, clean up:

-- Revoke all roles first
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM former_employee;

-- Then drop the user
DROP USER former_employee;

-- Drop unused roles
DROP ROLE IF EXISTS deprecated_role;

warning

In PostgreSQL, you cannot drop a user or role that owns database objects. You must first reassign ownership:

REASSIGN OWNED BY former_employee TO postgres;
DROP OWNED BY former_employee;
DROP USER former_employee;

Quick Reference Table

Operation	PostgreSQL	MySQL 8.0+	SQL Server
Create user	`CREATE USER x WITH PASSWORD 'p'`	`CREATE USER 'x'@'%' IDENTIFIED BY 'p'`	`CREATE LOGIN x WITH PASSWORD = 'p'`
Create role	`CREATE ROLE r`	`CREATE ROLE 'r'`	`CREATE ROLE r`
Grant role to user	`GRANT r TO x`	`GRANT 'r' TO 'x'@'%'`	`ALTER ROLE r ADD MEMBER x`
Revoke role	`REVOKE r FROM x`	`REVOKE 'r' FROM 'x'@'%'`	`ALTER ROLE r DROP MEMBER x`
Drop user	`DROP USER x`	`DROP USER 'x'@'%'`	`DROP USER x`
Drop role	`DROP ROLE r`	`DROP ROLE 'r'`	`DROP ROLE r`

Conclusion

Understanding how to SQL create user accounts, define roles with appropriate privileges, assign those roles to users, and structure role hierarchies is fundamental to database security. By separating who can connect (users) from what they can do (roles), you create a permission system that is secure, maintainable, and scalable.

Start by defining clear roles that match the access patterns in your organization. Assign those roles to users rather than granting permissions directly. Build role hierarchies when your permission structure naturally layers from basic to advanced access. And always follow the principle of least privilege to keep your database environment safe.

With these practices in place, you will have a robust access control system that grows cleanly as your team and application evolve.

What Are Users and Roles in SQL?​

SQL CREATE USER​

Basic Syntax​

Examples by Database System​

Common Options When Creating Users​

Verifying User Creation​

Common Mistake: Creating a User Without Granting Any Privileges​

SQL CREATE ROLE​

Basic Syntax​

Creating Roles with Privileges​

Practical Example: Defining Multiple Roles​

Roles in Different Database Systems​

Assigning Roles to Users​

Basic Syntax​

Examples​

Assigning Multiple Roles to a Single User​

Revoking Roles from Users​

Practical Example: Full Workflow​

Role Hierarchies​

How Role Hierarchies Work​

Assigning Hierarchical Roles to Users​

Visualizing Role Memberships​

Common Mistake: Circular Role Dependencies​

INHERIT vs. NOINHERIT (PostgreSQL)​

Best Practices for Managing Users and Roles​

1. Follow the Principle of Least Privilege​

2. Use Roles Instead of Direct Grants​

3. Use Descriptive Role Names​

4. Regularly Audit User and Role Assignments​

5. Remove Unused Users and Roles​

Quick Reference Table​

Conclusion​

Table of Contents

What Are Users and Roles in SQL?

SQL CREATE USER

Basic Syntax

Examples by Database System

Common Options When Creating Users

Verifying User Creation

Common Mistake: Creating a User Without Granting Any Privileges

SQL CREATE ROLE

Basic Syntax

Creating Roles with Privileges

Practical Example: Defining Multiple Roles

Roles in Different Database Systems

Assigning Roles to Users

Basic Syntax

Examples

Assigning Multiple Roles to a Single User

Revoking Roles from Users

Practical Example: Full Workflow

Role Hierarchies

How Role Hierarchies Work

Assigning Hierarchical Roles to Users

Visualizing Role Memberships

Common Mistake: Circular Role Dependencies

INHERIT vs. NOINHERIT (PostgreSQL)

Best Practices for Managing Users and Roles

1. Follow the Principle of Least Privilege

2. Use Roles Instead of Direct Grants

3. Use Descriptive Role Names

4. Regularly Audit User and Role Assignments

5. Remove Unused Users and Roles

Quick Reference Table

Conclusion