1️⃣ Overview
The Data Warehouse (DWH) enforces role-based access control (RBAC) and Multi-Factor Authentication (MFA) to secure access across:
- SQL Server databases
- Storage accounts (via Azure Storage Explorer or SAS tokens)
- myBMT application (with dedicated application login & role-based access)
- APIs (with access key validation)
All users must authenticate using MFA when accessing database resources and storage accounts.
Access Governance and Security
- Group-Based Role Assignments:
- Users are granted access only to the necessary resources.
- Regular Access Reviews:
- Periodic audits to ensure correct role assignments.
- Revocation of access when users change roles or leave.
- Authentication & Security Controls:
- myBMT-based authentication for application and API access.
- RBAC enforcement across storage and database layers.
- Audit logs track access and modification activity.
2️⃣ User Access and Membership Groups
Volumes – Users and Type
- 21 registered users in myBMT with login access to the pipeline process.
- Access to DWH databases and the
get.myviewfunction is managed via membership groups:- DWH Dev Admins (2 members)
- DWH Dev Users (4 members)
- DWH Prd Admins (4 members) + DWH Dev Admins
- DWH Prd Users (8 members) + DWH Dev Users
3️⃣ SQL Server Access Control
SQL Server permissions are structured using role groups mapped to schemas, ensuring that users and applications have controlled and appropriate access.
Database-Level Permissions
| User / Role Group | Assigned Role(s) | Permissions |
|---|---|---|
bmt-dwh-sql-bi | read_ifs, memb_cdm, read_hbs, read_mvw | Read access to key schemas (ifs, cdm, hbs, mvw). |
bmt-dwh-sql-dv | db_owner, db_datareader, db_datawriter | Full database access, including write operations. |
bmt-dwh-sql-my | db_datareader | Read access to my schema. |
bmt-dwh-sql-stg | db_datareader | Read access to staging schemas. |
dbo | db_owner | Full database ownership. |
DWH Dev Admins | db_owner | Full control over Development databases. |
DWH Dev Users | memb_cdm, exec_ifs | Read and execution permissions on cdm and ifs schemas. |
DWH Prod Admins | db_owner | Full control over Production databases. |
Schema-Level Access Control
- Read Access (
db_datareaderroles) is granted for Development (dv), staging, and myBMT app. - Schema based Read Access (
read_ifs) is granted to key schemas. - Write Access (
db_datawriter) is limited to Development (dv) and Data Engineering roles. - Execution Privileges (
exec_ifs) allow controlled execution of procedures in the IFS schema. - Full Ownership (
db_owner) is granted to Administrators and Production Admins only.
The user bmt-dwh-sql-bi is used to emulate the actions of users arriving via group based MFA
4️⃣ myBMT Application Access & Role-Based Permissions
Dedicated Application Login
The myBMT application operates using a dedicated application login, ensuring:
- Centralised access control for application-based interactions.
- Consistent role-based permissions, reducing the risk of unauthorised modifications.
- Seamless API and database access, aligning with security best practices.
User Groups & Access Levels
myBMT enforces access through role-based user groups, ensuring controlled interactions based on responsibility levels:
| myBMT Role | Permissions |
|---|---|
| Admin | Full system control, including database and storage settings. |
| Developer | Full access to all items and data, but no system controls. |
| DE&A (Data Engineers & Analysts) | View all items, can perform actions only on assigned items. |
| Manager | View assigned items, can perform actions only on assigned items. |
| Client | View assigned items only, no modification rights. |
5️⃣ Resource Access Controls
User access is strictly governed across the following core resources:
Storage Containers
- Development (Dev) and Production (Prd) storage accounts.
- Access Managed via: RBAC, container-level permissions.
SQL Servers
- Staging and Global SQL Servers (both Dev and Prd).
- Access Controlled By:
- Membership group assignments.
- Restricted read/write access based on user role.
- Query execution limitations for non-developer roles.
myBMT Application
- Login-based user access controls pipeline processes.
- Special Privileges:
- myBMT itself has Dev Admin access to Storage and SQL resources.
- Users within myBMT adhere to DWH membership roles.
API Access
- Access is paired with an access key, validated against registered myBMT users.
- Ensures only authenticated and authorised API users can interact with DWH resources.
- API access enforces User validation via myBMT.
Power BI Workspaces
- Access to Power BI reports and datasets is controlled via role-based workspaces.
- Workspaces align with SQL Server role groups to ensure consistent security across platforms.
- Row-level security (RLS) and workspace permissions further restrict data access as necessary.
6️⃣ SQL Server Access Control & Data Segmentation
SQL Server permissions are structured using role groups mapped to schemas, enforcing principle of least privilege.
General Data Access
- A majority of data is delivered via a batch process from the IFS ERP system.
- It is assumed that this data has been validated for general report consumption.
- General security access rules apply to this data, and it is further constrained by Power BI report writer controls (workspaces and row-level security).
- The function get.myView provides a single governed access method for all data within SQL Server, ensuring uniform security and controlled data retrieval.
Restricted Data Access
- Data identified as ‘restricted’ (e.g., financial and people-related data) is subject to stricter access controls, including:
- Separate Storage Accounts
- Dedicated SQL Server Databases
- Schema Role-Based Access Control (RBAC)
Only authorised users with appropriate use-case and schema-level permissions can access restricted data.
7️⃣ Multi-Factor Authentication (MFA) Enforcement
✅ Required for all users accessing:
- SQL Server databases
- Storage accounts (via Azure Storage Explorer)
MFA is enforced through Azure AD Conditional Access Policies, ensuring:
- All interactive sign-ins require MFA verification.
- MFA validates user identity before granting access.
- Users must re-authenticate periodically.
8️⃣ Azure Storage Account Access
Access via Azure Storage Explorer
Users access storage containers using Azure Storage Explorer with RBAC permissions:
- Storage Blob Reader permissions are assigned via Azure Portal to approved user groups.
- Only read access is provided through this method to prevent unintended modifications.
Access via SAS Tokens
For write (POST) actions, access is granted via SAS (Shared Access Signature) tokens:
- SAS tokens are time-limited and role-specific.
- They are issued only to approved users or applications needing temporary access.
- SAS tokens expire and are refreshed at least every 6 months to enhance security.
- Expiration policies and token rotation are enforced to prevent misuse.
sp=racwl: Permissions Read, Add, Create, Write, and List (Delete actions are not allowed)
9️⃣ Log & Monitoring Strategy
Logging Assumptions
- SQL Server & Storage Account log actions automatically within their own environments.
- myBMT maintains an internal login record for tracking user authentication.
Data Access Logging
| System | Log Destination | Details Logged |
|---|---|---|
| myBMT Application | Internal Login Record | Tracks authentication and user sessions. |
| get.myview Function | log.trans_mylog (SQL) | Logs access attempts for audit and reporting. |
| myBMT Container View (action=shower) | WordPress mylog | Logs access attempts to front-end views. |
Reporting on Usage
- The
log.trans_mylogtable forms the foundation for Report Usage Analysis in Power BI. - Reports track access frequency, failed attempts, and resource interaction trends.
🔟 Policy for Revoking Expired Users
- Users will be revoked from myBMT upon notification of access changes.
- Users are reviewed for inactivity and revoked after 3 months of inactivity.
- SQL Server users are applied via role groups, meaning:
- Group IT must be notified if a user requires withdrawal from a role group.
- The same role groups are applied to Power BI Workspaces, ensuring consistent access management across systems.