Master Data Services provides data governance and reference data management as part of SQL Server product. There is a security mechanism in MDS that provides robust role based security and authorization for domain accounts and groups. Security on data also can be applied on Model level, entity, or even in row level. Security in MDS can be applied through WebUI’s administrative tasks under User and Group Permissions.
Unfortunately the UI for Permission and security configuration is not well designed, and sometimes you face issues. for example when only a specific user account has priviledge on a specified model in MDS, and that user account removed from the active directory for any reason, then you have no way to get into the permission setting of that model and authorize another user for that. This will be serious problem especially in production environments.
I faced similar issue and tried to find a solution for that. Fortunately the MDS internal database structure is very powerful and coveres all of these exceptions, so the only way to solve those permission issues is to change permission and security configuration in the MDS database directly. as a result in this post I reveal security schema of master data services and show you structure of tables, views, and stored procedures. This detailed information about MDS database helps you in situation that the Security configuration in WebUI stucks, and you have no other way to set the security other that get into the database.
Active directory users and groups can be used in membership mechanism of MDS, here you see how membership tables structured;
Two main tables that I want to mention from schema above are tblUser and tblUserGroup, these two table contains data for active directory users and active directory groups. the importance of these two tables is because the ID of these tables will be used as Princicipal_ID in security schema tables. I will go through that in very next paragraphs.
There are also some tables that contains metadata information of objects in MDS such as Models, Entities, and Hierarchies. picture below shows these three tables:
Obviously there are more metadata tables in MDS that has data of attributes, business rules and etc. but simplicity I just mentioned three above tables. as you can see each table has the object properties with the ID column. the ID value of each object will be used in security schema tables.
Security Tables
Now it’s time to go through Security schema model; picture below shows how security tables structured and related to each other;
For detailed discussion it would be better to go through each table’s definition and view sample data rows.
tblSecurityPrivilege
This table contains privileges that can be applied on objects, and members. here you can see data rows for this table:
tblSecurityObject
Objects categorized for ability to apply security on different levels, object can be model, entity, hierarchy and etc. data rows for this table showed below:
tblSecurityRole
This table stores roles defined in the WebUI, each role can have different properties, Roles usually named based on the user account or group that this role assigned to it for the first time such as Role for UserAccount … or Role for Group …. here you can see sample records of this table;
tblSecurityAccessControl
This table defines relationship between Roles and Principals. Principal can come from tblUser or tblUserGroup, based on the fact that role assigned to user account or group account. there is also another property named PrincipalType_ID, this column would have 1 value for user account, or 2 value for group account. in below you can see sample records of this table
tblSecurityRoleAccess
This table stores information of which role has which privilege to which object. there are some important columns in this table; Role_ID is foreign key related to tblSecurityRole, Privilege_ID provides relationship to tblSecurityPrivilege, Object_ID defines type of object from tblSecurityObject, Model_ID defines which Model we want to set access for (model ID exists in tblModel), Securable_ID defines the ID of object that we want to set permission for it. for example if the object is an entity the Object_ID will be 3, and Securable_ID will be ID of that entity from tblEntity. picture below shows sample data rows for this table; (Please note that all columns are not listed in picture below, just those that mentioned for relationship highlighted)
tblSecurityRoleAccessMember
This table stores the most detailed level security defined in the master data services. in this table the information about security on Member level will be stored. there are columns for Role and Privilege for defining the role and access privilege, Entity_ID and Version_ID is to address specific entity in specific version. there are also other columns for hierarhcy, Item_ID, Member_ID. picture below shows columns of this table that participates in relationship with other tables
So as you’ve seen these few number of tables provide the security for whole master data services. so if you want to set a permission, and for any reason (Such as the example that I’ve mentioned) you cannot do that through WebUI, don’t worry, it is easy to be familiar with this structure and then you can insert data into these tables, or update them as you want. there are also stored procedures for setting these values that you can simply find them. I didn’t mentioned stored procedures one by one, because when you learned the structure of tables, you can understand that what task each stored procedure does.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.