Content access changes

Release 1.0 - ...

Since the inception of the content access model some important changes have taken place.

AccessTypes and AccessTypeLevels have both an identity field Nr (in order to be editable with the SixSmartsite DataEditor framework) and a unique Name field that is used to reference types and levels from SXML:

SQL	Copy Code
CREATE TABLE dbo.AccessTypes( nr int IDENTITY(1,1) NOT NULL, Name varchar(50) NOT NULL, Description varchar(1024) NULL, CONSTRAINT PK_AccessTypes PRIMARY KEY CLUSTERED (nr ASC) , CONSTRAINT UN_AccessTypeName UNIQUE NONCLUSTERED (Name ASC) )

SQL

Copy Code

 CREATE TABLE dbo.AccessTypes(
  nr int IDENTITY(1,1) NOT NULL,
  Name varchar(50) NOT NULL,
  Description varchar(1024) NULL,
  CONSTRAINT PK_AccessTypes PRIMARY KEY CLUSTERED (nr ASC) ,
  CONSTRAINT UN_AccessTypeName UNIQUE NONCLUSTERED (Name ASC) 
 )

SQL	Copy Code
CREATE TABLE AccessTypeLevels( Nr int IDENTITY(1,1) NOT NULL, AccessType int NOT NULL, AccessLevel smallint NOT NULL, Name varchar(50) NOT NULL, Description varchar(2000) NULL, CONSTRAINT PK_AccessTypeLevels PRIMARY KEY CLUSTERED (AccessType ASC, AccessLevel ASC), CONSTRAINT UN_AccessTypeLevelName UNIQUE NONCLUSTERED (Name ASC) )

SQL

Copy Code

CREATE TABLE AccessTypeLevels(
  Nr int IDENTITY(1,1) NOT NULL,
  AccessType int NOT NULL,
  AccessLevel smallint NOT NULL,
  Name varchar(50) NOT NULL,
  Description varchar(2000) NULL,
  CONSTRAINT PK_AccessTypeLevels PRIMARY KEY CLUSTERED (AccessType ASC, AccessLevel ASC),
  CONSTRAINT UN_AccessTypeLevelName UNIQUE NONCLUSTERED (Name ASC)
 )

Making these changes in an existing site is best done using SQL Management Studio, as it automatically fills the identity column for existing records.

The vwContentAccess view takes visitors into account as wel as users. This was an oversight in the original version:

SQL	Copy Code
CREATE VIEW [dbo].[vwContentAccess] AS SELECT ugca.ContentNumber, ugur.nrUserRole AS RoleNumber, ugu.UserNumber AS UserId, ugca.AccessType, ugca.AccessLevel FROM dbo.UserGroupsUserRoles AS ugur INNER JOIN ( SELECT UserNumber, GroupNumber FROM dbo.UserGroupUsers UNION ALL SELECT nrVisitors as UserNumber, nrUserGroups AS GroupNumber FROM dbo.UserGroupsVisitors) AS ugu ON ugur.nrUserGroup = ugu.GroupNumber INNER JOIN dbo.UserGroupContentAccess AS ugca ON ugu.GroupNumber = ugca.GroupNumber INNER JOIN dbo.Contents AS c ON ugca.ContentNumber = c.Nr AND c.Status <> 2 UNION ALL SELECT uca.ContentNumber, ugur.nrUserRole AS RoleNumber, uca.UserNumber AS UserId, uca.AccessType, uca.AccessLevel FROM dbo.UserGroupsUserRoles AS ugur INNER JOIN ( SELECT UserNumber, GroupNumber FROM dbo.UserGroupUsers UNION ALL SELECT nrVisitors AS UserNumber, nrUserGroups AS GroupNumber FROM dbo.UserGroupsVisitors) AS ugu ON ugur.nrUserGroup = ugu.GroupNumber INNER JOIN dbo.UserContentAccess AS uca ON ugu.UserNumber = uca.UserNumber INNER JOIN dbo.Contents AS c ON uca.ContentNumber = c.Nr AND c.Status <> 2

SQL

Copy Code

CREATE VIEW [dbo].[vwContentAccess]
AS
          SELECT ugca.ContentNumber, ugur.nrUserRole AS RoleNumber, ugu.UserNumber AS UserId,
                 ugca.AccessType, ugca.AccessLevel
          FROM   dbo.UserGroupsUserRoles AS ugur
                 INNER JOIN (
                     SELECT UserNumber, GroupNumber FROM dbo.UserGroupUsers 
                     UNION ALL
                     SELECT nrVisitors as UserNumber, nrUserGroups AS GroupNumber FROM dbo.UserGroupsVisitors)
                 AS ugu ON ugur.nrUserGroup = ugu.GroupNumber
                 INNER JOIN dbo.UserGroupContentAccess AS ugca ON ugu.GroupNumber = ugca.GroupNumber
                 INNER JOIN dbo.Contents AS c ON ugca.ContentNumber = c.Nr AND c.Status <> 2
          UNION ALL
          SELECT uca.ContentNumber, ugur.nrUserRole AS RoleNumber, uca.UserNumber AS UserId,
                 uca.AccessType, uca.AccessLevel
          FROM   dbo.UserGroupsUserRoles AS ugur
                 INNER JOIN (
                     SELECT UserNumber, GroupNumber FROM dbo.UserGroupUsers 
                     UNION ALL
                     SELECT nrVisitors AS UserNumber, nrUserGroups AS GroupNumber FROM dbo.UserGroupsVisitors)
                 AS ugu ON ugur.nrUserGroup = ugu.GroupNumber
                 INNER JOIN dbo.UserContentAccess AS uca ON ugu.UserNumber = uca.UserNumber
                 INNER JOIN dbo.Contents AS c ON uca.ContentNumber = c.Nr AND c.Status <> 2

Advanced