Skip to content
My Docs

Advanced Security and Permissions

Difficulty: Advanced
Generated on: 2025-07-10 02:34:45
Category: SQL Cheatsheet for Database Development

Advanced SQL Security & Permissions Cheatsheet

Section titled “Advanced SQL Security & Permissions Cheatsheet”

1. Quick Overview

Section titled “1. Quick Overview”

This cheatsheet covers advanced security features and permission management in SQL databases. These features allow fine-grained control over data access, ensuring only authorized users and applications can access sensitive information. Use these techniques to implement the principle of least privilege, protect against data breaches, and comply with security regulations.

2. Syntax

Section titled “2. Syntax”

2.1. Roles (Groups of Users)

Section titled “2.1. Roles (Groups of Users)”

  • Create a Role:

    -- PostgreSQL, SQL Server
    CREATE ROLE role_name [ WITH attribute [ ... ] ];
    

    -- MySQL
    CREATE ROLE 'role_name'@'host';
    • attribute: Options like SUPERUSER, CREATEROLE, LOGIN, PASSWORD, etc.
    • 'role_name'@'host': MySQL requires specifying host for roles.

  • Grant Role Membership:

    -- PostgreSQL, SQL Server
    GRANT role_name TO user_name;
    

    -- MySQL
    GRANT role_name TO 'user_name'@'host';

  • Revoke Role Membership:

    -- PostgreSQL, SQL Server
    REVOKE role_name FROM user_name;
    

    -- MySQL
    REVOKE role_name FROM 'user_name'@'host';

  • Set Role (Temporarily assume the privileges of another role):

    -- PostgreSQL
    SET ROLE role_name;
    

    -- SQL Server:  Not directly supported.  Use EXECUTE AS.
    -- MySQL: Not directly supported.

2.2. Grants (Object Permissions)

Section titled “2.2. Grants (Object Permissions)”

  • Grant Permissions on Objects (Tables, Views, etc.):

    -- PostgreSQL, SQL Server, MySQL, Oracle
    GRANT privilege_list ON object_name TO role_name | user_name;
    • privilege_list: SELECT, INSERT, UPDATE, DELETE, EXECUTE, USAGE, ALL PRIVILEGES, etc. The specific privileges available depend on the database and the object type.
    • object_name: Table name, view name, sequence name, function name, etc.

  • Grant with Grant Option:

    -- PostgreSQL, SQL Server, MySQL, Oracle
    GRANT privilege_list ON object_name TO role_name | user_name WITH GRANT OPTION;
    • Allows the recipient to grant the same privileges to other users/roles.

  • Revoke Permissions:

    -- PostgreSQL, SQL Server, MySQL, Oracle
    REVOKE [GRANT OPTION FOR] privilege_list ON object_name FROM role_name | user_name;
    • GRANT OPTION FOR: Revokes the right to grant the privilege to others.
    • CASCADE: Revokes privileges from objects that depend on the revoked privilege (PostgreSQL).

2.3. Row-Level Security (RLS)

Section titled “2.3. Row-Level Security (RLS)”

  • Create Policy (PostgreSQL, SQL Server):

    -- PostgreSQL
    CREATE POLICY policy_name ON table_name
    FOR {ALL | SELECT | INSERT | UPDATE | DELETE}
    TO {role_name | PUBLIC}
    [USING (boolean_expression)]
    [WITH CHECK (boolean_expression)];
    

    -- SQL Server
    CREATE SECURITY POLICY policy_name
    WITH (STATE = ON | OFF)
    AS predicate_function_name(column_name) = 1;
    

    CREATE FUNCTION predicate_function_name(@column_name data_type)
    RETURNS bit
    WITH SCHEMABINDING
    AS
    BEGIN
        -- Logic to determine if the user has access to the row
        -- Return 1 for access, 0 for no access
        RETURN CASE WHEN condition THEN 1 ELSE 0 END
    END;
    • USING: Determines which rows are visible for SELECT commands.
    • WITH CHECK: Determines which rows are allowed to be inserted/updated.
    • predicate_function_name: SQL Server requires a function to evaluate the predicate. SCHEMABINDING is best practice for performance.

  • Alter Policy (PostgreSQL, SQL Server):

    -- PostgreSQL
    ALTER POLICY policy_name ON table_name RENAME TO new_policy_name;
    ALTER POLICY policy_name ON table_name USING (new_boolean_expression);
    ALTER POLICY policy_name ON table_name WITH CHECK (new_boolean_expression);
    

    -- SQL Server
    ALTER SECURITY POLICY policy_name WITH (STATE = ON | OFF);

  • Drop Policy (PostgreSQL, SQL Server):

    -- PostgreSQL
    DROP POLICY policy_name ON table_name;
    

    -- SQL Server
    DROP SECURITY POLICY policy_name;
    DROP FUNCTION predicate_function_name;

