When setting privileges at the model level, it is often interesting to have a global picture of which roles are allowed to manage / edit / create/update, or export the data as part of the governance process.
All details are available at design time in the Application Builder and are displayed per role. The goal here is to be able to query that information in the repository, to make it available as SQL to be displayed in a dashboard or any other tool.
1. Understand privileges tables in the repository
There a 3 main tables to take into account when looking at model privileges grants, and the names are self-explanatory: Model > Entity > Attribute.
mta_model_priv_grant- Contains role name and specific grants such as full access to the model, REST APIs, and Model documentation
mta_ent_priv_grant- Contains entity level privileges such as default grant, create, checkout, export, removal, delete, and filters
mta_attr_priv_grant- Contains attribute level privileges read/write/none
2. Build a SQL query to list entity level privileges (multi DB)
This query returns all necessary details at the entity level, ready to use easily in dashboards. It contains a few joins on other repository tables to provide context on models and entities and to limit the audit to the latest model edition on the root branch (which will work for both development and production environments).
The key element is that you may have multiple entity privileges for a single model entity. This is usually done to specify different access privileges with different filters depending on the data.
To simplify the access audit we refer to the basic entity by default and only look at attribute level privileges grants to check if they are not using the default grant (i.e. partial access). You can implement the same logic to go one level deeper at the attribute level if needed.
select
m.label as ""Model"",
e.label as ""Entity"",
case when ep.uuid is null or ep.name = e.name then e.label else concat(concat(e.label, ' - '), ep.name) end as ""Entity Privilege"",
mp.label as ""Role"",
case when mp.is_data_admin = '1' then 'READ_WRITE'
when ep.uuid is null then 'DENIED'
when exists (select 1 from semarchy_repository.mta_attr_priv_grant ap where ap.o_ent_priv = ep.uuid and ap.branchid = 0 and ap.editionid = e.editionid) then 'PARTIAL'
else ep.default_access_type end as ""Access Type"",
case when ep.filter is not null then 'FILTERED' else 'ALL DATA' end as ""Filter"",
case when mp.is_data_admin = '1' or ep.is_checkout_allowed = '1' then 'YES' else 'NO' end as ""Checkout"",
case when mp.is_data_admin = '1' or ep.is_create_allowed = '1' then 'YES' else 'NO' end as ""Create"",
case when mp.is_data_admin = '1' or ep.is_delete_allowed = '1' then 'YES' else 'NO' end as ""Delete"",
case when mp.is_data_admin = '1' or ep.is_export_allowed = '1' then 'YES' else 'NO' end as ""Export""
from semarchy_repository.mta_root_model m
join semarchy_repository.mta_entity e on e.o_model = m.uuid and e.branchid = 0 and e.editionid = (select max(me.editionid) from semarchy_repository.mta_model_branch mb, semarchy_repository.mta_model_edition me where mb.o_rootmodel = m.uuid and mb.branchid = 0 and me.o_modelbranch = mb.uuid)
join semarchy_repository.mta_model_priv_grant mp on mp.o_model = m.uuid and mp.branchid = 0 and mp.editionid = e.editionid
left join semarchy_repository.mta_ent_priv_grant ep on ep.r_entity = e.uuid and ep.o_model_priv = mp.uuid and ep.branchid = 0 and ep.editionid = e.editionid
order by m.name, e.name, mp.name, ep.name, mp.label;3. Use xDM Dashboard to audit privileges
The query will allow building any sort of dashboard, to audit privileges for roles and entities defined in each model.
Here is a basic example of a pivot table with slicers, showing the access per entity and role.
Drill-down on Entity (also available on Entity x Role)
4. Alternative display using Excel
In case you need to produce an audit document you may use a tool of your choice and plug it into the SQL data.
Here is a nice example using Excel.
