The Base View Where Clause must use proper SQL syntax. Therefore, only administrators who are familiar with SQL should use this feature. This section provides four examples of Row Set Security.

Example 1: Display Records Created in the Last 30 Days

An administrator has created an entity for an organization that includes a DateCreated field (using the DateCreated base field). The organization's policy is that users should only have access to the records in this entity that were created in the last 30 days.

To satisfy this requirement, the administrator enters the following statement in the Base View Where Clause field for the entity:

DateCreated > GETDATE()-30  

[Where GETDATE() retrieves the current date and time]

Then, the administrator clicks a Save button, and Aptify regenerates the entity's base view so that it takes the following form:

CREATE VIEW vwSampleServices 
AS
SELECT 
ss.*
FROM 
SampleService ss WHERE (-DateCreated > GETDATE()-30) 

As a result of this change, all users will be unable to access any record in this entity that was created more than 30 days in the past.

Example 2: Limit Access to Only Records Created by the Current User

An administrator has created an entity for an organization that includes a WhoCreated field (using the WhoCreated base field). The organization's policy is that each user should only have access to the records in this entity that he/she created.

To satisfy this requirement, the administrator enters the following statement in the Base View Where Clause field for the entity:

WhoCreated = SUSER_SNAME())

[Where SUSER_SNAME() retrieves the name of the current user]

Then, the administrator clicks a Save button, and Aptify regenerates the entity's base view so that it takes the following form:

CREATE VIEW vwSampleServices
AS
SELECT 
ss.*
FROM SampleService ss WHERE (WhoCreated = SUSER_SNAME())

As a result of this change, each user can only access the records in this entity that he/she created.

Example 3: Limit Access to Only Records Created by the Current User But -Provide Administrators with Access to All Records

An administrator has created an entity for an organization that includes a WhoCreated field (using the WhoCreated base field). The organization's policy is that each user should only have access to the records in this entity that he/she created.

Also, members of the Administrators group should be able to view all records in the entity.

To satisfy this requirement, the administrator enters the statement below in the Base View Where Clause field for the entity. Note that this scenario is very similar to Example 2 and uses the same Where Clause Base View as in Example 2 but includes a new "OR" statement to support administrator access:

WhoCreated = SUSER_SNAME()
OR SUSER_SNAME() IN 
(SELECT u.UserID FROM APTIFY.dbo.vwUsers u
INNER JOIN APTIFY.dbo.vwGroupMembers gm ON gm.UserID=u.ID
INNER JOIN APTIFY.dbo.vwGroups g ON g.ID=gm.GroupID
WHERE g.Name = 'Administrators' ) 

[Where SUSER_SNAME() retrieves the name of the current user]

Then, the administrator clicks a Save button, and Aptify regenerates the entity's base view so that it takes the following form:

CREATE VIEW vwSampleServices
AS
SELECT 
ss.*
FROM 
SampleService ss WHERE (WhoCreated = SUSER_SNAME()
OR SUSER_SNAME() IN 
(SELECT u.UserID FROM APTIFY.dbo.vwUsers u
INNER JOIN APTIFY.dbo.vwGroupMembers gm ON gm.UserID=u.ID
INNER JOIN APTIFY.dbo.vwGroups g ON g.ID=gm.GroupID
WHERE g.Name = 'Administrators' ))

As a result of this change, each user can only access the records in this entity that he/she created, and members of the Administrators group can access all records in the entity.

Example 4: Limiting Access to Person Records and Company Records to the Main Account Manager

An organization's policy is that users should only have access to the Companies and associated Persons for which they are the Main Account Manager. This type of scenario requires the addition of Row Set Security logic to two entities: Companies and Persons. Note that Companies and Persons have separate Main Account Manager fields.

This example uses the Companies entity's Main Account Manager value; it assumes that users should only see the Persons that are associated with the Companies for which the user is the Main Account Manager.

To fulfill these requirements, the administrator enters the following statement in the Base View Where Clause field for the Companies entity:

c2.MainAccountManagerID IN
(SELECT MainAccountManagerID FROM APTIFY.dbo.Company c 
INNER JOIN APTIFY.dbo.Employee e ON -e.ID=c.MainAccountManagerID
INNER JOIN APTIFY.dbo.vwUserEntityRelations uer ON e.ID=uer.EntityRecordID 
INNER JOIN APTIFY.dbo.vwUsers u ON u.ID=uer.UserID 
WHERE u.UserID=SUSER_SNAME() AND 
-uer.EntityID_Name='Employees' )

This clause specifies that the base view contain the Companies records for which the current user is the Main Account Manager.

Note that the c2 alias preceding MainAccountManagerID in the first line is required because the base view aliases the Company table. Therefore, you should always review the entity's Base View prior to saving a Row Set Security record to ensure that your WHERE clause matches the syntax of the base view to which it will be appended

The administrator then enters the following statement as a Row Set Security Base View Where Clause field for the Persons entity:

p.CompanyID IN
(SELECT CompanyID FROM APTIFY.dbo.Person p
INNER JOIN APTIFY.dbo.Company c ON p.CompanyID=c.ID
INNER JOIN APTIFY.dbo.Employee e ON -e.ID=c.MainAccountManagerID
INNER JOIN APTIFY.dbo.vwUserEntityRelations uer ON e.ID=uer.EntityRecordID 
INNER JOIN APTIFY.dbo.vwUsers u ON u.ID=uer.UserID 
WHERE u.UserID=SUSER_SNAME() AND -uer.EntityID_Name='Employees' )

This clause specifies that the base view return only the Persons records for which the current user is the Main Account Manager for the Person's specified company. Note that the Persons base view aliases the Person table so the p.CompanyID in the first line is also aliased.

Note that this rule requires that each user be linked to an Employee record (specified in Step 7 of the User Administration Wizard). Also, to prevent users from creating Person records without specifying a Company, an administrator should make Company a required field within the Persons entity. Likewise, to prevent users from creating Company records without specifying a Main Account Manager, an administrator should make Main Account Manager a required field within the Companies entity.