Configure SQL granular permissions using T-SQL

Completed

If you're familiar with relational databases and enterprise warehouses, it's common knowledge that there are four fundamental permissions governing Data Manipulation Language (DML) operations. These permissions, namely SELECT, INSERT, UPDATE, and DELETE, are universally applicable across all database platforms.

All of these permissions can be granted, revoked or denied on tables and views. If a permission is granted using the GRANT statement, then the permission is given to the user or role referenced in the GRANT statement. Users can also be denied permissions using the DENY command. If a user is granted a permission and denied the same permission, the DENY will always supersede the grant, and the user will be denied access to the specific object.

Table and view permissions

Tables and views represent the objects on which permissions can be granted within a warehouse. Within those tables and views, you can additionally restrict the columns that are accessible to a given security principal.

Permission	Definition
SELECT	Allows the user to view the data within the object (table or view). When denied, the user will be prevented from viewing the data within the object.
INSERT	Allows the user to insert data into the object. When denied, the user will be prevented from inserting data into the object.
UPDATE	Allows the user the update data within the object. When denied, the user will be prevented from updating data in the object.
DELETE	Allows the user to delete data within the object. When denied, the user will be prevented from deleting data from the object.

Function and stored procedure permissions

Like tables and views, functions and stored procedures have several permissions, which can be granted or denied.

Permission	Definition
ALTER	Grants the user the ability to change the definition of the object.
CONTROL	Grants the user all rights to the object.

Principle of least privilege

The basic idea of the principle of least privilege is that users and applications should only be given the permissions needed in order for them to complete the task. Applications should only have permissions that they need to do in order to complete the task at hand.

As an example, if an application accesses all data through stored procedures, then the application should only have the permission to execute the stored procedures, with no access to the tables.

Dynamic SQL

Dynamic SQL is a concept where a query is built programmatically. Dynamic SQL allows T-SQL statements to be generated within a stored procedure or a query itself. A simple example is shown below.

CREATE PROCEDURE sp_TopTenRows @tableName NVARCHAR(128)
AS
BEGIN
    DECLARE @query NVARCHAR(MAX);
    SET @query = N'SELECT TOP 10 * FROM ' + QUOTENAME(@tableName);
    EXEC sp_executesql @query;
END;

This example demonstrates a stored procedure that accepts a table name as a parameter and returns the top 10 rows from that table. This could be useful for quickly inspecting tables in a data warehouse.

CREATE PROCEDURE sp_TopTenRows @tableName NVARCHAR(128)
AS
BEGIN
    DECLARE @query NVARCHAR(MAX);
    SET @query = N'SELECT TOP 10 * FROM ' + QUOTENAME(@tableName);
    EXEC sp_executesql @query;
END;

In this example, @tableName is the parameter that you can replace with the name of the table you want to inspect. The QUOTENAME function is used to safely quote the table name, preventing SQL injection attacks. The sp_executesql stored procedure is then used to execute the dynamically built query.

Please note that this is a simple example and real-world data warehouse tasks might require more complex dynamic SQL queries. Always be cautious when using dynamic SQL due to the risk of SQL injection attacks. Always use parameterization methods like sp_executesql or QUOTENAME to sanitize inputs.