Tutorial - Advanced Security - Dynamic User Level Security

In this tutorial we will show you how to setup dynamic 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, lookup, delete and edit permissions.

There are 2 types of User Level security:

Static User Levels	The User Levels and permissions are defined in the project and the User Levels are not to be changed after script generation.
Dynamic User Levels	The User Levels and permissions are defined in 2 tables in the database, the User Levels can 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 DB AppMaker to create these 2 tables for you, please see below.

Steps to Setup Dynamic User Level Security

1. Open DB AppMaker

Open DB AppMaker and connect to the demo database.

2. Set up User Levels

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

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

Use Existing Table
Enable this option to set up the user access levels. You should select the security table and the corresponding User 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.

Important 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 DB AppMaker 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 DB AppMaker 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 DB AppMaker 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 DB AppMaker to create the tables, DB AppMaker 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 three built-in user levels:

Anonymous	The built-in user level for the anonymous user (i.e. users that have not logged in). The User Level ID of Anonymous is -2.
Administrator	The 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. The User Level ID of Administrator is -1.
Default	The built-in default user level for logged in user. The User Level ID of Default is 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.

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:

Notes

Table names will be prefixed with Project ID (see Project File).
The prefix makes your project compatible with PHPMaker project.
The prefix also allow you to use the same table for multiple projects.

Click OK to close the Advanced Security Setup form.

If you go to Field Setup Page now and view the Edit Tag for the User Level Field, you should see that the Edit Tag has been setup as "SELECT" and the User Level table has been setup as lookup table automatically:

3. Generate scripts

Go to the Generate tab, click the Generate button to generate scripts.

Notes

Currently DB AppMaker does not include pages to set up the permission. This example uses PHPMaker to generate script for setting up permissions. If your project is created from a PHPMaker project, then you are ready. If not, just download PHPMaker, open your project with PHPMaker and generate a site. (PHPMaker can open DB AppMaker project.)
1. Only administrators can manage User Levels, you must login as the hard coded administrator or as a member of the built-in Administrator User Level.
2. You manage the User Level permissions via the scripts generated for the User Level Table and the User Level Permissions page (userpriv.php), NOT via the scripts generated for the User Level Permission Table directly. If you go to the scripts for the User Level Permission Table directly, you'll only see the permissions in integer format.
Permissions are stored in the User Level Permission Table. If you use your own tools to set up, note that the value in the Permission Field is a sum of the granted permissions:
ADD: 1
DELETE: 2
EDIT: 4
LIST: 8
VIEW: 32
SEARCH: 64
LOOKUP: 256

4. Setup permissions

Go to the PHPMaker generated site, login as Administrator, go to the List page of the User Level table and add some user levels.

Note If the User Level table is not created by DB AppMaker, you may not have the Anonymous, Administrator and Default User Level in the table yet. You'll need to add them yourself.

In this example, we add 2 more user levels - Sales and Manager. Click the Add link and enter the User Level ID, User Level name and default permissions for the "Sales" user level as follows:

Click "Add" button to confirm. Similarly, add the "Manager" User Level.

In the List page of User Levels Table, you can click the "Permissions" link of each user level to refine the permission for each tables:

To assign different user level for the users, go to the user table (the "employees" table in this case).

We assign a password and the user level "Sales" to the employee #1 (the employee with EmployeeID equals 1).

5. Run the application

To see the Advanced Security works, run the app with ionic serve.

Login as administrator. If you go the Add/Edit page of the user table, you'll find that the selection list the User Level Field is populated with the user levels from the User Level Table.

According to the user level defined by us, users with "Sales" level has view and add permissions to the "orders" table only. They are not allowed to update or delete records. Employee #1 belongs to the "Sales" level, so you can view the records, you can see the "+" button at the bottom. If you slide the item, you can only see the "Copy" button, but not the "Edit" and "Delete" buttons.

Notes

User Level Security settings are generated in a file named userlevelsettings.php. After changing permissions, you must generate that file again.
Users have no right to change his own user level. Only Administrator can change an user's user level. If you want to assign user levels in the generated scripts, a hard-coded administrator login account must also be created. Alternatively, an user must be assigned with the Administrator level. However, you may still need to use the hard-coded Administrator Login to log in and assign user levels to users initially. Of course, you can also modify data in your database directly. The value of the Administrator level is -1.
Since User Level works at table level only, if an user has permissions to the User Table, personal information (including user level and user id) of other users will be exposed. Therefore you should not expose the User Table to normal users. If you want to expose the User Table and restrict users to access their own data only, you need to use User ID Security, which controls permissions at record level. (See Advanced Security - User ID Security)
There are two types of Advanced Security implemented in DB AppMaker - 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