AppFrame Security

AppFrame security is built on top of SQL Server’s security model. In order to fully understand AppFrame security you therefore need to have in-depth knowledge to how SQL Server security works.

By utilizing the SQL Server security model, we can benefit from all its built-in security functions. In addition AppFrame supports integrated Windows security, which will allow for even more advanced security settings.

When designing the AppFrame security model, one of the most important features that was required was the ability to define permissions on row level. Since this is not a part of SQL Server security concept, we have implemented this based on utilizing triggers and views. All AppFrame based solutions are defining the security at the database level. That means that if you access or alter your data from a web application, CR3 ClickOnce , a MS Access database or SQL Server Management Studio (2005) or Query Analyzer (2000), or whatever type of client, there will be no difference in what data you will be able to see or alter.

Login, users and roles

An AppFrame user needs to have a login on the SQL Server, and a user in the database which is member of the af_user role. The login can be mapped to a windows login to allow for integrated windows security.

The af_user role is an AppFrame-defined role, which has the following permissions:

Select, Insert, Update, Delete on views
Select on Primkey and Timestamp columns in tables
Insert, update and delete on tables
Execute on functions and stored procedures

The stored procedures sstp_system_ApplyPermisisons and sstp_ApplyPermissionsAll ensures that all database objects get the required access.

A job that executes these stored procedures should be defined during the setup of the AppFrame environment. If not, users which are not member of the db_owner role will not be able to retrieve data or execute procedures or functions. As a db_owner you may grant permissions manually to a view as well (e.g. in case sstp_ApplyPermissions has failed or is temporary in-active).

E.g. to grant access to a view, run the following statement: “GRANT SELECT ON [viewname] to af_user”

So, the AppFrame User have both insert, update and delete permissions on tables. How do we then restrict the user from altering data? The answer is the triggers. When defining tables in AppFrame from CR3 DB Objects you also create the Insert, Update and Delete Triggers with AppFrame Security.

Types of access

By using AppFrame’s security concept you may define access to tables at row level. The following access types can be granted to the records:

SUID: Select, Update, Insert and Delete. That means that the user will be able to read, modify, delete and add records that satisfy criteria defined in “Row level security”. For example: If a user has SUID permissions to the table atbl_Projbaseline_WorkPacks the user will be able to read, modify, or delete records in the domain.
SUI: Select, Update and Insert. Same as above, except that the user will not be able to delete any data.
SU: Select and Update.
S: Select – read-only access.

Restricting access to view data

The standard user will not be able to select data from tables directly. Data can only be accessed from views, stored procedures or functions.

atbv-viewsEvery table should have a corresponding view, prefixed with atbv instead of atbl. For example, the table atbl_PC_Projbaseline_WBS will have a view named: atbv_PC_Projbaseline_WBS.This view should return all records that the user have access to, in his current domain.

The view will look like this:

SELECT

FROM atbl_PC_ProjBaseline_WBS WITH (NOLOCK)

WHERE EXISTS (SELECT *

FROM sviw_System_MyPermissionsCurrentDomain WITH (NOLOCK)

WHERE Domain = atbl_PC_ProjBaseline_WBS.Domain

AND TableID = 'atbl_PC_ProjBaseline_WBS')

The view sviw_System_MyPermissionsCurrentDomain is very central, and you will find it utilized in all standard atbv-views. The view returns all the tables for the current user that he have access to in his current domain.

Accessing data independent of current domain

By utilizing these atbv-views in your application you make sure that the end users only sees the data in his current domain. However, sometimes it is relevant to show all the data that the user have access to, regardless of his or hers current domain. To accomplish this, you may generate an atbx-view (using the CR3 Database Objects form).

An atbx view returns all records that the user have access to.

Criteria fields

It is possible to control access to data on a more detailed level than Domain. Every table has a Criteria1 and Criteria2 field that you can use. Say that you want to only give read access to certain WBS codes in the above table. You would then select "WBS" as criteria1 field. After you have modified, added or removed a criteria field, remember to Recreate views and triggers.After you have chosen WBS as your criteria1 field, and recreated your views and triggers, the atbx-view will look like this:

SELECT

FROM atbl_PC_Projbaseline_WBS WITH (NOLOCK)

WHERE EXISTS (SELECT *

FROM sviw_System_MyPermissionsCurrentDomain WITH (NOLOCK)

WHERE Domain = atbl_PC_Projbaseline_WBS.Domain

AND TableID = 'atbl_PC_Projbaseline_WBS'

AND atbl_PC_Projbaseline_WBS.WBS LIKE sviw_System_MyPermissionsCurrentDomain.Criteria1)

By adding WBS as a criteria field, the last AND criteria was added. In this case WBS has to match Criteria1 field from sviw_System_MyPermissionsCurrentDomain. When you grant access to a group you have the option to enter a value for Criteria1 and Criteria2. If you want to grant a group access to all WBS's starting with A-2, you will enter "A-2%" in the Criteria1 field for this group. The value "%" will mean that he does get access to all records in the selected domain. If Criteria1 is NULL for the selected group, the groups members will not get access to any records.

