Introduction to Appframe Security

The Appframe security model that Pims is utilizing 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 in-built 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 windows application, web application or SQL Server Management Studio, or whatever type of client, there will be no difference in what data you will be able to see or alter.



AppframeSecurityLayer.png

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
  • Execute on functions and stored procedures

The stored procedures sstp_Database_Maintenance_ApplyPermissions ensures that all database objects get the required access.

A job that executes this stored procedures is defined during the setup of the Appframe environment.

When defining tables in Appframe, insert, update and delete triggers are created and it's these that are controlling the access to the data in the table.

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:

SELECT, UPDATE, INSERT, DELETE (use Manage Roles form to maintain these settings)

For example: If a user has SELECT permissions to the table atbl_Project_Markers the user will be able to read all records in the domain.

Restricting access to view data

The standard user will not be able to access the tables directly. They can only be accessed from views, stored procedures or functions.

Every table should have a corresponding view, prefixed with atbv instead of atbl. For example, the table atbl_Project_Markers will have a view named: atbv_Project_Markers.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_Project_Markers WITH (NOLOCK)

WHERE EXISTS (SELECT *

FROM sviw_System_MyPermissions_CurrentDomain WITH (NOLOCK)

WHERE Domain = atbl_Project_Markers.Domain

AND TableID = 'atbl_Project_Markers')

The view sviw_System_MyPermissions_CurrentDomain will be in all standard atbv-views. The view returns all the tables for the current user that he have access to in his current domain.

In the autogenerated view, atbv_, you can add custom security for either grant more access or to add more restrictions. Inside the view you will find to sections. One named Appframe generated code and one named Custom security check. The Custom security check will not be altered, and can be used to add custom security.


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 Winforms Database Objects form). The atbx will use the sviw_System_MyPermissions_Domains for check users permissions.

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

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 Custom security checks:, Custom trigger logic, Appframe security checks and Appframe logging checks. Code in the Custom security checks and Custom trigger logic section will not be altered.

The standard permissions check will be executed for all users

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_Project_Markers), 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 a stored procedures have statements that alter data, the security check should be in the triggers.

Capabilities

In addition to defining access to select, insert, update, delete permissions to records, it is also possible to define module specific capability codes. E.g. In Pims Document Control, the following capability is used to control if users can manually add sequential numbers themselves or not: "Manually create sequential numbers"

Managing Access

Since a standard SQL Login have no permissions in the Pims database itself, a Pims user needs to be created within the Pims application and be given data permissions through Role memberships.


Appframe AccessLayer Security


Communication Encryption

The Microsoft Windows Web Server can be configured with various settings, these are our Best Practise settings:

  • SSL Certificate with RSA 2048 Key
  • Secure Cipher Suites: ephemeral DiffieHellman or RSA
  • Secure Protocols: TLS 1.1 or higher
  • Encrypted web configuration files
  • HTTP Strict Transport Security
  • All Cookies set to be secure (Allow only HTTPS)

SQL Injection

Every SQL query is generated from object structures and parsed filter strings and all values are parameterized using .Net's SqlClient class. Because we do not construct SQL queries manually anywhere, and all values are parameterized, SQL injection is not possible.


Cross-Site Scripting (XSS)

All our controls make sure to escape any data coming from the database and insert them using textContent or innerText which ultimately prevents the data from being handled as anything but text. Our only possible XSS vectors would be holes created by the developers of the applications, so the applications must be reviewed to make sure all data is inserted into the DOM as text instead of html. One of the important areas to check is the use of the repeater control, which if used incorrectly can cause un-escaped data to be inserted into the DOM.


File Encryption

Appframe Filestore can be encrypted by any third party encryption software where the policy is set so Appframe can write and read file(s) from an NTFS File system through and SMB Share or a Local folder on the server.

Appframe Filestore with Windows Encrypted File System supports:

  • Certificate with RSA Key of 2048 Bit
  • Advanced Encryption Standard (128 and 256)
  • Secure Hash Algorithm (SHA2 256, 384 and 512)