2.4. Data Masking (SQL Server)

Section titled “2.4. Data Masking (SQL Server)”

  • Add Data Mask:

    ALTER TABLE table_name
    ALTER COLUMN column_name ADD MASKED WITH (FUNCTION = 'masking_function');
    • masking_function: Built-in functions like default(), email(), partial(n, 'xxx', n), random(start, end).

  • Remove Data Mask:

    ALTER TABLE table_name
    ALTER COLUMN column_name DROP MASKED;

2.5. Encryption (Column, Table, TDE)

Section titled “2.5. Encryption (Column, Table, TDE)”

  • Column Encryption (Example using PostgreSQL with pgcrypto extension):

    -- Enable the pgcrypto extension
    CREATE EXTENSION IF NOT EXISTS pgcrypto;
    

    -- Encrypt a column during insertion:
    INSERT INTO sensitive_data (name, encrypted_value) VALUES ('Test', pgp_sym_encrypt('Secret Value', 'encryption_key'));
    

    -- Decrypt a column during selection:
    SELECT name, pgp_sym_decrypt(encrypted_value, 'encryption_key') AS decrypted_value FROM sensitive_data;

  • Transparent Data Encryption (TDE - SQL Server, Oracle): Configuration is usually at the database level and managed via database administration tools or specific SQL commands. This encrypts the entire database at rest.

    • SQL Server: Requires creating a database master key, a certificate, and then enabling TDE.
    • Oracle: Requires configuring the Oracle Wallet and setting up encryption keys.

3. Common Use Cases

Section titled “3. Common Use Cases”
  • Separation of Duties: Create roles for different job functions (e.g., data_analyst, application_user, database_admin) and grant only the necessary permissions to each role.
  • Auditing: Track data access and modifications by logging user actions.
  • Multi-tenant Applications: Use RLS to isolate data for different tenants (customers) within the same database.
  • Data Privacy Compliance (GDPR, CCPA): Use data masking and encryption to protect personally identifiable information (PII).
  • Security Hardening: Implement the principle of least privilege to minimize the attack surface.
  • Restricting Access based on Time: Implement logic within RLS policies to restrict access to data based on the current time of day or day of the week. This is useful for limiting access during non-business hours.

4. Best Practices

Section titled “4. Best Practices”
  • Principle of Least Privilege: Grant only the minimum necessary permissions to each user/role.
  • Use Roles: Manage permissions at the role level instead of granting individual permissions to users. This simplifies administration and reduces the risk of errors.
  • Regularly Review Permissions: Periodically review user roles and permissions to ensure they are still appropriate.
  • Automate Permission Management: Use scripts or tools to automate the process of granting and revoking permissions.
  • Secure Encryption Keys: Store encryption keys securely and rotate them regularly. Consider using a hardware security module (HSM) for key management.
  • Test RLS Policies Thoroughly: Ensure that RLS policies are working as expected by testing them with different user roles and data scenarios.
  • Use Parameterized Queries: Prevent SQL injection attacks by using parameterized queries or prepared statements.
  • Enable Auditing: Enable auditing to track data access and modifications.
  • Avoid Using dbo or public Schema: Avoid using these schemas for storing sensitive data, as they often have default permissions that grant wide access. Create dedicated schemas with more restrictive permissions.
  • Monitor Performance: RLS, data masking, and encryption can impact performance. Monitor query performance and optimize queries as needed. Use indexes appropriately.
  • Avoid Dynamic SQL: Dynamic SQL can be vulnerable to SQL injection. Use parameterized queries instead.

5. Examples

Section titled “5. Examples”

5.1. Roles and Grants

Section titled “5.1. Roles and Grants”
-- Create a role for data analysts (PostgreSQL)
CREATE ROLE data_analyst;


-- Grant SELECT permission on the 'customers' table to the data_analyst role
GRANT SELECT ON customers TO data_analyst;


