Tutorial - Advanced Security - Dynamic User Level Security

In this tutorial we will show you how to setup User Level Security. We will use the demo database for demonstration.

User Level
User Level Security secures data at table level. Each user level is granted with specific permissions to tables in the database. Users with different access levels are restricted with different add/copy, list/search/view, delete and edit rights.

There are 2 types of User Level security:

1. Static User Levels - the User Levels and the permissions are defined in the project and the User Levels are not to be changed after script generation.
2. Dynamic User Levels - the User Levels and the permissions are defined in 2 tables in the database, the User Levels can still be changed with the generated scripts.

In this tutorial we use dynamic User Level Security.

The "Employees" table and "Orders" table in the demo will be used in this example.

Fields in Table "Employees"

Fields in Table "Orders"

Dynamic User Level Security stores the User Level information in the database, so you need to add 2 tables to your database - User Level Table and User Level Permission Table which must have the following fields, note the data types, User Level ID and the Permission fields must be of integer type, the field names can be different though:

You can create these 2 tables in the database yourself or you can use ASP Report Maker to create these 2 tables for you, please see below.

Steps to Setup Advanced Security

1. Loading ASP Report Maker

Open ASP Report Maker and connect to the demo database.

2. Setting up User Access Levels

Click on the [Security] tab, there are two sections for the login process:

Administrator Login
If you tick this option, a hard-coded Administrator account will be generated which has all access right to all tables/views.

Use Existing Table
Tick this option to set up the user access levels. You should select the security table and the corresponding Login Name and Password fields.

To set up the user levels, click on the [Advanced] button. A popup window will appear. Click on the [User Levels] button. Select the User Level Field.

Note: The User Level field must be of integer data type. Non integer fields will not be seen in the User Level Field combobox.

To use dynamic User Levels, switch to the [Dynamic User Levels] and check [Enable Dynamic User Levels].

If you want ASP Report Maker to create these 2 tables in your database, click the [Create tables] button, the following form will display for you to change the table/field names if necessary. You can change the table/field names and then click OK to continue.

If you have projects created by previous versions of ASP Report Maker you may want to use dynamic User Levels and migrate the previously defined static User Levels in the project to the database. After selecting or creating the User Level and User Level Permission tables/fields, just click the [Migrate] button to let ASP Report Maker do that for you.

You'll need to specify the User Level Table and the User Level Permission Table and the related fields. (If you use ASP Report Maker to create the tables, ASP Report Maker will set up the tables/fields automatically also.)

User Level Table - the table for storing the User Levels
User Level ID Field - the ID of an User Level, this field must be integer type
User Level Name Field - the name of an User Level, this field should be string field

A typical User Level Table should contain data like this:

Note that there are two built-in user levels:
Administrator - Administrator is a built-in user level that has all permissions plus the privileges to modify User IDs and User Levels. Its permissions are same as that of the hard-coded Administrator Login. The User Level ID of Administrator is -1.
Default - Default user level is built-in user level with user level = 0. Since User Level field is an integer field, if you set a default value of 0 for this field, this user level will become the default user level for the user after registration and before the Administrator assigning another higher user level.

Note: The Anonymous built-in user level in previous versions is deprecated.

You can add your own User Levels with User Level ID starting from 1.

User Level Permission Table - the table for storing the permission of the User Levels
Table Name Field - the table name of each table in the database, this field should be of string type
User Level ID Field - the ID of an User Level, this field must be integer type
Permission Field - the permission of the specified User Level ID in the specified table, this field must be integer type also.

A typical User Level Permission Table should contain data like this:

Click OK to close the Advanced Security Setup form.

Note: ASP Report Maker does not support data modification, it does NOT generate scripts to update the User Level Table and User Level Permission Table.

3. ASP Script Generation

Click the [Generate] button and ASP Report Maker will generate the required ASP scripts automatically.

4. Running the ASP Application

To see the Advanced Security works, we login using "andrew" as user name and "1234" as password. The user is of "Manager" user level. In the menu, you'll see that you can access all allowed reports.

Now we logout and then login again as employee #1 using "nancy" as user name and "1234" as password.

According to the user level defined by us, users with "Sales" user level has view permission to the "Orders" table only. In the menu, you'll see no links to other reports.

If you try to go to other reports by typing the URL directly in your browser, you're not allowed to view it and you'll be redirected back to a report that you have permission.

Notes: There are two types of Advanced Security implemented in ASP Report Maker - User ID Security and User Level Security. User ID Security secures data at record level; User Level Security secures data at table level. They can work independently or work together. (See Advanced Security - User ID Security)

Also see:

Advanced Security - User ID Security
Advanced Security - Static User Level Security