Users and Permissions

Manage database access with users, roles, and permissions.

20 min read
Intermediate

Introduction to Database Security

Your database contains valuable data - customer information, financial records, business secrets. Who should have access to it? What can they do with it? This is where database security comes in.

Users and permissions control who can access your database and what actions they can perform. Without proper security:

  • Developers might accidentally delete production data
  • Analysts could view sensitive customer information
  • Applications might have more permissions than needed
  • Malicious users could steal or corrupt data

Real-world scenario: Your e-commerce database has:

  • Developers: Need full access to development database, read-only to production
  • Analysts: Can SELECT data for reports, cannot modify anything
  • Web application: Can INSERT orders, SELECT products, cannot DROP tables
  • ETL jobs: Can INSERT/UPDATE in data warehouse, read from production

Each gets exactly the permissions they need - no more, no less. This is the principle of least privilege.

Security best practice: grant users only the minimum permissions required to perform their job. Reduces risk of accidental or malicious damage.

Common Database Permissions
Permission
Allows
Typical User
SELECTRead dataAnalysts, reports
INSERTAdd new dataApplications
UPDATEModify existing dataApplications
DELETERemove dataApplications (carefully!)
CREATECreate tables/databasesDevelopers, admins
DROPDelete tables/databasesAdmins only
GRANTGive permissions to othersAdmins only

Creating Database Users

CREATE USER creates a new database account:

sql
-- MySQL / MariaDB
CREATE USER 'analyst'@'localhost' IDENTIFIED BY 'secure_password_123';

-- PostgreSQL
CREATE USER analyst WITH PASSWORD 'secure_password_123';

-- SQL Server
CREATE LOGIN analyst WITH PASSWORD = 'secure_password_123';
CREATE USER analyst FOR LOGIN analyst;

User format in MySQL: 'username'@'host'

  • 'analyst'@'localhost': Can only connect from local server
  • 'analyst'@'192.168.1.%': Can connect from IP range
  • 'analyst'@'%': Can connect from anywhere (use cautiously!)

Best practices:

  • Use strong passwords (long, random, mix of characters)
  • Restrict host to specific IPs when possible
  • Never use 'root' or 'admin' for applications
  • Use different users for different applications
sql
-- Create users with limited hosts
CREATE USER 'web_app'@'10.0.1.50' IDENTIFIED BY 'app_password_xyz';
CREATE USER 'backup_user'@'10.0.2.100' IDENTIFIED BY 'backup_pass_abc';
CREATE USER 'developer'@'localhost' IDENTIFIED BY 'dev_password_def';

Security alert: Never hardcode passwords in scripts! Use environment variables, secret managers, or configuration files with restricted permissions.

GRANT - Giving Permissions

GRANT assigns permissions to users. Syntax:

sql
GRANT permission ON database.table TO 'user'@'host';

Example 1: Read-only access (analysts)

sql
-- Grant SELECT on all tables in database
GRANT SELECT ON company_db.* TO 'analyst'@'localhost';

-- Grant SELECT on specific tables only
GRANT SELECT ON company_db.employees TO 'analyst'@'localhost';
GRANT SELECT ON company_db.orders TO 'analyst'@'localhost';

Now the analyst can read data:

sql
-- As analyst user
SELECT name, salary FROM employees;
sql
-- But cannot modify
UPDATE employees SET salary = 100000 WHERE name = 'Alice';
-- Error: UPDATE command denied to user 'analyst'@'localhost'

Example 2: Application user (read/write)

sql
-- Web app needs SELECT, INSERT, UPDATE
GRANT SELECT, INSERT, UPDATE ON company_db.orders TO 'web_app'@'10.0.1.50';
GRANT SELECT, INSERT, UPDATE ON company_db.customers TO 'web_app'@'10.0.1.50';
GRANT SELECT ON company_db.products TO 'web_app'@'10.0.1.50';

Example 3: Full access to specific database

sql
-- Developer gets all permissions on development database
GRANT ALL PRIVILEGES ON dev_db.* TO 'developer'@'localhost';

-- ALL PRIVILEGES includes: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, etc.

Wildcard shortcuts:

  • database.* = all tables in database
  • *.* = all databases and tables (dangerous!)
  • table_name = specific table

GRANT Options and Variations

Grant specific columns only:

