1. User-Defined Schemas:

  • Create Schemas: Organize tables and objects into distinct schemas to logically separate data.
  • Grant Permissions: Assign specific permissions (SELECT, INSERT, UPDATE, DELETE, EXECUTE) to users or roles on individual schemas.
  • Example:
CREATE SCHEMA Sales;
CREATE TABLE Sales.Customers (CustomerID INT, Name VARCHAR(50));

GRANT SELECT ON SCHEMA::Sales TO SalesRole;

2. Row-Level Security (RLS):

  • Apply Dynamic Filters: Enforce fine-grained access control at the row level based on user attributes or conditions.
  • Create Security Policies: Define RLS policies using predicate functions that filter rows based on user context.
  • Example:
CREATE SECURITY POLICY SalesFilter
    ON dbo.Sales
    WITH (STATE = ON)
    AS PERMISSIVE
    WHERE SalesTerritory = USER_NAME();

3. Server-Level Roles:

  • Predefined Roles: Utilize built-in roles like db_datareader, db_datawriter, db_ddladmin, or custom roles to grant broader permissions across schemas.
  • Assign Roles: Grant appropriate roles to users or groups based on their access needs.
  • Example:
ALTER ROLE db_datareader ADD MEMBER SalesUser;

4. Credentials and External Users:

  • Secure External Access: For accessing Synapse from external tools, create database scoped credentials and external users with specific schema permissions.
  • Map External Users: Map external users to database users with defined permissions.
  • Example:
CREATE DATABASE SCOPED CREDENTIAL MyCredential WITH IDENTITY = 'user1', SECRET = 'password';
CREATE EXTERNAL USER [MyExternalUser] WITH PASSWORD = 'mypassword';
GRANT SELECT ON SCHEMA::Sales TO [MyExternalUser];

5. Dynamic Data Masking (DDM):

  • Obfuscate Sensitive Data: Restrict sensitive data visibility for unauthorized users by masking it with placeholder values.
  • Define Masking Rules: Create DDM policies to specify which columns to mask and the masking method.
  • Example:
CREATE MASKING POLICY SocialSecurityMaskingPolicy
    AS MASKED WITH (FUNCTION = 'partial(0,"XXXX-XX",6)')
    ON Sales.Customers(SocialSecurityNumber);

Additional Considerations:

  • Monitor Access: Track user access and permissions for security auditing and compliance.
  • Regularly Review: Periodically review and update access controls to align with evolving requirements and security best practices.
  • Tailor Approaches: Choose the most suitable methods based on your specific security needs and data organization.