-- Create a user and grant them membership in the data_analyst role
CREATE USER analyst_user WITH PASSWORD 'password';
GRANT data_analyst TO analyst_user;


-- Revoke SELECT permission from a user
REVOKE SELECT ON customers FROM analyst_user;


-- MySQL Example
CREATE ROLE 'data_reader'@'%';
GRANT SELECT ON `mydb`.`employees` TO 'data_reader'@'%';
CREATE USER 'user1'@'%' IDENTIFIED BY 'password';
GRANT 'data_reader'@'%' TO 'user1'@'%';
SET DEFAULT ROLE 'data_reader'@'%' TO 'user1'@'%';

5.2. Row-Level Security (PostgreSQL)

Section titled “5.2. Row-Level Security (PostgreSQL)”
-- Sample table
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    department_id INTEGER,
    salary INTEGER,
    manager_id INTEGER
);


INSERT INTO employees (department_id, salary, manager_id) VALUES
(1, 50000, 101),
(1, 60000, 101),
(2, 70000, 102),
(2, 80000, 102);


-- Create a role for department managers
CREATE ROLE department_manager;


-- Create a policy that allows department managers to see only employees in their department
CREATE POLICY department_policy ON employees
FOR SELECT
TO department_manager
USING (department_id IN (SELECT department_id FROM employees WHERE employee_id = current_user));


-- Simulate current_user (for testing, not for production)
-- For real implementations, the application would set the role of the connection


-- Create a user who is a department manager
CREATE USER manager1 WITH PASSWORD 'password';


-- Set the user's employee_id (This would usually be retrieved from an authentication system)
ALTER USER manager1 SET employee_id = 101;  -- IMPORTANT:  This is a placeholder.  Do not store passwords or employee IDs in SQL.


-- Grant the user membership in the department_manager role
GRANT department_manager TO manager1;


-- Set the role to the user to see the effect of the policy
SET ROLE manager1;


-- Now, when manager1 selects from the employees table, they will only see employees in their department
SELECT * FROM employees; -- Only returns employees with department_id = 1


-- Reset the role back to the default user
RESET ROLE;

5.3. Row-Level Security (SQL Server)

Section titled “5.3. Row-Level Security (SQL Server)”
-- Sample Table
CREATE TABLE Sales  (
    OrderID int,
    SalesPerson nvarchar(50),
    Region nvarchar(50),
    Amount money
);


INSERT INTO Sales VALUES
(1, 'John', 'West', 100),
(2, 'Jane', 'East', 200),
(3, 'John', 'West', 300),
(4, 'Jane', 'East', 400);


-- Create a security function
CREATE FUNCTION Security.fn_securitypredicate(@SalesPerson AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
    WHERE @SalesPerson = USER_NAME() OR USER_NAME() = 'Admin'; -- Allow 'Admin' to see everything


-- Create a security policy
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesPerson) ON dbo.Sales,
ADD BLOCK PREDICATE Security.fn_securitypredicate(SalesPerson) ON dbo.Sales;


-- Create a user
CREATE USER John WITHOUT LOGIN;


-- Grant SELECT permission to the Sales table
GRANT SELECT ON dbo.Sales TO John;


-- Execute as the user John
EXECUTE AS USER = 'John';


-- Select from the Sales table (John will only see his own sales)
SELECT * FROM Sales; -- Only returns rows where SalesPerson = 'John'


-- Revert to the original context
REVERT;


-- Disable the security policy
ALTER SECURITY POLICY SalesFilter WITH (STATE = OFF);


-- Drop the security policy and function
DROP SECURITY POLICY SalesFilter;
DROP FUNCTION Security.fn_securitypredicate;

5.4. Data Masking (SQL Server)

Section titled “5.4. Data Masking (SQL Server)”
-- Sample table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    PhoneNumber VARCHAR(20)
);


INSERT INTO Customers (CustomerID, FirstName, LastName, Email, PhoneNumber) VALUES
(1, 'John', 'Doe', 'john.doe@example.com', '555-123-4567'),
(2, 'Jane', 'Smith', 'jane.smith@example.com', '555-987-6543');


-- Mask the Email column
ALTER TABLE Customers
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');


-- Mask the PhoneNumber column
ALTER TABLE Customers
ALTER COLUMN PhoneNumber ADD MASKED WITH (FUNCTION = 'partial(2, "XXX", 2)');


