SQL Users, Permissions, and Security: Complete Defense Guide

1. CREATE USER

In production database management systems, client applications and human engineers must never share a single administrative account. The CREATE USER statement registers distinct user accounts in the data catalog.

MySQL Syntax

CREATE USER 'analyst_jane'@'%' IDENTIFIED BY 'SecureP@ssw0rd!';

PostgreSQL Syntax

CREATE USER analyst_jane WITH PASSWORD 'SecureP@ssw0rd!';

Microsoft SQL Server Syntax

-- Create server-level login
CREATE LOGIN analyst_jane WITH PASSWORD = 'SecureP@ssw0rd!';

-- Map login to a database user
CREATE USER analyst_jane FOR LOGIN analyst_jane;

`Principle of Least Privilege (PoLP)`

Minimum Access:Every application service and developer should receive only the minimum permissions necessary to complete their specific job.
Never Use Root:Web applications must never connect using the root or sa superuser accounts.

2. GRANT Statement

When a user account is first created, it possesses zero privileges. The GRANT statement assigns specific permissions on database objects:

GRANT SELECT, INSERT ON employees TO analyst_jane;

-- Grant all permissions on an entire database
GRANT ALL PRIVILEGES ON sales_db.* TO 'app_backend'@'%';

Column-Level Permissions

You can restrict privileges to specific columns, preventing analysts from inspecting sensitive payroll figures while allowing them to view operational details:

GRANT SELECT (id, name, department_id, hire_date) ON employees TO analyst_jane;

`WITH GRANT OPTION Risk`

Permission Propagation:Appending WITH GRANT OPTION allows the grantee to transfer their privileges to other database users.
Security Advice:Reserve this option exclusively for senior database administrators to maintain tight authorization control.

3. REVOKE Statement

When an employee transitions roles or an application service no longer requires write access, revoke their privileges using REVOKE:

-- Revoke insert privileges
REVOKE INSERT ON employees FROM analyst_jane;

-- Revoke all permissions on a table
REVOKE ALL ON employees FROM analyst_jane;

If permissions were originally granted using WITH GRANT OPTION, appending CASCADE ensures that any downstream privileges granted by that user are also revoked.

4. Roles and Privileges (Role-Based Access Control)

Managing permissions on a per-user basis becomes unmanageable as organizations scale. Role-Based Access Control (RBAC) solves this by grouping permissions into logical roles and assigning roles to users.

-- Step 1: Create a role
CREATE ROLE readonly_analyst;

-- Step 2: Grant permissions to the role
GRANT SELECT ON orders TO readonly_analyst;
GRANT SELECT ON order_items TO readonly_analyst;

-- Step 3: Assign the role to individual users
GRANT readonly_analyst TO analyst_jane, analyst_bob;

When business requirements change, updating the role immediately adjusts permissions for all assigned users simultaneously.

`Privilege Categorization`

DML Privileges:SELECT, INSERT, UPDATE, DELETE permissions on specific tables and views.
DDL Privileges:CREATE, ALTER, DROP permissions for schema definitions and migrations.
Administrative Privileges:SUPERUSER, REPLICATION, BACKUP, and PROCESS rights for database operations.

5. What is SQL Injection?

SQL Injection (SQLi) is a security vulnerability that occurs when untrusted user input is directly concatenated into a dynamic SQL string before execution.

Instead of treating the user input as passive data, the database query parser interprets the attacker payload as executable SQL syntax. This enables attackers to bypass authentication gates, exfiltrate confidential databases, modify financial records, or drop entire tables.

6. SQL Injection Examples

Example 1: Authentication Bypass

Consider a poorly written backend login endpoint that concatenates input strings:

-- Vulnerable application code
query = "SELECT * FROM users WHERE username = '" + userInput + "' AND password = '" + passInput + "'";

If an attacker enters the following payload into the username field:

admin' --

The resulting query executed by the database becomes:

SELECT * FROM users WHERE username = 'admin' --' AND password = '...';

The double dash (--) comments out the password verification check entirely. The database returns the administrator record, and the attacker logs in without a password.

Example 2: Data Exfiltration via UNION

If a public product search field concatenates input, an attacker can append a UNION SELECT statement to extract credit card numbers or password hashes:

' UNION SELECT id, username, password_hash, email FROM admin_users --

7. How to Prevent SQL Injection

Defending against SQL injection requires systematic coding standards across application layers:

Parameterized Queries: The primary defense. Separates the SQL query structure from literal input parameters.
Input Allowlisting: When identifiers (such as column names or sorting directions like ASC/DESC) cannot be parameterized, validate inputs against a strict hardcoded allowlist.
Restricted Database Accounts: Ensure the web application database account does not have DROP TABLE or administrative privileges.
Object Relational Mapping (ORM): Modern ORMs (like Prisma, Hibernate, and SQLAlchemy) parameterize queries automatically by default.

8. Prepared Statements and Parameterized Queries

A prepared statement pre-compiles the SQL query template on the database server before data is supplied. When input values arrive, the database treats them strictly as literal values, never as executable SQL commands. Even if a user enters ' OR '1'='1, the engine searches for that exact literal text string.

Implementation in Node.js (PostgreSQL)

-- Safe parameterized query
const text = 'SELECT * FROM users WHERE username = $1 AND password_hash = $2';
const values = [userInput, hashedPass];
const res = await client.query(text, values);

Implementation in Python (sqlite3 / psycopg2)

# Safe parameterized execution
cursor.execute('SELECT * FROM users WHERE email = ?', (user_email,))

Restricting Column Exposure with Views

SQL

Shield confidential salary figures by exposing a restricted virtual schema

9. Backup and Restore a Database

Reliable backup strategies protect systems against ransomware, hardware corruption, and accidental deletions:

Logical Backups: Generates SQL scripts containing CREATE TABLE and INSERT statements (e.g., pg_dump, mysqldump). Portable across server versions.
Physical Backups: Copies raw database disk blocks and transaction logs directly. Fast to restore for multi-terabyte enterprise databases.

-- PostgreSQL command line backup and restore
pg_dump -U postgres -d production_db > backup.sql
psql -U postgres -d restored_db < backup.sql

-- MySQL command line backup and restore
mysqldump -u root -p production_db > backup.sql
mysql -u root -p restored_db < backup.sql

Running Storage Integrity Checks

SQL

Verify physical database page integrity and record count consistency

`Point-In-Time Recovery (PITR)`

Continuous WAL Archiving:Enterprise databases save Write-Ahead Log segments continuously to cloud object storage.
Exact Replay:Administrators restore the nightly base backup and replay transaction logs up to the exact second before an accidental DROP TABLE.

10. Encrypting Sensitive Data

Thorough database defense requires encrypting data across multiple physical and application layers:

Encryption Layer	Mechanism	Threat Mitigated
In Transit	Transport Layer Security (TLS / SSL)	Man-in-the-middle packet sniffing
At Rest	Transparent Data Encryption (TDE / AES-256)	Stolen physical hard drives or backups
Column / Application Level	Client-side encryption / Salted password hashing	Compromised database administrator credentials

`Password Security Rule`

Never Store Plaintext:Never store passwords in plain text or using reversible symmetric encryption.
Use Salted Hash Algorithms:Always use modern adaptive one-way hashing algorithms such as bcrypt, argon2, or scrypt.

Knowledge Check: SQL Users, Permissions & Security

1. Which SQL command grants permissions to a user or role?

2. What is the primary defense against SQL injection?

3. Why is Role-Based Access Control preferred over direct grants?

4. What does the REVOKE statement accomplish?

5. What is Transparent Data Encryption?

6. How does Point-In-Time Recovery restore databases?