4. Users and permissions¶
4.1. Overview¶
Understand users, user groups, logins, and access control in SQL. Design integrations that respect permission boundaries, and analyze user group membership—including nested groups—directly from SQL.
4.2. Users in user groups¶
The Connector includes functionality to extract a listing of users by user group. A new column VaultRoles is updated in the MFUserAccount table when spMFDropAndUpdateMetadata is run. This column shows which users have full control, default roles or special roles in the vault.
Running the procedure spmfUsersByUsergroup will create a global temporary table ##spMFUsersByUserGroup to list the users by usergroup. This listing will also include the users for one level deep of usergroups within usergroups.
Step 1: Execute spMFDropandUpdateMetadata (only necessary if changes have taken place to users or usergroups)
Step 2: Execute spmfUsersByUsergroup
exec spmfUsersByUsergroup
Step 3: Use views on the table ##spMFUsersByUserGroup to analyze the results
select * from ##spMFUsersByUserGroup
In the following example the script shows the user groups that Andy is a member of, including being a member of the ContextMenu user group by virtue of being a member of the Managers user group
select * from ##spMFUsersByUserGroup where userName = 'AndyN'
On the other hand, selecting the users by user group shows all the users for ContextMenu, including the usergroups in ContextMenu
select * from ##spMFUsersByUserGroup where userGRoup = 'Managers'
The following query will show all the users in all the user groups excluding the generic user groups (all internal users) that have been disabled and does not have a license.
select ug.*,mla.Enabled, mla.LicenseType from ##spMFUsersByUserGroup ug
inner join dbo.MFLoginAccount as mla
on mla.MFID = ug.userid
where usergroupid not in (1,2)
and (mla.Enabled = 0 or mla.LicenseType = 'MFLicenseTypeNone')