SQL Security

Database security controls who can connect and what they can do.

Concept

Database security controls who can connect and what they can do.

Authentication — verifying identity: "Who are you?" (passwords, certificates, Kerberos/Active Directory, IAM tokens, LDAP).

Authorization — verifying permissions: "What are you allowed to do?" (privileges granted to users and roles).

User / login An identity that can connect (SQL Server distinguishes server *logins* from database *users*)

A named collection of privileges, granted to users (or other roles) Privilege / permission

The right to perform an action on an object (SELECT on a table, EXECUTE on a procedure)

Namespace; privileges can often be granted per schema

Why It Matters

Data breaches often come from over-privileged accounts (an application connecting as a superuser).

Regulations (GDPR, HIPAA, PCI DSS, automotive cybersecurity standards such as ISO/SAE 21434 for connected systems) require access control and auditing.

Least privilege limits the damage of SQL injection, leaked credentials, and human error.

Syntax

GRANT privilege_list ON object TO grantee [WITH GRANT OPTION];
REVOKE privilege_list ON object FROM grantee [CASCADE | RESTRICT];

-- Roles
CREATE ROLE role_name;
GRANT role_name TO user_name;
REVOKE role_name FROM user_name;

Example

30.1 Creating users and roles (Never put real passwords in scripts committed to source control; use a secrets manager.) 30.2 Granting privileges Read rows (optionally specific columns)

Modify rows (optionally specific columns) Run procedures/functions

Create foreign keys referencing the table TRIGGER, TRUNCATE PostgreSQL-specific table privileges CREATE, ALTER, DROP DDL on schemas/objects ALL [PRIVILEGES] Everything (avoid for applications)

WITH GRANT OPTION allows the grantee to grant the same privilege to others. Use it rarely.

30.3 Revoking privileges REVOKE... CASCADE (PostgreSQL, standard) also revokes privileges that the grantee passed on via GRANT OPTION.

SQL Server also has DENY, which overrides any GRANT from role membership:

30.4 Inspecting privileges 30.5 Principle of least privilege

Grant each identity only the permissions needed for its job, nothing more.

Additional controls:

Row-level security (RLS): restrict which rows a user sees (PostgreSQL CREATE POLICY, SQL Server security policies, Oracle VPD).

Column-level security / data masking: SQL Server Dynamic Data Masking, views that hide columns.

Encryption: TLS in transit; transparent data encryption (TDE) at rest; column-level encryption for highly sensitive fields.

Auditing: log logins, privilege changes, and access to sensitive tables (pgAudit, SQL Server Audit, Oracle Unified Auditing, MySQL Enterprise Audit).

No shared accounts; separate accounts per application/service and per human.

Network restrictions: firewall rules, private subnets, pg_hba.conf, MySQL host patterns.

-- PostgreSQL (users are roles with LOGIN)
CREATE ROLE reporting_ro NOLOGIN;
CREATE ROLE app_rw NOLOGIN;
CREATE ROLE analyst_ravi LOGIN PASSWORD 'use-a-strong-generated-secret';
CREATE ROLE orders_service LOGIN PASSWORD 'use-a-strong-generated-secret';

-- MySQL 8
CREATE ROLE 'reporting_ro', 'app_rw';
CREATE USER 'analyst_ravi'@'10.0.%' IDENTIFIED BY 'use-a-strong-generated-secret';
CREATE USER 'orders_service'@'%' IDENTIFIED BY 'use-a-strong-generated-secret';

-- SQL Server
CREATE LOGIN analyst_ravi WITH PASSWORD = 'use-a-strong-generated-secret';
CREATE USER analyst_ravi FOR LOGIN analyst_ravi;       -- inside the database
CREATE ROLE reporting_ro;

-- Oracle
CREATE USER analyst_ravi IDENTIFIED BY "use-a-strong-generated-secret";
GRANT CREATE SESSION TO analyst_ravi;
CREATE ROLE reporting_ro;

