Database Security

Database security answers three questions. Who may connect. What may they do once connected. And what is readable if a copy of the data ends up somewhere it should not be.

This lesson is about building safely, so everything here is a defensive measure. Nothing in it explains how to attack a database, and you do not need that knowledge to protect one.

One idea runs through all three answers: reduce what is possible, rather than relying on nothing going wrong. A component that cannot delete rows will not delete rows through a bug, a mistake or a compromise.

Least privilege is that idea applied to permissions. Every account gets exactly what its job needs and nothing more. It is the highest-value change most applications can make, and it is usually a configuration change rather than a code change.

The practical way to apply it is through roles rather than individual accounts. Grant permissions to a role that describes a job — the ordering application's write access, the reporting tool's read access — then add accounts to the role. When a fourth service needs the same access, it joins the role instead of collecting its own grants that nobody can audit.

-- A login for the application. The password comes from your secret store,
-- never from a script in version control.
CREATE LOGIN ordering_app WITH PASSWORD = '<from-secret-store>';

USE ordering;
CREATE USER ordering_app FOR LOGIN ordering_app;

-- A role describing a job, so permissions are auditable in one place
CREATE ROLE ordering_app_role;

-- Read what it displays
GRANT SELECT ON dbo.customers    TO ordering_app_role;
GRANT SELECT ON dbo.products     TO ordering_app_role;
GRANT SELECT ON dbo.orders       TO ordering_app_role;
GRANT SELECT ON dbo.order_items  TO ordering_app_role;

-- Write only what it is responsible for
GRANT INSERT, UPDATE ON dbo.orders      TO ordering_app_role;
GRANT INSERT, UPDATE ON dbo.order_items TO ordering_app_role;

-- Run the procedures it is meant to use
GRANT EXECUTE ON dbo.usp_submit_order TO ordering_app_role;

ALTER ROLE ordering_app_role ADD MEMBER ordering_app;

-- Deliberately absent, and each absence is a decision:
--   no DELETE on any table        -- cancellation sets a status instead
--   no permission on employees    -- salaries are not this application's business
--   no ALTER or CREATE            -- schema changes run under a separate account
--   no membership of db_owner     -- see the warning below

• Permissions are granted per table and per action, so SELECT on a table does not imply the ability to change it. That granularity is what makes least privilege achievable rather than aspirational.
• No DELETE anywhere is the change with the largest effect for the least effort. Most business data should not be destroyed in the first place — a cancelled order becomes a cancelled order, not a missing one — and an account without DELETE cannot lose rows to a faulty WHERE clause.
• Nothing is granted on employees. The ordering application never displays a salary, so a defect or a compromise in it cannot read one. Deciding what an account cannot reach is as much a part of the design as deciding what it can.
• Migrations run under a different account with schema permissions, used by the deployment pipeline and not by the running application. That way an application with a SQL defect cannot drop a table, because it has no permission to.
• The password placeholder is deliberate. Credentials belong in a secret store or a managed identity, and a connection string with a real password must not be in version control, in a container image, or in a script like this one.
• Roles also make review possible. "What can the ordering application do?" is answered by reading one role's grants, rather than by auditing whatever accumulated on an account over four years.

Parameterised queries are the structural fix for injection. SQL injection happens when a value supplied by a user is combined into the text of a statement. Once a value is part of the statement text, the database has no way to know which characters were supposed to be data, so anything in that value that looks like SQL syntax is treated as part of the command.

The fix is structural rather than defensive. A parameterised query sends the statement and the values to the database separately. The statement's structure is fixed before any value is attached, so a value can never change what the statement does — it is data, and it stays data.

This is why input filtering is the weaker answer. Escaping quotes, stripping keywords or validating with a pattern all attempt to guess which values are dangerous. Parameters remove the category of danger, and they are less code than the filtering they replace.

// Vulnerable: the value becomes part of the statement text.
// Whatever the user typed is parsed as SQL along with the rest.
var unsafeSql = $"SELECT customer_id, company_name FROM customers WHERE country = '{country}'";
using var bad = new SqlCommand(unsafeSql, connection);

// Safe: the statement text is fixed, and the value travels separately.
const string sql = @"
    SELECT customer_id, company_name
    FROM   customers
    WHERE  country = @country
      AND  credit_limit >= @minimumCredit";

using var command = new SqlCommand(sql, connection);
command.Parameters.Add("@country", SqlDbType.NVarChar, 80).Value = country;
command.Parameters.Add("@minimumCredit", SqlDbType.Decimal).Value = minimumCredit;

using SqlDataReader reader = await command.ExecuteReaderAsync();

• In the first version the statement text is assembled from a value, so its structure depends on what the value contains. That dependency is the whole vulnerability — not the absence of validation, but the fact that data and command have been mixed.
• In the second version the text containing @country is fixed at compile time and never changes. The value is sent alongside it as a parameter, and the database binds it after parsing, so it cannot affect the statement's meaning.
• Declaring the type and length on the parameter is worth the extra characters. It documents what the column expects, avoids a silent conversion, and helps the database reuse the cached plan for the query.
• The same rule applies through every layer. Stored procedures called with parameters are safe; a stored procedure that concatenates its arguments into dynamic SQL is not, and the risk is easier to miss because it looks like a procedure call from outside.
• If you use an ORM such as Entity Framework Core, LINQ queries are parameterised for you. The exceptions to watch for are raw SQL methods and string interpolation into them — FromSqlRaw with a concatenated string reintroduces exactly this problem, while FromSqlInterpolated parameterises the interpolated values.
• Note what parameters do not do: they do not authorise anything. A parameterised query running as an administrative account still has administrative reach. Least privilege and parameterisation address different problems and you need both.