-- Grant SELECT permission to a user
CREATE USER masked_user WITHOUT LOGIN;
GRANT SELECT ON Customers TO masked_user;


-- Execute as the masked_user
EXECUTE AS USER = 'masked_user';


-- Select from the Customers table (the Email and PhoneNumber columns will be masked)
SELECT * FROM Customers;
-- Expected Output:
-- CustomerID | FirstName | LastName | Email                       | PhoneNumber
-- -----------|-----------|----------|-----------------------------|-------------
-- 1          | John      | Doe      | jXXX@example.com            | 55XXX7
-- 2          | Jane      | Smith    | jXXX@example.com            | 55XXX3


-- Revert to the original context
REVERT;


-- Remove the data mask
ALTER TABLE Customers
ALTER COLUMN Email DROP MASKED;


ALTER TABLE Customers
ALTER COLUMN PhoneNumber DROP MASKED;

5.5 Column Encryption (PostgreSQL)

Section titled “5.5 Column Encryption (PostgreSQL)”
-- Enable the pgcrypto extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;


-- Create table to store sensitive data
CREATE TABLE sensitive_data (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    encrypted_value BYTEA
);


-- Insert encrypted data
INSERT INTO sensitive_data (name, encrypted_value) VALUES ('Test', pgp_sym_encrypt('Secret Value', 'encryption_key'));


-- Select and decrypt data
SELECT name, pgp_sym_decrypt(encrypted_value, 'encryption_key') AS decrypted_value FROM sensitive_data;


-- Clean up:
DROP TABLE sensitive_data;
DROP EXTENSION pgcrypto;

6. Common Pitfalls

Section titled “6. Common Pitfalls”
  • Over-granting Permissions: Granting more permissions than necessary can expose sensitive data.
  • Forgetting to Revoke Permissions: When a user leaves the organization or changes roles, remember to revoke their permissions.
  • Incorrect RLS Policies: Incorrectly configured RLS policies can lead to data leaks or prevent authorized users from accessing data.
  • Performance Impact: RLS, data masking, and encryption can impact query performance. Monitor performance and optimize queries as needed.
  • SQL Injection Vulnerabilities: Using dynamic SQL or failing to sanitize user input can lead to SQL injection attacks.
  • Hardcoding Credentials: Avoid hardcoding usernames, passwords, or encryption keys in your code. Use environment variables or configuration files instead.
  • Ignoring Auditing: Failing to enable auditing can make it difficult to track data access and modifications, making it harder to detect and respond to security incidents.
  • Not testing properly: Always test permission changes and row-level security policies thoroughly before deploying to production.
  • Using PUBLIC schema: Avoid placing sensitive tables in the PUBLIC schema, as this schema often has default permissions granted to all users.

7. Database Variations

Section titled “7. Database Variations”
FeatureMySQLPostgreSQLSQL ServerOracle
RolesYes, with host specificationYesYesYes
GrantsSimilar syntaxSimilar syntaxSimilar syntaxSimilar syntax
Row-Level SecurityAvailable in Enterprise EditionYesYesVirtual Private Database (VPD) is similar
Data MaskingNot natively supported, requires custom solutionsNot natively supported, requires custom solutionsYesData Redaction is similar
Column EncryptionAES functions, 3rd party solutionspgcrypto extensionTDE, Always EncryptedTDE, Transparent Sensitive Data Protection
Transparent Data Encryption (TDE)YesNot natively supportedYesYes

Key Differences:

  • MySQL: Roles require host specification (e.g., 'role_name'@'localhost'). RLS is only available in the Enterprise Edition. Data masking requires custom solutions.
  • PostgreSQL: Offers a robust RLS implementation. Column encryption is typically achieved using the pgcrypto extension. TDE is generally implemented using full disk encryption.
  • SQL Server: Provides both RLS and data masking features. Offers TDE and Always Encrypted for encryption.
  • Oracle: Uses Virtual Private Database (VPD) for RLS, which is similar in concept to PostgreSQL and SQL Server’s RLS. Offers Data Redaction which is similar to Data Masking in SQL Server. Provides TDE and Transparent Sensitive Data Protection for encryption.

This cheatsheet provides a comprehensive overview of advanced SQL security and permissions. Remember to consult the documentation for your specific database system for the most accurate and up-to-date information. Always prioritize security best practices to protect your data.