Manage Users, Roles, Grants, and Prevent SQL Injection - Textnotes

Manage Users, Roles, Grants, and Prevent SQL Injection

Learn SQL security and permissions in this beginner-friendly guide. Master creating users, assigning roles, using GRANT/REVOKE, securing views and stored procedures, and preventing SQL injection with parameterized queries.

1. Introduction

Database security is critical to protect sensitive data and ensure authorized access.

  1. SQL permissions allow administrators to control what users can view or modify.
  2. Security best practices prevent data breaches and SQL injection attacks.

Key Points:

  1. Roles simplify management by grouping users with similar privileges.
  2. Always limit access to only required operations.
  3. Use parameterized queries to prevent SQL injection.

2. Users and Roles

2.1 Create Users


-- SQL Server Example
CREATE LOGIN ReadOnlyUser WITH PASSWORD = 'StrongP@ssword123';
CREATE USER ReadOnlyUser FOR LOGIN ReadOnlyUser;

2.2 Create Roles


CREATE ROLE ReadOnlyRole;

2.3 Assign Users to Roles


ALTER ROLE ReadOnlyRole ADD MEMBER ReadOnlyUser;

3. GRANT and REVOKE Permissions

3.1 GRANT Permissions


-- Allow select only
GRANT SELECT ON Employees TO ReadOnlyRole;

-- Grant multiple permissions
GRANT INSERT, UPDATE ON Departments TO AdminRole;

3.2 REVOKE Permissions


REVOKE UPDATE ON Departments FROM AdminRole;

Key Points:

  1. GRANT gives permissions; REVOKE removes them.
  2. Use principle of least privilege for security.

4. Views and Stored Procedures for Security

  1. Views can limit columns and rows users can access.
  2. Stored procedures allow controlled access to operations without exposing table structures.

Example – View for read-only employee data:


CREATE VIEW vw_EmployeeReadOnly AS
SELECT ID, Name, Department, Salary
FROM Employees;

Grant access to view:


GRANT SELECT ON vw_EmployeeReadOnly TO ReadOnlyRole;

Stored Procedure Example:


CREATE PROCEDURE GetEmployeeByDept
@DeptID INT
AS
BEGIN
SELECT ID, Name, Salary
FROM Employees
WHERE DepartmentID = @DeptID;
END;

Grant execute access:


GRANT EXECUTE ON GetEmployeeByDept TO ReadOnlyRole;

5. SQL Injection Prevention

SQL injection occurs when malicious input alters SQL queries.

Unsafe Query Example:


-- Vulnerable to SQL injection
SELECT * FROM Employees WHERE Name = ' " + @name + " ';

Safe Query Using Parameterized Queries:


-- SQL Server example using T-SQL
DECLARE @name NVARCHAR(50) = 'John';
SELECT * FROM Employees WHERE Name = @name;

Tips:

  1. Use stored procedures with parameters.
  2. Avoid dynamic SQL with user input.
  3. Always validate and sanitize input.

6. Practical Exercises

  1. Create a user with read-only access to the Employees table.
  2. Create a role and assign multiple users to it.
  3. Grant SELECT permission on a view but not on the table.
  4. Create a stored procedure for department-based employee access and grant execute permission.
  5. Rewrite a vulnerable query using parameterized queries to prevent SQL injection.

7. Tips for Beginners

  1. Always follow the principle of least privilege.
  2. Use roles instead of assigning permissions individually.
  3. Limit direct table access; use views and stored procedures for operations.
  4. Parameterized queries are mandatory for user input.
  5. Regularly audit users and permissions to maintain database security.


Next Step: After mastering SQL security and permissions, the next module is Backup and Recovery in SQL, where you’ll learn full, differential, and transaction log backups, and how to restore databases safely.