Next we tackle the Permissions information contained in the
DNN database. Perhaps you need a quick way to view what roles utilize the
different options on a particular portal. To do this we look at the following
tables:
·
TabPermission
·
Roles
·
Tabs
·
Portals
·
Permission
·
UserRoles
·
Users
To make our SQL statement to use, we need to join all these
tables together. See Listing 2 for the exact code.
Listing 2
Create View vwpermissions_summary
as
select tp.tabpermissionid
, tp.AllowAccess
, tp.PermissionID
, per.PermissionName
, ro.RoleName
, tb.TabName
, po.PortalName
, us.FirstName
, us.LastName
from dbo.TabPermission tp
inner join dbo.Roles ro on (ro.RoleID = tp.roleid)
inner join dbo.Tabs tb on (tb.TabID = tp.TabID)
inner join dbo.Portals po on (po.PortalID = tb.PortalID)
inner join dbo.Permission per on (per.PermissionID = tp.PermissionID)
inner join dbo.UserRoles usr on (ro.RoleID =usr.RoleID)
inner join dbo.Users us on (us.UserID = usr.UserID)
In order to make changing underlying fields easier, we have
created a view. This allows us to add and subtract fields; then by using Verify
Database we can get those fields synched with our report.
We get the Names of users that have specific permissions
from the Users table, linked via UserRoles, then the role table and the tab
table. The portal table is related.also. The table we start from is called
tabpermission. This table includes links back to the tab, to the role and even
to Users if they have specific permissions. But we need to "snake"
our way back via the role table, User Roles, then to Users to get the first and
last name of the actual users.
There is also a permissions table that includes the names of
the permissions (View Tab, Edit Tab). This helps us view what the different
roles can actually do on the different tabs.