sql
-- HR can see names and departments, but not salaries
GRANT SELECT (id, name, department) ON employees TO 'hr_user'@'localhost';
sql
-- As hr_user
SELECT name, department FROM employees;  -- Works
SELECT name, salary FROM employees;      -- Error: column 'salary' access denied

WITH GRANT OPTION - Delegate permissions:

sql
-- Senior developer can grant permissions to others
GRANT SELECT, INSERT ON company_db.* TO 'senior_dev'@'localhost' WITH GRANT OPTION;

-- Now senior_dev can grant to others:
-- As senior_dev:
GRANT SELECT ON company_db.employees TO 'junior_dev'@'localhost';

Be careful with WITH GRANT OPTION! Users can grant their permissions to anyone, potentially creating security holes. Only give to trusted administrators.

Grant on stored procedures:

sql
-- App can execute specific procedures without table access
GRANT EXECUTE ON PROCEDURE calculate_order_total TO 'web_app'@'10.0.1.50';
GRANT EXECUTE ON PROCEDURE process_payment TO 'web_app'@'10.0.1.50';

REVOKE - Removing Permissions

REVOKE removes permissions from users:

sql
REVOKE permission ON database.table FROM 'user'@'host';
sql
-- Remove DELETE permission from web app
REVOKE DELETE ON company_db.orders FROM 'web_app'@'10.0.1.50';

-- Remove all permissions on a table
REVOKE ALL PRIVILEGES ON company_db.salary_history FROM 'analyst'@'localhost';

-- Remove specific permissions
REVOKE INSERT, UPDATE ON company_db.products FROM 'junior_dev'@'localhost';

Revoke WITH GRANT OPTION:

sql
-- Remove ability to grant, but keep the permission itself
REVOKE GRANT OPTION FOR SELECT ON company_db.* FROM 'senior_dev'@'localhost';

Cascading revokes: In some databases, revoking from a user who granted to others may cascade (revoke from everyone). Check your database documentation!

Roles - Grouping Permissions

Roles are named collections of permissions. Instead of granting to each user individually, grant to a role, then assign users to roles.

A named set of permissions that can be granted to multiple users. Simplifies permission management - change role once, affects all users with that role.

Creating roles:

sql
-- MySQL 8.0+ / PostgreSQL
CREATE ROLE analyst_role;
CREATE ROLE developer_role;
CREATE ROLE app_role;

Grant permissions to roles:

sql
-- Analyst role: read-only
GRANT SELECT ON company_db.employees TO analyst_role;
GRANT SELECT ON company_db.orders TO analyst_role;
GRANT SELECT ON company_db.products TO analyst_role;

-- Developer role: full access to dev database
GRANT ALL PRIVILEGES ON dev_db.* TO developer_role;

-- App role: limited CRUD operations
GRANT SELECT, INSERT, UPDATE ON company_db.orders TO app_role;
GRANT SELECT ON company_db.products TO app_role;

Assign roles to users:

sql
-- Grant role to user
GRANT analyst_role TO 'alice'@'localhost';
GRANT analyst_role TO 'bob'@'localhost';
GRANT developer_role TO 'charlie'@'localhost';
GRANT app_role TO 'web_app'@'10.0.1.50';

Now Alice and Bob automatically get all analyst_role permissions!

Benefits of roles:

  • Easier management: Update role once, affects all users
  • Consistency: All analysts have identical permissions
  • Auditing: Clear which users have which roles
  • Faster onboarding: New analyst? Just grant analyst_role

Example: When analyst needs access to new table

sql
-- Without roles: Grant to each analyst individually
GRANT SELECT ON company_db.new_table TO 'alice'@'localhost';
GRANT SELECT ON company_db.new_table TO 'bob'@'localhost';
GRANT SELECT ON company_db.new_table TO 'carol'@'localhost';
-- 20 more analysts...

-- With roles: Grant once to role
GRANT SELECT ON company_db.new_table TO analyst_role;
-- Done! All analysts now have access.

Best practice: Always use roles for production systems. Direct user grants should be rare exceptions.

Viewing Permissions

Show current user:

sql
-- MySQL
SELECT USER();

-- PostgreSQL
SELECT CURRENT_USER;

Show grants for a user:

sql
-- MySQL
SHOW GRANTS FOR 'analyst'@'localhost';
sql
-- PostgreSQL
SELECT * FROM information_schema.role_table_grants
WHERE grantee = 'analyst';

List all users:

sql
-- MySQL
SELECT User, Host FROM mysql.user;