-- Read-only reporting role
GRANT SELECT ON customers, orders, order_items, products TO reporting_ro;

-- PostgreSQL: all current tables in a schema + future tables
GRANT USAGE ON SCHEMA public TO reporting_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reporting_ro;

-- Application role: DML only, no DDL
GRANT SELECT, INSERT, UPDATE ON orders, order_items TO app_rw;
GRANT SELECT ON customers, products TO app_rw;
GRANT UPDATE (stock) ON products TO app_rw;          -- column-level privilege

-- EXECUTE on a procedure (users call the procedure but cannot touch tables directly)
GRANT EXECUTE ON PROCEDURE place_order(INT, INT, INT, INT) TO app_rw;   -- PostgreSQL
GRANT EXECUTE ON dbo.place_order TO app_rw;                             -- SQL Server
GRANT EXECUTE ON PROCEDURE shop.place_order TO 'app_rw';                 -- MySQL

-- Assign roles to users
GRANT reporting_ro TO analyst_ravi;                        -- PostgreSQL, Oracle
GRANT 'reporting_ro' TO 'analyst_ravi'@'10.0.%';           -- MySQL (then SET DEFAULT ROLE ALL TO ...)
ALTER ROLE reporting_ro ADD MEMBER analyst_ravi;           -- SQL S
…

REVOKE INSERT, UPDATE ON orders FROM app_rw;
REVOKE reporting_ro FROM analyst_ravi;
REVOKE ALL PRIVILEGES ON employees FROM PUBLIC;   -- PUBLIC = every user

DENY SELECT ON employees(salary) TO reporting_ro;

-- Standard information_schema (PostgreSQL, MySQL, SQL Server)
SELECT grantee, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_name = 'orders';

-- MySQL
SHOW GRANTS FOR 'analyst_ravi'@'10.0.%';

-- PostgreSQL psql meta-command
\dp orders

Role design for an e-commerce system

db_owner / migration_user   → DDL (used only by CI/CD migrations)
orders_service_role         → SELECT/INSERT/UPDATE on order tables; EXECUTE place_order
catalog_service_role        → SELECT/INSERT/UPDATE on products
reporting_ro                → SELECT on reporting views only
support_agent_role          → SELECT on customer/order views with masked PII
dba_role                    → administrative, used with MFA and auditing

-- PostgreSQL row-level security: agents see only customers in their region
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
CREATE POLICY region_isolation ON customers
    USING (country = current_setting('app.current_country'));

Expected Result

Logged in as analyst_ravi (member of reporting_ro with SELECT only):

SELECT COUNT(*) FROM orders;             -- 6
DELETE FROM orders WHERE order_id = 1004;
-- ERROR: permission denied for table orders            (PostgreSQL)
-- ERROR 1142: DELETE command denied to user ...        (MySQL)

Common Mistakes

Applications connecting as root, sa, postgres, or SYS.

GRANT ALL to application accounts.

Granting directly to users instead of roles (hard to audit and revoke).

Forgetting default privileges for future tables (PostgreSQL).

Leaving PUBLIC privileges on sensitive objects.

Storing passwords in plain text in code or config files.

Never reviewing or revoking access for people who changed roles or left.

Best Practices

Least privilege; role-based access control.

Separate accounts for migrations (DDL) and runtime (DML).

Use views/procedures as controlled access layers.

Enable auditing for privileged actions.

Rotate credentials; use a secrets manager or IAM authentication.

Review privileges periodically.

Interview Relevance

Medium-high: "Authentication vs authorization?", "GRANT and REVOKE examples?", "What is least privilege?", "What is a role?", "How would you give an analyst read-only access?", "What is row-level security?".

Practice Questions

Create a role that can read only products and orders and grant it to a new user.

Allow a support role to update only the status column of orders.

Revoke all privileges from PUBLIC on employees.

Explain why the runtime application account should not be able to run DROP TABLE.

Design roles for a hospital database (doctor, nurse, billing, auditor).