Reporting on privilege assignment

[#1]

An Access SQL query I use to help report and diagnose access privilege issues. Assumes an ODBC connection to your SQL VCDB called VCDB.

The inner joins are to compensate for the fact that read-only is a reserved role with no VPX_ROLE record and no VPX_PRIV_ROLE entries.

SELECT dbo_VPX_ACCESS.PRINCIPAL, dbo_VPX_OBJECT_TYPE.NAME, dbo_VPX_ENTITY.NAME, dbo_VPX_ROLE.NAME, dbo_VPX_PRIV_ROLE.PRIV_NAME, d bo_VPX_ACCESS.FLAG

FROM ((dbo_VPX_ACCESS INNER JOIN dbo_VPX_ENTITY ON dbo_VPX_ACCESS.ENTITY_ID = dbo_VPX_ENTITY.ID) LEFT JOIN (dbo_VPX_PRIV_ROLE LEFT JOIN dbo_VPX_ROLE ON dbo_VPX_PRIV_ROLE.ROLE_ID = dbo_VPX_ROLE.ID) ON dbo_VPX_ACCESS.ROLE_ID = dbo_VPX_PRIV_ROLE.ROLE_ID) INNER JOIN dbo_VPX_OBJECT_TYPE ON dbo_VPX_ENTITY.TYPE_ID = dbo_VPX_OBJECT_TYPE.ID

WHERE (((dbo_VPX_ACCESS.PRINCIPAL)="INTERNAL\test.vm.admins"))

ORDER BY dbo_VPX_ACCESS.PRINCIPAL, dbo_VPX_OBJECT_TYPE.NAME, dbo_VPX_ENTITY.NAME, dbo_VPX_ROLE.NAME, dbo_VPX_PRIV_ROLE.PRIV_NAME;

[yorkie]

Hey Guy,

I wish there was a non-SQL way of doing this (like through the API) but I guess sometimes we have to dig deep... I always worry that this little gems get broken if and when VC DB schema changes, as I'm sure you do.

We are expanding the security community in a number of ways (have you seen the security technical resource center at vmware.com/security?) - and a new extension being piloted now (want to join) is VIOPS - Virtual Infrastructure Operations.

This portal has a different, complementary use compared to VMTN because it is focused at being a repository of proven practices, and also looking beyond technical nuggets of gold like this into the people and process issues in the enterprise.

Check out http://vpp-dev-1.vmware.com. As I say, its a pilot and we are looking for community leaders to jump in and shape it into something they want and find useful.

Look forward to seeing you over there.

Cheers

Steve