Tutorial 12: Security Configuration¶

-- Create Azure AD user in Synapse
CREATE USER [user@company.com] FROM EXTERNAL PROVIDER;

-- Create Azure AD group
CREATE USER [SynapseDataEngineers] FROM EXTERNAL PROVIDER;

-- Grant permissions to AD group
GRANT SELECT ON SCHEMA::reporting TO [SynapseDataEngineers];
GRANT EXECUTE ON SCHEMA::etl TO [SynapseDataEngineers];

-- View AD users
SELECT
    name,
    type_desc,
    authentication_type_desc
FROM sys.database_principals
WHERE type IN ('E', 'X');  -- E = External User, X = External Group

-- Grant managed identity access to storage
-- (Done via Azure Portal or CLI)
-- az role assignment create --assignee <managed-identity-id> \
--   --role "Storage Blob Data Reader" \
--   --scope /subscriptions/<sub>/resourceGroups/<rg>/providers/Microsoft.Storage/storageAccounts/<account>

-- Use managed identity in SQL
CREATE DATABASE SCOPED CREDENTIAL ManagedIdentityCredential
WITH IDENTITY = 'Managed Identity';
GO

CREATE EXTERNAL DATA SOURCE SecureDataLake
WITH (
    LOCATION = 'https://securestorage.dfs.core.windows.net/data',
    CREDENTIAL = ManagedIdentityCredential
);
GO

-- Create credential for service principal
CREATE DATABASE SCOPED CREDENTIAL ServicePrincipalCredential
WITH
    IDENTITY = '<application-id>@https://login.microsoftonline.com/<tenant-id>/oauth2/token',
    SECRET = '<client-secret>';
GO

-- Grant column-level permissions
GRANT SELECT ON fact.Sales(SaleID, DateKey, TotalAmount) TO DataAnalyst;

-- Deny access to sensitive columns
DENY SELECT ON dim.Customer(CreditCardNumber, SSN) TO DataAnalyst;

-- Grant execute on specific procedures
GRANT EXECUTE ON etl.LoadDailySales TO DataEngineer;
GRANT EXECUTE ON reporting.GetSalesSummary TO DataAnalyst;

-- View effective permissions
SELECT
    permission_name,
    state_desc
FROM sys.fn_my_permissions('fact.Sales', 'OBJECT');

-- Create schema for security objects
CREATE SCHEMA security;
GO

-- Create user mapping table
CREATE TABLE security.UserDataAccess
(
    UserId INT IDENTITY(1,1),
    UserEmail VARCHAR(200) NOT NULL,
    AccessLevel VARCHAR(50) NOT NULL,  -- 'All', 'Region', 'Country'
    Region VARCHAR(50) NULL,
    Country VARCHAR(100) NULL,
    IsActive BIT DEFAULT 1
)
WITH (DISTRIBUTION = REPLICATE);

-- Populate user access
INSERT INTO security.UserDataAccess (UserEmail, AccessLevel, Region, Country) VALUES
('global.admin@company.com', 'All', NULL, NULL),
('regional.manager.na@company.com', 'Region', 'North America', NULL),
('regional.manager.eu@company.com', 'Region', 'Europe', NULL),
('country.manager.us@company.com', 'Country', 'North America', 'United States'),
('country.manager.uk@company.com', 'Country', 'Europe', 'United Kingdom');

-- Create security predicate function
CREATE FUNCTION security.fn_DataAccessPredicate(@Region VARCHAR(50), @Country VARCHAR(100))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS AccessGranted
    WHERE
        -- Global access
        EXISTS (
            SELECT 1 FROM security.UserDataAccess
            WHERE UserEmail = USER_NAME()
              AND AccessLevel = 'All'
              AND IsActive = 1
        )
        OR
        -- Regional access
        EXISTS (
            SELECT 1 FROM security.UserDataAccess
            WHERE UserEmail = USER_NAME()
              AND AccessLevel = 'Region'
              AND Region = @Region
              AND IsActive = 1
        )
        OR
        -- Country access
        EXISTS (
            SELECT 1 FROM security.UserDataAccess
            WHERE UserEmail = USER_NAME()
              AND AccessLevel = 'Country'
              AND Region = @Region
              AND Country = @Country
              AND IsActive = 1
        );
GO

-- Apply security policy to sales view
CREATE SECURITY POLICY security.SalesDataPolicy
ADD FILTER PREDICATE security.fn_DataAccessPredicate(Region, Country)
ON reporting.vw_SalesByRegion,
ADD FILTER PREDICATE security.fn_DataAccessPredicate(Region, Country)
ON fact.Sales
WITH (STATE = ON);
GO

-- Test RLS
EXECUTE AS USER = 'country.manager.us@company.com';
SELECT COUNT(*) AS AccessibleRows FROM fact.Sales;
REVERT;

EXECUTE AS USER = 'global.admin@company.com';
SELECT COUNT(*) AS AccessibleRows FROM fact.Sales;
REVERT;

-- Add dynamic data masking to sensitive columns
ALTER TABLE dim.Customer
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

ALTER TABLE dim.Customer
ALTER COLUMN PhoneNumber ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XXX-",4)');

ALTER TABLE dim.Customer
ALTER COLUMN CreditCardNumber ADD MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)');

ALTER TABLE dim.Customer
ALTER COLUMN SSN ADD MASKED WITH (FUNCTION = 'default()');

