User Management and Security in SQL – Protecting Data with Roles, Privileges, and Policies

Data security is one of the most critical concerns in modern database systems. As SQL databases often contain sensitive business and personal data, ensuring that only authorized users can access and manipulate that data is vital.

In this chapter, we’ll explore the essential concepts and techniques for SQL user management and database security, including:

• Creating users and roles
• Granting and revoking privileges
• Understanding access control models
• Security best practices
• Preventing SQL injection
• Role-based access control (RBAC)
• Encryption and auditing features

We will also highlight differences in user management across popular databases like MySQL, PostgreSQL, Oracle, and SQL Server.

What is User Management in SQL?

User management involves:

• Creating individual database users
• Assigning them specific access rights (permissions)
• Grouping users into roles for easier management
• Ensuring authentication, authorization, and accountability

Most SQL systems use Access Control Lists (ACLs) or Role-Based Access Control (RBAC) to manage user access.

Creating and Managing Users

The first step is to create users who can connect to and interact with the database.

MySQL:

CREATE USER 'report_user'@'localhost' IDENTIFIED BY 'StrongPass123!';

PostgreSQL:

CREATE ROLE report_user WITH LOGIN PASSWORD 'StrongPass123!';

SQL Server:

CREATE LOGIN report_user WITH PASSWORD = 'StrongPass123!';
CREATE USER report_user FOR LOGIN report_user;

Oracle:

CREATE USER report_user IDENTIFIED BY StrongPass123!;
GRANT CONNECT TO report_user;

Privileges – What Can a User Do?

Privileges define what actions a user can perform on database objects (tables, views, procedures, etc.).

Privilege	Description
SELECT	Read data
INSERT	Add new rows
UPDATE	Modify existing data
DELETE	Remove data
EXECUTE	Run stored procedures
USAGE	Use schemas, sequences, and other resources
ALL PRIVILEGES	All of the above

Granting Privileges:

GRANT SELECT, INSERT ON Sales TO report_user;

Revoking Privileges:

REVOKE INSERT ON Sales FROM report_user;

Granting with GRANT OPTION:

Allows a user to grant their privileges to others.

GRANT SELECT ON Products TO analyst_user WITH GRANT OPTION;

Roles – Managing Groups of Users

A role is a named set of privileges that can be assigned to multiple users.

Creating and Assigning Roles:

-- PostgreSQL
CREATE ROLE data_analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO data_analyst;
GRANT data_analyst TO user_john;

Activating Roles:

Some databases require users to activate a role during a session.

SET ROLE data_analyst;

Dropping a Role:

DROP ROLE data_analyst;

Roles simplify privilege management and promote least privilege principles.

Schema-Level and Object-Level Security

Permissions can be applied at:

• Database level (connect, create)
• Schema level (usage, create table)
• Object level (specific tables, views, procedures)

Example – Restricting access to a single table:

GRANT SELECT ON Customers TO read_only_role;

System-Level Security Controls

Password Policies:

• Enforce strong passwords
• Set password expiration
• Enable account lockout after failed attempts

Session Controls:

• Limit max sessions per user
• Track user sessions (via views or system tables)

Connection Restrictions:

• MySQL: Restrict by IP using 'user'@'host'
• PostgreSQL: Use pg_hba.conf file for authentication rules

SQL Injection – A Major Security Threat

SQL Injection is an attack where malicious input alters the intended SQL logic.

Vulnerable Query:

SELECT * FROM Users WHERE Username = 'admin' AND Password = 'pass';

If input is:

Username: admin' --

The query becomes:

SELECT * FROM Users WHERE Username = 'admin' --' AND Password = 'pass';

Mitigation Techniques:

• Use prepared statements with parameterized queries
• Avoid dynamic SQL where possible
• Validate and sanitize user input

Example (Safe – Python):

cursor.execute("SELECT * FROM Users WHERE Username = %s", (username,))

Auditing – Tracking Who Did What

Auditing helps track:

• User logins/logouts
• Data changes (INSERT/UPDATE/DELETE)
• Privilege changes

PostgreSQL:

Enable pgaudit extension.

CREATE EXTENSION pgaudit;

SQL Server:

Use SQL Server Audit:

CREATE SERVER AUDIT AuditLogin
TO FILE (FILEPATH = 'C:\AuditLogs');

Encryption in SQL Databases

Data-at-Rest Encryption:

Protects files and storage.

• SQL Server Transparent Data Encryption (TDE)
• MySQL and PostgreSQL disk-level encryption

Data-in-Transit Encryption:

• Use SSL/TLS connections
• Force secure connections using server configs

Column-Level Encryption:

-- SQL Server
CREATE COLUMN ENCRYPTION KEY MyKey;
-- Application handles encryption and decryption

Use encryption to protect PII, financial, and medical data.

Security Best Practices

1. Enforce Least Privilege: Give users only what they need
2. Use Roles Instead of Direct Grants
3. Rotate Passwords Regularly
4. Monitor and Audit Privileged Access
5. Avoid Hardcoded Credentials in Applications
6. Use SSL/TLS for Connections
7. Log All Access Attempts
8. Enable Firewall or Network-Level Controls
9. Patch Database Engines Regularly

Real-World Scenario: Analytics Platform Access Control

Imagine a company with different roles:

• Analyst – read-only access to reports and tables
• Manager – read/write to forecast data
• DBA – full control

Setup Roles:

CREATE ROLE analyst;
CREATE ROLE manager;
CREATE ROLE dba;

Assign Permissions:

GRANT SELECT ON ReportData TO analyst;
GRANT SELECT, INSERT, UPDATE ON ForecastData TO manager;
GRANT ALL PRIVILEGES ON DATABASE company TO dba;

Assign Users:

GRANT analyst TO user_amy;
GRANT manager TO user_bob;
GRANT dba TO admin_user;

Monitor Usage:

Enable query logging and track user activities.

Common Security Mistakes

Mistake	Consequence
Using GRANT ALL liberally	Users may access or destroy sensitive data
No password policies	Easier for attackers to compromise accounts
Allowing root/admin connections remotely	Full access from outside network
Not auditing changes	Hard to track malicious or accidental changes
Static credentials in application code	Easily extracted and misused

Summary

Robust user management and security in SQL databases ensures that your data is protected from unauthorized access and manipulation. By combining:

• Clear user-role assignment
• Granular privilege control
• Security features like encryption and auditing
• Vigilant coding and access policies

you build a database system that is safe, compliant, and trustworthy.

In the next chapter, we will focus on Performance Optimization in SQL, where you’ll learn how to tune queries, use indexes effectively, and design efficient schemas for large-scale applications.