Managing Row Set Security

In addition to Entity Security, Field Level Security, and Field Level Encryption, administrators can also prevent users from accessing specific data records within a service or entity. This is known as Row Set Security (since each record is considered a row within the database).

For example, an administrator can define a rule within an entity so that a user can only see the records that he/she created. For example, an organization can configure the Persons entity to allow a user to see only those records for which the user is the Main Account Manager. This rule would prevent users from accessing Person records managed by other employees.

Aptify implements Row Set Security by specifying a rule that filters out records from an entity's base view. The administrator enters the rule into the Entities record in the form of a SQL WHERE clause. Aptify appends the WHERE clause to the end of the Base View command.

Caution

Implementing Row Set Security may have a negative impact on system performance. Aptify recommends that only administrators who are familiar with SQL should use this feature. For best results, use the minimum number of Row Set Security statements required by your organization. Also, review the Aptify Performance Tuning white paper for suggestions on how to improve overall system performance (the white paper is available from Aptify Support). 

This section covers the following topics:

• Enabling Row Set Security
• Sample SQL Where Clauses
• Determining Whether a Base View is Generated
• Disabling Row Set Security
• About the Row Set Security Effects

Enabling Row Set Security

Follow these steps to enable Row Set Security for a particular entity:

1. Open or create a view of the Entities service that includes the entity you want to configure.
2. Double-click the entity's entry in the view to open the entity's record.
3. Confirm that the entity's base view is automatically generated by Aptify.
   • Row Set Security applies only to entities whose base view is generated automatically by Aptify. For entities that have a non-generated base view, the system administrator must apply the row set security logic manually. See Determining Whether a Base View is Generated for information on how to identify generated and non-generated base views.
   • You should review the base view to determine the proper syntax for your WHERE clause. For example, if the entity's table is aliased in the base view, your WHERE clause field also needs to be aliased. See Example 4: Limiting Access to Person Records and Company Records to the Main Account Manager for a WHERE clause that needs to include an alias. 
4. Click the Row Set Security tab.
5. Open a new Row Set Security sub-type record.
   • Either click the New Record... button in the menu bar, or right-click in the gray area and select New from pop-up menu to open a new record. 
6. Enter the rule in the Base View Where Clause field.
   • Row Set Security rules must use proper SQL syntax. Therefore, only administrators who are familiar with SQL should use this feature.
   • See Sample SQL Where Clauses for examples.
7. Enter a description for the rule.  
   
   
8. Click OK to save the rule.
9. Enter additional rules for the same entity, if necessary. Aptify uses an AND operator to separate multiple Base View Where Clauses within a generated base view.
   
   • Alternatively, you can Click OK and New in Step 8 to save the current and open a new Row Set Security record in one step. 
10. Save and close the Entities record.
11. Regenerate the base view for any related entities that join to the entity for which you just enabled row set security.
    • By default, when generating a base view for an entity, the system automatically references the base table rather than the base view for joined entities whenever possible for optimization purposes. However, when an entity's base view references a joined entity that Row Set Security enabled, then the entity's base view joins to the related entity's base view and not its base table (to ensure that row set security restrictions are respected across entities). Therefore, after enabling Row Set Security for one entity, you need to regenerate the base view for any related entities that joins to it to ensure that the base views are properly updated.
    • For example, an organization has defined row set security for the Persons entity so users can only see persons assigned to their particular organization. However, the base view for the Certifications entity in the Education Management add-on application references the Persons entity's base table rather than its base view for optimization purposes by default. Therefore, after enabling Row Set Security for the Persons entity, you need to regenerate the Certifications entity, so its base view references the vwPersons base view rather than the Person base table. 
12. Create views of the entity from multiple users to confirm that the Row Set Security logic operates as expected.

Sample SQL Where Clauses

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.

Important Note

The following examples are for illustration purposes only; they demonstrate how Row Set Security modifies an entity's Base View. These examples are may not be suitable for real-world implementation. Also, note that complex Row Set Security statements may have a negative impact on system performance. 

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

Important Note

The following example is for illustration purposes only; it demonstrates how to create complimentary Row Set Security rules for two entities. This example may not be suitable for real-world implementation since no user will have access to all Persons or Companies records.  

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.

Determining Whether a Base View is Generated

The Row Set Security configuration option applies only to entities whose base view is generated automatically by Aptify. For entities that have a non-generated view, the system administrator can add row set security logic by manually appending a WHERE clause to the end of the entity's Base View.

Entities that use generated base views include:

• Persons
• Companies
• Employees
• Orders
• Payments

Follow these steps to determine whether or not an entity uses a generated base view:

1. Open or create a view of the Entities service that includes the entity whose settings you want to review.
2. Double-click the entity's entry in the view to open the entity's record.
3. Click the Base View link to open the entity's Base View record.
   
   
4. Locate the DB field.
   • If the base view is automatically generated, SQL is Generated appears to the right of the DB field.
   • If the base view is non-generated, SQL is Generated does not appear to the right of the DB field.
     
     

Disabling Row Set Security

Follow these steps to disable Row Set Security for a particular entity:

1. Open or create a view of the Entities service that includes the entity you want to configure.
2. Double-click the entity's entry in the view to open the entity's record.
3. Click the Row Set Security tab.
4. Right-click the rule you want to remove and select Delete from the drop-down menu.
5. Click Save and Close.

About the Row Set Security Effects

Row Set Security modifies an entity's base view within the SQL database. Therefore, the following results occur when the feature is enabled for a particular entity:

• Users can only view the Entity records defined by the rule.
• Users can only search the Entity records defined by the rule.
• Advanced users cannot bypass the Aptify security measures to gain access to restricted records using another database tool, such as SQL Server Query Analyzer or Microsoft Access.