Controlling User Access to Application Data
Controlling user access to application data is an important part of providing a managed query and reporting environment. For example, when setting up a querying and reporting environment for users of an accounting application, you may want to restrict querying of payroll data on a user-by-user basis. Foxfire!’s data-driven design makes it easy for you to implement this type of security as well as a wide variety of other security schemes.
Each application you create will probably have a different set of querying and reporting data access security requirements. Foxfire!’s security architecture is designed for maximum flexibility to enable you to create your own custom security scheme tailored to each application. The flexibility to create a tailored security scheme is made possible using a multi-layered approach. The four layers in the Foxfire! data access security architecture are:
1. User Id Authentication and User Security Level
2. Filtering Foxfire! System Tables
3. User Privileges
4. Request Ownership and Limits on Request Editing
Each successive layer gives you finer control over user access to data, and you can target specific layers to an individual user or groups of users.
User id authentication and user security level
The first layer of security involves authenticating the User Id and establishing the user’s Security Level. Foxfire! v6.0 now supports User Id and Password authentication to control access to the application. In previous versions of the product, the User Id needed to be established externally and then passed in as a parameter, and there was no authentication of the Id. In v6.0, the User Id and Password are authenticated against existing records in the User Table (the User Table is also a new to v6.0 and is setup and maintained using the User Account Manager). You can now either pass in the User Id or prompt the user for an Id and Password using the built in Login dialog (see Figure 1 below). Either way, the User Id must be authenticated before the application will be allowed to start up.
Figure 1. “Foxfire! v6.0 now provides a built in login dialog to give you the option to prompt users for an Id and Password.”
Example 1
Passing in the User Id using command line parameters
DO FOXFIRE WITH “REQUESTS”,”JOHN”,””,”FFCONFIG”
Example 2
Forcing a user login dialog to prompt the user by leaving the User Id empty
DO FOXFIRE WITH “REQUESTS”,””,””,”FFCONFIG”
You might wonder why there is no password parameter when passing in the User Id. It is assumed that if your passing in a User Id you’ve already prompted the user for User Id and Password information somewhere else, so primary security has already been established, and you only need to establish the User Id to determine user Security Level and facilitate the other three security levels. Also, specifying a password in code could compromise security.
Once the User Id has been authenticated and the user established, the contents of the user record are scattered into memory variables, which will stay in scope throughout the Foxfire! session and are referred to as the User variables (all begin with LG_ ).
Two of these variables, the User Id (m.LG_USERID) and the System Administrator flag (m.LG_ADMIN), when used in conjunction provide many ways to control access to your application data. Each new Foxfire! Request will have it’s ownership established using m.LG_USERID , and the value of m.LG_ADMIN is used to establish the users Security Level.
In previous Foxfire! versions, Security Level was controlled by the variable FF_SECLVL. This variable is still used for this purpose for backward compatibility, but is now automatically set depending on the value of m.LG_ADMIN. FF_SECLVL has only two states, either “*”, or not “*”. When the value is “*”, Security Level is set to “Developer”, and the user will be immune to any system Privilege settings (see Privileges below). When the value is not “*”, Security Level is set to “Non-Developer”, and system Privileges settings will be in effect. In previous versions of Foxfire!, you had to programmatically set FF_SECLVL in the Foxfire! configuration file FFCONFIG.PRG. If you wanted to control Security Level on a user-by-user basis you had to write logic in one of the hooks in FFCONFIG. Now, the value of FF_SECLVL is set to “*” if m.LG_ADMIN = .T., and it is set to “USER” if m.LG_ADMIN = .F..
Note: All the new User Id authentication features are optional in the Standard Edition. Passing in the User Id without authentication is still supported for backward compatibility to previous versions. However, Enterprise Edition requires the mandatory use of User Id authentication for controlling end user licensing
Filtering Foxfire! system tables
Foxfire!’s user interface is heavily driven by the contents of tables, referred to as the Foxfire! system tables or the Foxfire! metadata. If you control access to the records in these tables, you effectively control access to what a user can see and do with the Foxfire! application. Since the system tables are FoxPro tables, the strategy here is to apply filters to these tables to control user access to specific system table records.
The three tables of primary interest are the Preference table, the Request table, and the Data Items table (the data dictionary).
The Preference Table
The Preference table is the highest-level organization construct in Foxfire!. Each Preference record maintains a reference to a Request table and a Data Items table. You can create multiple Preferences for users to choose from that either partition a single database or provide querying and reporting for multiple databases (see Figure 2 below).
Figure 2. “The Preference Set Picker displays all available Preferences available to the user.”
The tables and fields that can be queried and all existing Requests is therefore indirectly controlled by the Preference. If you have multiple Preferences, and want to prevent certain users from accessing the Requests and Data Items associated with certain Preferences, you simply apply a filter to the Preference table.
The best place to apply filters to the Preference table is the Foxfire! Preference Setup phase programming hook. Located in the Foxfire! configuration file FFCONFIG.PRG, this phase is called when Foxfire! starts up and attempts to establish the active Preference. The Preference table is open and is the current alias at this point in Foxfire! execution.
Example 3
The Preference table contains a “department” field, PF_DEPT. The User record also contains a department field, LG_DEPT. I want to allow users to access only those Preferences where the users department matches the Preferences department. Also, I want to exclude users with “Developer” Security Level from this constraint.
In the Preference Setup phase in FFCONFIG.PRG, you would enter the following code:
IF !(m.lg_admin = .T.)
SET FILTER TO ALLTRIM(pf_dept) = ALLTRIM(m.lg_dept)
ENDIF
The Requests Table
After a startup Preference has been establish in the Preference Setup phase, the associated Request table for that Preference is opened to fill the list of available Requests displayed in the Request Manager (see Figure 3 below). Each item in the Request List is a record in the Request table.
Figure 3. “The Foxfire! Request Manager shows the list of Requests which is driven from records in the active Request table.”
To prevent users from running particular Requests, you can filter the Request table so that these Requests will not appear in the Request List. In the Preference Set Editor – Files dialog, you can specify a filter expression to be applied for each of the system tables in a Preference (see Figure 4 below).
Figure 4. “The Files dialog in the Preference Set Editor displays the name and location of each system table.”
In the above figure, you can see that each system table has an associated textbox for entering a filter expression. To filter the Request Table, you can enter any valid FoxPro filter expression in the “Filter” textbox. The expression entered for the Request table is saved in the Preference field PF_FLTREQ. Whenever, the Request table is opened, Foxfire! checks if the field PF_FLTREQ contains an expression, and if so, attempts to apply the filter to the table.
Note: no validation is attempted on this expression. If you enter an expression that is invalid, Foxfire! will error.
Example 4
When a user creates a Foxfire! Request, the Request is tagged with the User Id of the user who created the Request (using m.LG_USERID). The value in m.LG_USERID is stored in the Request table field RQ_USER. If you wanted to filter the Request table such that users can only see Requests that they created, you would enter the following filter expression:
Rq_user = UPPER(ALLTRIM(m.lg_userid))
Using filter expressions to control access to records gives you a great deal of flexibility when implementing a security scheme.
Example 5
The above example used Rq_User in the filter expression, but you may want to filter on a wider range of user subsets. To achieve this you will of course need more variables in the filter. A variable that is often used for filtering purposes is the Request field RQ_MISC. This field is a miscellaneous field not used by the Foxfire! application and is available for you to use for any purpose. A common use for this field is for storing additional information about a Request for use in filtering the Request table.
What if you wanted users to see their own Requests or any Requests that you’ve tagged as being for “General” use. You could browse the Request table and place the value “General” in appropriate Request records. Then, change the Request filter expression to:
Rq_user = UPPER(ALLTRIM(m.lg_userid)) OR Rq_misc = “General”
Note: You can add fields to any Foxfire! system table without damaging the Foxfire! application. Add as many fields for filtering as you need.
The Data Items Table
The Data Items table holds all the table, field, and custom data item information for a Preference (this is also referred to as the Foxfire! data dictionary). The technique for controlling access to individual data items is exactly the same as with the Request table.
One small difference is that miscellaneous field (RDI_MISC) in the Data Items table is surfaced in the Data Item Editor interface. In the Advance Options dialog there is a textbox for entering “User-definable Group Ids” (see Figure 5 below). Values entered in this textbox are saved in the field RDI_MISC.
Figure 5. “The Advanced options dialog in the Data Item Editor allows you to tag each data item with a Group Id which can then be used for filtering the Data Items table.”
Example 6
You could create a Group Id scheme such as: “A” = All; “P” = Payroll. Then, you could enter a filter expression for the Data Items table in the Preference – Files dialog such as:
“A” $ rdi_misc OR (“P” $ rdi_misc AND m.lg_dept = “Payroll”)
The above filter would insure that data items tagged with “A” are available to all users, but data items tagged with “P” would only be available to users who belonged to the Payroll department.
User privileges
Foxfire! uses a Privilege system to control a user’s ability to add, edit or delete Requests and Data Items. Privileges are actually used to control access to every aspect of the Foxfire! interface, but I will focus on Requests and Data Items. One clear way to prevent users from accessing sensitive application data is to simply prevent them from being able to add or edit Requests.
The Preference maintains the Privileges for adding, editing, and deleting Requests. You set Privileges using the Privileges dialog in the Preference Set Editor (see Figure 6 below).
Figure 6. “The Privileges dialog in the Preference Set Editor is where you set system wide privileges for users with ‘Non-Developer’ security level.”
The Request Manager Privileges in the upper left hand portion of the dialog control whether a user can create, edit or delete Requests. If you “uncheck” these options, users that do not have “Developer” Security Level will not have the ability to add, edit, or delete Requests. They can only Run or Preview pre-existing Requests.
Request Ownership and Limits on Request Editing
Although it is possible to prevent a user from editing any Requests at all by setting an appropriate privilege, another option is to “lock down” one or more portions of a Request so that they may not be edited by users other than the Request’s owner. As described above, the owner of a Request is the user who created it and whose User Id is saved as the value of the RQ_USER field. If the owner wants to protect all or a portion of the Request, they may use the locking options in the “Save As” feature of the Request Editor (see Figure 7 below).
Figure 7. “The ‘Save As’ dialog in the Request Editor lets you lock portions of a Reqest.”
Each Request Editor sub-dialog may be locked by the owner of the Request so that other users can not modify that portion of the Request. Locking involves choosing the appropriate lock options in the Save As dialog, then saving the Request. If the new Request name is the same as the old one, the net effect is to change the locking status of the Request without creating a copied version of the Request. Locking all four parts results in a completely “read only” Request when it is edited by anyone other than the owner.
Locking portions of a Request can assure that vital parts of a Request which could effect access to application data if changed, won’t be modified, while allowing the user to still vary their query as needed. For example, you may want to lock user access to the Data Item picker, but still allow users to change Filter criteria, thus strictly controlling what data items they can see. Or, you could lock user access to the Filter Builder, but give them access to the Data Item picker, thus strictly controlling the number of records returned by the query.
Note: Request-level locks are completely ignored under two circumstances: when the user is a System Administrator or when the user is already the owner of the Request.
I hope this outline of the four main layers of Foxfire!’s security architecture gets you started toward implementing your own security requirements. Foxfire!’s open architecture should make it possible to create a variety of security schemes to control access to your application data.