-- Grant unmask permission to specific users
GRANT UNMASK ON dim.Customer TO DataAdmin;

-- View masked data (as regular user)
EXECUTE AS USER = 'analyst@company.com';
SELECT CustomerID, CustomerName, Email, PhoneNumber, CreditCardNumber
FROM dim.Customer;
REVERT;

-- View unmasked data (as admin)
EXECUTE AS USER = 'admin@company.com';
SELECT CustomerID, CustomerName, Email, PhoneNumber, CreditCardNumber
FROM dim.Customer;
REVERT;

-- View masking configuration
SELECT
    OBJECT_NAME(object_id) AS table_name,
    name AS column_name,
    masking_function
FROM sys.masked_columns;

-- View current firewall rules (via Azure CLI)
-- az synapse workspace firewall-rule list --workspace-name myworkspace --resource-group myrg

-- Create firewall rule
-- az synapse workspace firewall-rule create \
--   --workspace-name myworkspace \
--   --resource-group myrg \
--   --name AllowOffice \
--   --start-ip-address 203.0.113.0 \
--   --end-ip-address 203.0.113.255

-- Allow Azure services
-- az synapse workspace firewall-rule create \
--   --workspace-name myworkspace \
--   --resource-group myrg \
--   --name AllowAllAzureIps \
--   --start-ip-address 0.0.0.0 \
--   --end-ip-address 0.0.0.0

-- Check TDE status
SELECT
    db.name AS database_name,
    db.is_encrypted,
    de.encryption_state,
    de.encryption_state_desc,
    de.key_algorithm,
    de.key_length
FROM sys.databases db
LEFT JOIN sys.dm_database_encryption_keys de ON db.database_id = de.database_id;

-- TDE is enabled by default for dedicated SQL pools
-- Using service-managed keys (recommended for most cases)

-- For customer-managed keys (CMK):
-- Configure via Azure Key Vault integration

-- Create column master key
CREATE COLUMN MASTER KEY CMK_Auto
WITH (
    KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT',
    KEY_PATH = 'https://mykeyvault.vault.azure.net/keys/AlwaysEncryptedKey/xxx'
);

-- Create column encryption key
CREATE COLUMN ENCRYPTION KEY CEK_Auto
WITH VALUES (
    COLUMN_MASTER_KEY = CMK_Auto,
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x01700000...  -- Generated value
);

-- Create table with encrypted columns
CREATE TABLE secure.SensitiveData
(
    ID INT PRIMARY KEY,
    SSN CHAR(11) ENCRYPTED WITH (
        COLUMN_ENCRYPTION_KEY = CEK_Auto,
        ENCRYPTION_TYPE = Deterministic,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    ),
    Salary DECIMAL(10,2) ENCRYPTED WITH (
        COLUMN_ENCRYPTION_KEY = CEK_Auto,
        ENCRYPTION_TYPE = Randomized,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    ),
    Department VARCHAR(50)
);

-- View audit settings (via Azure Portal or CLI)
-- Auditing is configured at workspace level

-- Query audit logs (stored in Log Analytics or Storage)
-- Sample KQL query for Log Analytics:
/*
SynapseAuditLogs
| where TimeGenerated > ago(24h)
| where Category == "SQLSecurityAuditEvents"
| where ActionName in ("SELECT", "INSERT", "UPDATE", "DELETE")
| project
    TimeGenerated,
    PrincipalName,
    ActionName,
    ObjectName,
    Statement
| order by TimeGenerated desc
*/

-- View failed logins
SELECT
    event_time,
    session_id,
    database_name,
    server_principal_name,
    client_ip,
    succeeded
FROM sys.fn_get_audit_file
    ('https://audit.blob.core.windows.net/audit/*', default, default)
WHERE succeeded = 0
ORDER BY event_time DESC;

-- Query Synapse activity via SQL
SELECT
    request_id,
    status,
    [label],
    command,
    resource_class,
    submit_time,
    start_time,
    end_time,
    total_elapsed_time / 1000.0 AS elapsed_seconds
FROM sys.dm_pdw_exec_requests
WHERE submit_time >= DATEADD(hour, -24, GETUTCDATE())
ORDER BY submit_time DESC;

-- Query by specific user
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE [label] LIKE '%user@company.com%'
ORDER BY submit_time DESC;

-- Add sensitivity classifications
ADD SENSITIVITY CLASSIFICATION TO dim.Customer.Email
WITH (LABEL = 'Confidential - PII', INFORMATION_TYPE = 'Contact Info');

ADD SENSITIVITY CLASSIFICATION TO dim.Customer.SSN
WITH (LABEL = 'Highly Confidential', INFORMATION_TYPE = 'National ID');

ADD SENSITIVITY CLASSIFICATION TO dim.Customer.CreditCardNumber
WITH (LABEL = 'Confidential - Financial', INFORMATION_TYPE = 'Financial');

ADD SENSITIVITY CLASSIFICATION TO dim.Customer.PhoneNumber
WITH (LABEL = 'Confidential - PII', INFORMATION_TYPE = 'Contact Info');

-- View classifications
SELECT
    OBJECT_NAME(major_id) AS table_name,
    COL_NAME(major_id, minor_id) AS column_name,
    Label AS sensitivity_label,
    information_type
FROM sys.sensitivity_classifications;

-- Generate classification report
EXEC sp_generate_classifications_report;