-- PostgreSQL
SELECT usename FROM pg_user;

List all roles:

sql
-- MySQL 8.0+
SELECT User FROM mysql.user WHERE account_locked = 'Y';

-- PostgreSQL
SELECT rolname FROM pg_roles;

Deleting Users and Roles

DROP USER:

sql
-- Remove user completely
DROP USER 'old_employee'@'localhost';

-- Drop if exists (no error if doesn't exist)
DROP USER IF EXISTS 'temp_user'@'localhost';

DROP ROLE:

sql
-- Remove role
DROP ROLE IF EXISTS old_role;

Dropping users doesn't revoke object ownership! If a user created tables, views, or procedures, those objects remain after the user is dropped. Reassign ownership first:

-- PostgreSQL
REASSIGN OWNED BY old_user TO new_user;
DROP OWNED BY old_user;
DROP USER old_user;

Password Management

Change user password:

sql
-- MySQL
ALTER USER 'analyst'@'localhost' IDENTIFIED BY 'new_secure_password_456';

-- PostgreSQL
ALTER USER analyst WITH PASSWORD 'new_secure_password_456';

-- SQL Server
ALTER LOGIN analyst WITH PASSWORD = 'new_secure_password_456';

Require password change on next login:

sql
-- MySQL
ALTER USER 'analyst'@'localhost' PASSWORD EXPIRE;

Set password expiration policy:

sql
-- MySQL: Password expires every 90 days
ALTER USER 'analyst'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Never expires
ALTER USER 'system_user'@'localhost' PASSWORD EXPIRE NEVER;

Account locking:

sql
-- Lock account (user can't log in)
ALTER USER 'suspicious_user'@'localhost' ACCOUNT LOCK;

-- Unlock
ALTER USER 'suspicious_user'@'localhost' ACCOUNT UNLOCK;

Security Best Practices

1. Principle of Least Privilege:

sql
-- Good: Web app has only what it needs
GRANT SELECT, INSERT ON orders TO 'web_app'@'10.0.1.50';

-- Bad: Web app has everything
GRANT ALL PRIVILEGES ON *.* TO 'web_app'@'10.0.1.50';  -- NO!

2. Never use root/admin for applications:

sql
-- Bad: App uses root account
-- If app is compromised, attacker has full database access!

-- Good: App uses limited account
CREATE USER 'shopping_cart_app'@'10.0.1.50' IDENTIFIED BY 'app_password';
GRANT SELECT ON products TO 'shopping_cart_app'@'10.0.1.50';
GRANT INSERT, UPDATE ON orders TO 'shopping_cart_app'@'10.0.1.50';

3. Use SSL/TLS for connections:

sql
-- Require encrypted connections
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;

-- Specific certificate required
GRANT ALL ON db.* TO 'secure_user'@'%' REQUIRE X509;

4. Regular permission audits:

sql
-- Find users with dangerous permissions
SELECT User, Host FROM mysql.user WHERE Super_priv = 'Y';

-- Find users who can grant permissions
SELECT User, Host FROM mysql.user WHERE Grant_priv = 'Y';

5. Separate prod and dev environments:

sql
-- Developer: full access to dev, read-only to prod
GRANT ALL PRIVILEGES ON dev_db.* TO 'developer'@'localhost';
GRANT SELECT ON prod_db.* TO 'developer'@'localhost';  -- Read-only!

6. Use roles consistently:

Create standard roles for your organization:

  • readonly_role: SELECT only
  • app_role: SELECT, INSERT, UPDATE
  • developer_role: Full dev access, read prod
  • dba_role: Full access everywhere
  • analyst_role: SELECT on specific tables

Then assign users to roles, not individual permissions.

Real-World Permission Scenarios

Scenario 1: E-commerce web application

sql
-- Create app user
CREATE USER 'ecommerce_app'@'10.0.1.50' IDENTIFIED BY 'secure_app_password';

-- Products: read-only
GRANT SELECT ON shop_db.products TO 'ecommerce_app'@'10.0.1.50';
GRANT SELECT ON shop_db.categories TO 'ecommerce_app'@'10.0.1.50';

-- Customers: full CRUD
GRANT SELECT, INSERT, UPDATE ON shop_db.customers TO 'ecommerce_app'@'10.0.1.50';

-- Orders: can create and view, cannot delete
GRANT SELECT, INSERT, UPDATE ON shop_db.orders TO 'ecommerce_app'@'10.0.1.50';
GRANT SELECT, INSERT ON shop_db.order_items TO 'ecommerce_app'@'10.0.1.50';

-- Cannot access admin tables
-- No permissions on shop_db.admin_users, shop_db.audit_log

Scenario 2: Data analyst team

sql
-- Create analyst role
CREATE ROLE analyst_role;

-- Grant read access to business data
GRANT SELECT ON company_db.orders TO analyst_role;
GRANT SELECT ON company_db.customers TO analyst_role;
GRANT SELECT ON company_db.products TO analyst_role;
GRANT SELECT ON company_db.sales_metrics TO analyst_role;

-- NO access to sensitive tables
-- No permissions on company_db.employees (has salaries)
-- No permissions on company_db.financial_statements

-- Add analysts to role
GRANT analyst_role TO 'alice'@'localhost';
GRANT analyst_role TO 'bob'@'localhost';
GRANT analyst_role TO 'carol'@'localhost';

Scenario 3: ETL batch job

sql
-- ETL job needs to load data into warehouse
CREATE USER 'etl_job'@'10.0.2.100' IDENTIFIED BY 'etl_password';

-- Read from production (source)
GRANT SELECT ON prod_db.* TO 'etl_job'@'10.0.2.100';

-- Write to warehouse (destination)
GRANT SELECT, INSERT, UPDATE, DELETE ON warehouse_db.* TO 'etl_job'@'10.0.2.100';

-- Can truncate staging tables for reload
GRANT DROP, CREATE ON warehouse_db.staging_* TO 'etl_job'@'10.0.2.100';

Common Mistakes to Avoid

Don't make these mistakes:

  1. Granting ALL to everyone: ❌

    • GRANT ALL ON *.* TO 'app'@'%' is dangerous!
    • Only grant what's needed

  2. Using root for applications: ❌

    • If app is hacked, attacker owns your database
    • Create dedicated app users with limited permissions

  3. Allowing connections from anywhere: ❌

    • 'user'@'%' accepts any host
    • Restrict to specific IPs: 'user'@'10.0.1.50'

  4. Not using roles: ❌

    • Direct grants are hard to manage at scale
    • Use roles for consistency

  5. Forgetting to revoke when employees leave: ❌

    • Drop user accounts immediately
    • Review permissions quarterly

  6. Weak passwords: ❌

    • 'password123' is not secure!
    • Use long, random passwords
    • Store in secret manager, not code

  7. Giving GRANT OPTION carelessly: ❌

    • Users can grant their permissions to anyone
    • Reserve for DBAs only

  8. Not auditing permissions: ❌

    • Regularly review who has what access
    • Look for privilege creep over time

Key Takeaways

What you learned:

✅ CREATE USER creates database accounts with passwords ✅ GRANT assigns permissions to users or roles ✅ REVOKE removes permissions ✅ Roles group permissions for easier management ✅ Principle of least privilege: Grant minimum permissions needed ✅ Never use root/admin accounts for applications ✅ Restrict user connections by host/IP when possible ✅ Use SSL/TLS for remote connections ✅ Regularly audit permissions and remove unused accounts ✅ Use strong passwords and rotate them periodically

Permission levels from safest to most dangerous:

  1. SELECT: Read-only (safest)
  2. INSERT: Can add data
  3. UPDATE: Can modify data
  4. DELETE: Can remove data (dangerous)
  5. CREATE/DROP: Can modify schema (very dangerous)
  6. ALL PRIVILEGES: Full control (most dangerous)
  7. WITH GRANT OPTION: Can grant to others (extremely dangerous)

Real-world workflow:

  1. Create user or role
  2. Grant minimum permissions needed
  3. Test that user can do their job
  4. Document permissions in your security policy
  5. Audit quarterly, revoke unused accounts

Remember: Security is not a one-time setup. It requires ongoing vigilance and regular audits!

Practice Exercise: Set up these users:

  1. Create 'report_user' with read-only access to orders and customers tables
  2. Create 'backup_admin' with full access to backup and restore
  3. Create 'app_service' with INSERT/SELECT on transactions, SELECT on products
  4. Create 'data_analyst_role' and assign three users to it
  5. Revoke DELETE permissions from 'junior_dev'
🗄️

SQL Fundamentals

Master SQL from basics to advanced topics. Learn to query, manipulate, and analyze data in relational databases.