In this tutorial we will show you how to setup User ID Advanced Security Access in ASP Report Maker. We will use the demo database for demonstration.
User ID Security
User ID Security secures data at record level. Protected tables must have an User ID field for identifying which user a record belongs to. The User ID field names can be different in tables though. When User ID security is enabled, users can only access their own data.
The "Employees" table and "Orders" table in the demo.mdb will be used in this example.
Fields in Table "Employees"
Fields in Table "Orders"
Steps to Setup User ID Security
1. Loading ASP Report Maker
Open ASP Report Maker and connect to the demo database. Open ASP Report Maker and connect to the demo database. Note that the tables and views are not selected by default, these tables and views are the source tables of your reports. However, to demonstrate the User ID Security, we use these tables directly in this tutorial for simplicity. The setup for Detail and Summary Report and Crosstab Report is exactly the same.
In the database pane, check the "Orders" table to generate a simple report for it. (Alternatively, you can also select tables in the [Generate] column of the Table Setup page.)
In the Field Setup page on the right hand side. In the [Filter] column, check the checkbox for the field "EmployeeID" to create a filter for this field in the report.
2. Setting up Security
Click on the [Security] tab, there are two sections for the login process:
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 ID, click on the [Advanced] button. A popup window will appear.
3. Setting up User Access Own Data
You can set up the [User ID] Field and [Parent User ID] as follow:
As the caption suggests, the User ID Field is a field to identify users. The field values for each user must be unique. So typically the primary key of the User Table is used as User ID Field.
To identify the records owned by a user, the records must also have a field to store the User ID value. Therefore, all protected tables must have an User ID field, the field names can be different though. To setup User Data for different tables/views, select the "User ID Field" for the tables/views that requires User ID security.
a. Click on User ID in the left pane,
b. Select the User ID field from your user table, (otherwise the feature is disabled)
c. In the User ID Field column, select the User ID Field for the tables/views that requires User ID security.
[Parent User ID] field stores the parent User ID that the user belongs to. For example, a parent user can be the manager that the employee reports to. You can select an Parent User ID for users so the parent user can modify the child users' records as his/her own.
In this example, we set the Parent User ID Field as the "ReportsTo" field.
4. ASP Script Generation
Click the [Generate] button and ASP Report Maker will generate the required ASP scripts automatically.
5. Running the ASP Application
Login as employee #1 using "nancy" and user name and "1234" as password. Go to the "Orders" table. As we have used User ID security for the table,
we can only access employee #1 (nancy)'s records.
If you now log out and login again as employee #3 using "janet" as user name and "1234" as password, you can only access employee #3 (janet)'s records:
But if you now lo gout and login again as employee #2 using "andrew" as user name and "1234" as password, you can access all employees' records because "andrew" is the parent user of all employees. All users report to "andrew".
If you click the popup filter button in the header, you can filter the records for your child user(s) and/or your own.