Revoke Access

It is also possible to use the criteria fields to revoke access to certain data. To implement revoke access by using CR3 Database Objects Form, select a Criteria1 field, and check the "Revoke" access. Remember to recreate views and triggers. Instead of the AND criteria created above, the following criteria will be added:

atbl_PC_Projbaseline_WBS.WBS NOT LIKE sviw_System_MyPermissionsCurrentDomain.Criteria1

If you want to revoke access to for example all WBS's starting with A-3 for a certain group, you would enter "A-3%" in the Criteria1Revoke field for the selected group.

Restricting access to alter data

The Insert trigger is executed when the user tries to insert data. The system table "Inserted" contains all records that are about to be inserted. If this table contains records that the user do not have access to insert, an error message will be returned, and the sql statement rolled back.

Similar triggers are created for update and delete events. It is possible to add custom code in these triggers as well. In the triggers there are sections marked as "Automatic generated section. Do not modify..", and "Insert your code here". The first section will be replaced when you choose "Create Triggers" from the CR3 Database Objects. Code in the "Insert your code here" section will not be altered.

The standard permissions check will be executed for all users, expect for 'sa', unless you specify "Bypass security for DBO's" when creating the trigger. If you have selected the "DBO Bypass" optin, the permission check will not be run for members of the db_owner role.

Note: if you for certain tables have a different security setup, and you do not use triggers to maintain security, you must check the "deny afUser Update Permissions" in the table. If not, the sstp_ApplyPermissions stored procedure will set the UPDATE, INSERT, DELETE permissions directly to table.

Database permissions

The new database permission functionality is added to control database modifications (DDL statements) regardless of user’s database roles. The permission check is implemented in strg_Database_ChangeLog. Note that this functionality is not a replacement for AppFrame security. For more info see DDL Triggers vs. DML Triggers.

The new functionality consists of the following new and modified database objects.

stbl_Database_Managers (New)

Fields: Login

Your Login must exist in the Managers table in order to insert, update and delete records in Managers table or the Managers table must be empty.

Your Login must exist in the Managers table in order to insert, update and delete records in Permissions table.

stbl_Database_Permissions (New)

Fields

Namespace – Any object, namespace or pattern can be specified by standard wildcards. (e.g. ____[_]PC[_]% , %, a% )

AllowCreate

AllowAlter

AllowDrop

AllowUsingTables – Specifies if views can be created or altered when tables are used as source.

sstp_Database_VerifyDDLActionPermission (New)

Procedures used in strg_Database_ChangeLog to verify permission of DDL statement / event.

strg_Database_ChangeLog (Updated)

Implemented DDL statement / event permission checks.

Managing Access

For each group you define what tables the group should have access to, type of access and to what data.

Example

You want to create a group that should have access to select and update data in the table: atbl_ProjetCost_WBS for WBS starting with '2' in domain 'AAA': Criteria1 field for this table needs to be set to WBS. The following record must be added to Table Row permissions for the group:

Table ID: atbl_ProjetCost_WBS
Grant: SU
Criteria1: 2%

If you want this group to have access to all WBS’s starting with 2, expect 2-A, this will be accomplished by:

Enable revoke access for criteria-field
Set Criteria1Revoke to '2-A%'

Master Groups versus Groups

A Standard Group access is defined per domain. In many cases it is relevant, and will cause less administration, to create a “Master Group”. A Master Group can be applied to several domains. That means that you can create a Master Group “Accounting Admin” which can be used for other domains as well. The row level permissions will be defined for the master group.

A Master Group can also have members. The members of a master group will have access to all domains that the master group applies to. If the “Accounting Admin” is not created as a master group, but instead as a standard group, you will need to maintain to the table row permissions for this group for each individual domain.

Stored Procedures

All AppFrame users will be able to execute stored procedures, since af_user is granted this kind of permission.If your stored procedure returns all records from a atbl (e.g. SELECT * FROM atbl_ProjectCost_WBS), all data from this table will be returned. The reason for this is that the user has execute permissions to the stored procedure, and that will override the fact that the user does not have access to the table directly. Therefore it is essential that your select statements contains the required permissions check, either by only using the table views (atbv’s/atbx’s) or that you implement a permissions check in the stored procedure.

When it comes to stored procedures that executes statements that alter data, the security check should be in the triggers. If you have chosen not to have security triggers for your tables, ensure that the stored procedure is taking care of it.

Windows Integrated Security

Integrated Windows Authentication provides a more seamless user experience. The user will not be prompt for user ID and password, if he is already authenticated based on Active Directory. With AD, password management uses the ordinary Windows password policies and password change APIs, and no passwords are passed over the network.

Setting up integrated Windows authentication in your environment is not a complicated and big job. However, you will need to involve the IT department to do some configuration changes on Active Directory server. In addition, some updates to AppFrame configuration will be required. This procedure describes what to do in order to set up integrated Windows security:

Enabling Win Authentication

AppFrame

Meta