3305 Main Street Ste 019 Vancouver, WA 98663

How do I restrict Sage 100 Contractor data from being viewed by specific people?


The security features of Sage 100 Contractor relate only to the menus and screens within Sage 100 Contractor and do not affect data-access from external sources (such as Microsoft Access, Crystal Reports, or Liberty Reports). This is because the security controls are implemented within the program (Sage 100 Contractor) and not within the database (which is a Microsoft Visual FoxPro database). This fact obviously does not eliminate the requirement in some cases to restrict the data that can be seen. This article addresses potential options to control this.

Option 1 - Windows Folder Permissions

Using Windows permissions, you can control which users or groups have access to the Sage 100 Contractor data folders that contain sensitive information. Keep in mind that if a user is restricted from accessing a given data folder, they will not be able to access it even within Sage 100 Contractor.

Option 2 - Windows File Permissions

Using Windows permissions, you can control which users or groups have access to the specific files within a Sage 100 Contractor data folder. Each DBF file represents a different table of information (e.g., "employ.dbf" is the "PR Employee" table). Restricting access to a file for a given user or group will prevent them from being able to run reports that utilize the information in that file.

WARNING - Restricting access to files can cause tasks within Sage 100 Contractor to not work. For example, the GL Account (lgract) table is used by Sage 100 Contractor during tasks such as invoice entry or payroll processing for the purpose of providing an on-screen list or for validation. Restricting access to this table would therefore lead to the inability for these related tasks to work. You should consult first with a qualified expert on Sage 100 Contractor before deciding which files can safely be restricted in this manner based on the type of role and the tasks they perform within Sage 100 Contractor.

Option 3 - User-Sensitive Report Logic

Liberty Reports provides a DBUser function that can return the user name that was used to connect with the Sage 100 Contractor database. This function can therefore be used control what the report does based on the current user name. For example, the following expression returns TRUE if the current user is in a list of permitted users, and FALSE if they are not:

=NOT(ISNA(MATCH(DBUSER("Sage 100 Contractor"),{"CHRIS","KIM","SAM"})))

This is only an example of one way to utilize this function. There are others that are simpler and also more sophisticated.

The result of this expression could be used within IF functions to either return a value or blank (e.g., show an account balance or not). This could also be used in a similar fashion to control the parameter values that are provided to a query so that a query will return no records if the result is FALSE (e.g., a cutoff date parameter might be 1/1/1900 if FALSE but use the entered value if TRUE).

The scope of this article is not intended to be a comprehensive guide on how to implement this approach. For further assistance, contact your services representative for Liberty Reports.

Explanation of Above Formula

  1. The DBUSER("Sage 100 Contractor") portion of the formula returns the user name of the user currently connected to Sage 100 Contractor via Liberty Reports
  2. The MATCH function compares that to a list of users that is identified in the array {"CHRIS","KIM","SAM"}
  3. If a match is found, the MATCH function returns a number (1 to 3 in this case), otherwise it returns #N/A
  4. The ISNA function returns TRUE if the result of the MATCH was #N/A and FALSE if it was a number
  5. The NOT function switches TRUE to FALSE and FALSE to TRUE so that we get a result of TRUE if the user is in the list