Auditing – Administrative User Accounts that have permissions to deploy a package to a resource

--What admins have the right to initiate a package install on a resource
select a.LogonName from RBAC_Admins a
-- Does the admin have read and deploy package permission to at least one collection
where a.AdminID in (select top 1 AdminID from RBAC_InstancePermissions where AdminID = a.AdminID and ObjectTypeID = 1 and (GrantedOperations & 0x00004001) = 0x00004001)
-- and
and
(
 a.AdminID in
 (
  -- does the admin
  select top 1 dp.AdminID from RBAC_InstancePermissions dp
  -- have read permissions to at least one DP
  inner join v_PackageStatusDistPointsSumm pdp on pdp.ServerNALPath = dp.ObjectKey and (dp.GrantedOperations & 0x00000001 = 0x00000001) and dp.ObjectTypeID = 42
  -- and read permissions to a package that is on that DP
  inner join RBAC_InstancePermissions p on p.ObjectKey = pdp.PackageID and dp.AdminID = p.AdminID and (p.GrantedOperations & 0x00000001 = 0x00000001) and p.ObjectTypeID = 2
  where dp.AdminID = a.AdminID
 )
 or
 a.AdminID in
 (
  -- does the admin
  select top 1 dp.AdminID from RBAC_InstancePermissions dp
  -- have read permissions to at least one DPG
  inner join v_DPGroupContentDetails pdp on pdp.GroupID = dp.ObjectKey and (dp.GrantedOperations & 0x00000001 = 0x00000001) and dp.ObjectTypeID = 43
  -- and read permissions to a package that is on that DPG
  inner join RBAC_InstancePermissions p on p.ObjectKey = pdp.PkgID and dp.AdminID = p.AdminID and (p.GrantedOperations & 0x00000001 = 0x00000001) and p.ObjectTypeID = 2
  where dp.AdminID = a.AdminID
 )
 or
 a.AdminID in
 (
  -- does the admin
  select top 1 dp.AdminID from RBAC_InstancePermissions dp
  -- have read and copy to DP permissions to at least one DP
  inner join v_PackageStatusDistPointsSumm pdp on pdp.ServerNALPath = dp.ObjectKey and (dp.GrantedOperations & 0x00000009 = 0x00000009) and dp.ObjectTypeID = 42
  -- and create package permissions
  inner join v_CategoryPermissions p on dp.AdminID = p.AdminID and (p.GrantedOperations & 0x00000401 = 0x00000401) and p.ObjectTypeID = 2
  where dp.AdminID = a.AdminID
 )
 or
 a.AdminID in
 (
  -- does the admin
  select top 1 dp.AdminID from RBAC_InstancePermissions dp
  -- have read and copy to DP permissions to at least one DPG
  inner join v_DPGroupContentDetails pdp on pdp.GroupID = dp.ObjectKey and (dp.GrantedOperations & 0x00000009 = 0x00000009) and dp.ObjectTypeID = 43
  -- and create package permissions
  inner join v_CategoryPermissions p on dp.AdminID = p.AdminID and (p.GrantedOperations & 0x00000401 = 0x00000401) and p.ObjectTypeID = 2
  where dp.AdminID = a.AdminID
 )
 or
 a.AdminID in
 (
  -- does the admin have rights to all DPs and/or DPGs and packages
  select top 1 dp.AdminID from v_CategoryPermissions dp
  inner join v_CategoryPermissions p on p.AdminID = dp.AdminID and dp.ObjectTypeID in (42, 43) and dp.CategoryID = 'SMS00ALL' and p.ObjectTypeID = 2 and p.CategoryID = 'SMS00ALL'
  where dp.AdminID = a.AdminID
 )
)
order by 1
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s