Creating Users and Roles in SQL – Managing Database Access

Overview

In SQL-based databases, managing users and roles is crucial for maintaining security, access control, and operational efficiency. You can define who can connect to the database, what actions they can perform, and which data they can access using user accounts and roles (also known as privileges or permissions).

What Is a User in SQL?

A user is a database-level account used to authenticate and authorize a connection to the database. Each user can be granted specific privileges on databases, tables, or views.

Syntax to Create a User

MySQL:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

PostgreSQL:

CREATE USER username WITH PASSWORD 'password';

What Is a Role in SQL?

A role is a named group of privileges that can be assigned to one or more users. Roles help simplify permission management by assigning a set of permissions to a role instead of individual users.

📘 Syntax to Create a Role

PostgreSQL / Standard SQL:

CREATE ROLE role_name;
GRANT SELECT, INSERT ON table_name TO role_name;
GRANT role_name TO username;

MySQL (MySQL 8+):

CREATE ROLE 'role_name';
GRANT SELECT, INSERT ON database_name.* TO 'role_name';
GRANT 'role_name' TO 'username'@'host';

Granting and Revoking Privileges

Grant Permissions:

GRANT SELECT, INSERT, UPDATE ON database_name.table_name TO 'username'@'host';

Revoke Permissions:

REVOKE INSERT ON database_name.table_name FROM 'username'@'host';

👥 Example: Role-Based Access Control

-- Create a role for analysts
CREATE ROLE analyst;

-- Grant permissions to role
GRANT SELECT ON sales_data TO analyst;

-- Create a user and assign the role
CREATE USER 'john'@'localhost' IDENTIFIED BY 'secure123';
GRANT analyst TO 'john'@'localhost';

This example gives the user john read-only access to sales_data via the analyst role.

Best Practices

• Use roles to manage privileges at scale
• Use strong passwords and avoid default usernames
• Grant minimum necessary privileges (principle of least privilege)
• Regularly audit user access and revoke unused accounts

Supported In